How do I get all records from tableA with a left outer join and a where condition on tableB?

Question

Basically, what I want is if there is a record in tableB of type 'X' I want to see it, otherwise I don't, but I want all records from tableA.

I know I could accomplish this by putting the tableB.type = 'X' in the LEFT OUTER JOIN ON clause, but I can't do that because I'm limited to using only the where condition because I'm using a restricted query manager of a program I won't name, but I definitely hate. :)

SELECT *
FROM tableA 
LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'

How do I accomplish this?

EDIT

I've tried this, but I still don't get all records from tableA. I'm testing this on SQL server to avoid waiting for long periods for my query to run on the 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

Answer 1

Check for nulls in tableB:

SELECT *
FROM tableA LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'
OR tableB.type IS NULL

That will get you everything from both tables when the join matches, and everything from tableA when there's no corresponding record in tableB.

If type can be null naturally, you'll want to change the condition to something more sound:

SELECT *
FROM tableA LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'
OR tableB.ID IS NULL

Assuming ID is the primary key and cannot be null naturally, that will get the same result.

Answer 2

You can join against the "type X" records from tableB exclusively by amending the join condition:

SELECT 
  *
FROM 
  tableA 
  LEFT OUTER JOIN ON 
    tableA.ID = tableB.ID  
    AND tableB.type = 'X'

Answer 3

Don't know if you have access to the database, or if you have to query tableB specifically due to some other restriction, but you could always create a view of table b called tableBTypeX where the view is restricted to only those rows with type = x. Then you could left outer join against tableBTypeX . In your query, the join columns are ID columns, so they probably have indexes, making the query fine in terms of speed. In the case where the join columns are not indexed, joining against the view would be more efficient, because fewer rows are joined against, and joining against unindexed rows usually requires a full table scan, making it a much more time consuming query.

Answer 4

Is UNION possible?

SELECT *
FROM tableA 
LEFT OUTER JOIN ON tableA.ID = tableB.ID
WHERE tableB.type = 'X'

UNION

SELECT *
FROM tableA

... or a CTE? Not sure how the name tableB would resolve though and can't test...

;WITH tableB AS
(
    SELECT * FROM tableB WHERE type = 'X'
)
SELECT *
FROM
    tableA 
    LEFT OUTER JOIN
    tableB ON tableA.ID = tableB.ID