Tuesday, 28 August 2018

MySQL left join that does not return null values ​​for the joined table

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?

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)

0 comments:

Post a Comment