December 2nd, 2004, 10:43 PM
LEFT JOIN does not work when one table is empty
I had a simple query that worked when both tables had data but if the second table is empty the query returns nothing. I converted my query to include a LEFT JOIN which should work in my case but...
December 2nd, 2004, 11:21 PM
Here I am answering my own question.
Apparently in LEFT OUTER JOINs it's not good to have same clauses in the ON and WHERE.
It took me over an hour to figure this one out. Ouch!
I hope this helps.
December 3rd, 2004, 05:25 AM
it's not just when the right table is empty
what happens is that the left outer join works fine, it returns all rows from the left table, plus matching rows from the right table, if any
if a given row of the left table has no matching row, then the way the left outer join works, all the values of that result row for the columns that would've come from the right table will be set to null
but then the WHERE clause takes over, and compares left and right columns after the joined result row has been constructed, and guess what, one of them is null and of course nothing is equal to null
if you were to replace the WHERE clause in the above query with
... WHERE suggestions.id IS NULL
then you would receive all rows from the left table which do not have a matching row from the right table (and if the right table is empty, then this would be all rows in the left table) -- the equivalent of a NOT EXISTS (subquery)