Watch out for LEFT JOIN with additional conditions in SQL

SELECT  *
FROM    a
LEFT JOIN
b
ON      b.a_id = a.id
WHERE   b.column = 'sth'

Return the same result as (but less efficient)

SELECT  *
FROM    a
INNER JOIN
b
ON      b.a_id = a.id
WHERE   b.column = 'sth'

Put additional conditions on left table on left join will actually filter out those rows where b.column is null. Because  NULL in SQL doesn't equal to anything. So the only exception to put extra condition on left table of left join is to check b.column is null or not.

If you actually want to return those  b.column = 'sth' and at the same time with all records of table a, then the condition should be moved into ON clause:

SELECT  *
FROM    a
LEFT JOIN
        b
ON      b.a_id = a.id
AND b.column = 'sth'