SQL left join is not returning all results when second table is in WHERE statement

Question

I am trying to join two tables together where the second table may not have a related record. In that case, I want the data from the first table to still display. I am able to do this with a LEFT JOIN but the problem I am running into is that I have a reference to the second table in the WHERE statement.

SELECT a.field1, a.field2, b.field2 from a
LEFT JOIN b ON a.id = b.id 
WHERE a.field1 = "value" AND b.field1 = "value"

It seems that since I reference table b in the WHERE statement, I am only getting records where there is a related record in table b. Is there a way to still include table b in the WHERE statement and still return records from table a even if there is not a related record in table b?

Thanks!

the answers here and in the supposed duplicate only address one of the two possible intents in this question's non-working query, voting to reopen — ysth, Sep 17 '20 at 21:43
Ah ok, that makes total sense. You guys are awesome! Thanks for all your help! — Kevin Rose, Sep 18 '20 at 03:34

score 3 · Answer 1 · answered Sep 17 '20 at 21:22

3

Move the condition on the left joined table to the on clause of the join:

SELECT a.field1, a.field2, b.field2 
FROM a
LEFT JOIN b ON a.id = b.id AND b.field1 = 'value'
WHERE a.field1 = 'value'

When then LEFT JOIN finds no match in table b for a given id of table a, all columns in b are null, so condition b.field1 = 'value' cannot be satifsfied in the where clause - so the entire row is removed from the resultset. You want that condition to be bound to the left join instead.

answered Sep 17 '20 at 21:22

GMB

216,147
25
84
135

not necessarily; there are two possible intents: first, that the join is only wanted when b.field1='value'. your answer works for that. the other possibility is that they really do want to left join only by id, and not return any row at all if b.field1 is not 'value'. for that the condition stays in the where clause but is `(b.id is null or b.field1='value')` – ysth Sep 17 '20 at 21:46

Gordon Linoff · Accepted Answer · 2020-09-18T10:29:03.550

Filters on the second table need to be in the on clause:

SELECT a.field1, a.field2, b.field2
from a LEFT JOIN
     a
     ON a.id = b.id AND b.field1 = value
WHERE a.field1 = 'value' ;

If they are in the WHERE clause, the outer join is turned into an inner join because NULL fails the comparison.

I assume that b is actually defined somewhere in the query -- you oversimplified.

Filters on the first table remain in the WHERE clause.

score 0 · Answer 3 · answered Sep 17 '20 at 21:25

when you put where clause on left joined table which is b, left join is implicitly converted to INNER JOIN

what you can do is to move b.field1 = "value" to left join statement

SELECT a.field1, a.field2, b.field2 from a
LEFT JOIN b ON a.id = b.id  and b.field1 = "value" 
WHERE a.field1 = "value"

SQL left join is not returning all results when second table is in WHERE statement

3 Answers3