How to get all records from table A using left outer join and where clause on tableB?
Basically, I want, if there is a record in tableB of type "X", I want to see it, otherwise I won't, but I need all the records from tableA.
I know I can accomplish this by putting tableB.type = 'X' in the LEFT OUTER JOIN ON clause, but I cannot do that because I am limited to using only the where clause, because I am using the restricted query manager of the program I am I won't name it, but I definitely hate it. :)
SELECT *
FROM tableA
LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'
How to do it?
EDIT
I tried this, but I still don't get all the records from tableA. I am testing this on a SQL server to avoid waiting for long periods for my query to run on a production system. I'm pretty sure the production system is using Oracle if that helps.
SELECT *
FROM tableA LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X' OR tableB.type IS NULL
source to share
Check null
in tableB:
SELECT *
FROM tableA LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'
OR tableB.type IS NULL
This will give you whatever you want from both tables when the join matches, and everything from tableA when there is no matching entry in tableB.
If it type
might be null
natural, you might want to change the condition to something more sonic:
SELECT *
FROM tableA LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'
OR tableB.ID IS NULL
Assuming which ID
is the primary key and cannot be null
naturally will get the same result.
source to share
Don't know if you have access to the database or if you need to query tableB on purpose due to some other restriction, but you can always create a view of table b called tableBTypeX where the view is limited to only those rows with type = x ... Then you can leave the outer join with tableBTypeX. In your query, the join columns are ID columns, so they probably have indexes, making the query accurate in terms of speed. In case the join columns are not indexed, the view join will be more efficient because fewer rows are appended to them, and the non-indexed join usually requires a full table scan, which makes it much more time consuming.
source to share
Is UNION possible?
SELECT *
FROM tableA
LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'
UNION
SELECT *
FROM tableA
... or CTE? Not sure how the name tableB will resolve, although it cannot verify ...
;WITH tableB AS
(
SELECT * FROM tableB WHERE type = 'X'
)
SELECT *
FROM
tableA
LEFT OUTER JOIN
tableB ON tableA.ID = tableB.ID
source to share