I always considered myself SQL advanced however when I came to the following sql code, I couldn’t figure out the correct answer: The below table has 5 rows(from on the first sql ), what will be the result of the 2nd query?
The 2nd query uses a left join, and a filter p.pos = ‘400’ which doesn’t match any row; The result will still contain 5 rows, not empty! See below the screenshot
If p.pos = ‘4’, you’ll end up with 5 rows again, but this time one row matched, like below
So the ON clause means return the matched rows (one row, i.e. p.pos = ‘4’) PLUS ALL other rows from left table p!
If however this condition is put into WHERE, the left join will become an inner join therefore only the matched row will return, like below
I hope this simple demo shows how LEFT JOIN works – just remember, it means to return ALL rows from the left table, regardless of where the filter is placed!