MySQL Left Join not returning null values for joined table

Question

Please help me with the following MySQL query, which joins two tables (A and B):

SELECT * from A left join B on A.sid = B.sid where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5) AND (rYear = 2011 or rYear is null) 

roleCode is a field in table A and rYear is a field in table B

The result set is not as expected. Only 185 rows are returned, but there are 629 rows in table A that match the where condition. Shouldn't the rows without a matching row in table B be returned with null values for their B fields?

Answer 1

You should not specify rYear in a WHERE clause. Those limit your results after the join. You should specify rYear in an ON clause to get back records with NULL from table B.

SELECT * from A left join B  on A.sid = B.sid  AND (rYear = 2011 or rYear is null) where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5) 

Answer 2

Greg, is that really all there is to the query?

Sample tables

create table A(rCode int, sid int); insert A select 1,1; insert A select 2,3; insert A select 3,2; insert A select 5,4; insert A select 1,5; create table B(rYear int, sid int); insert B select 2011,1; insert B select null,3; insert B select 2011,2; insert B select 2015,2; 

Queries:

SELECT * from A left join B on A.sid = B.sid where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5) AND (rYear = 2011 or rYear is null);  SELECT * from A left join B on A.sid = B.sid AND (rYear = 2011 or rYear is null) where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5); 

Both of the queries are exactly the same, both returning:

rCode       sid         rYear       sid ----------- ----------- ----------- ----------- 1           1           2011        1 2           3           NULL        3 3           2           2011        2 5           4           NULL        NULL 1           5           NULL        NULL 

So I am surprised that Jage's query (the 2nd option) works for you but not your original. It would be different story without the inner or rYear is null.

Think of the LEFT JOIN like this [1]

SELECT * from A left join B on A.sid = B.sid 

Keep everything in A, and where matched in the ON clause, keep B otherwise pad B columns with NULL. Add the WHERE clause [2]

where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5) AND (rYear = 2011 or rYear is null); 

Using the output from [1], CUT down based on the filter, applied AFTER the left join. With the rYear is null, it should still keep all A records, on the proviso that the rCode filter is matched. However, if the filter in rYear is only

AND (rYear in (2011,2012)) 

It's a different story, because where B was not matched, the rYear was padded with NULL, which won't match the rYear filter -> the entire row gets removed, including the A record. Such a filter on rYear would have gone into the ON clause as shown below, otherwise might as well make it an INNER JOIN.

SELECT * from A left join B on A.sid = B.sid AND (rYear in (2011,2012)) where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)