#1
  1. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    699
    Rep Power
    95

    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...
    SELECT distinct
    `SCHEDULES`.student_last_name,
    `SCHEDULES`.section,
    `SCHEDULES`.id,
    `suggestions`.teacher_suggestion3,
    `suggestions`.teacher_suggestion4
    FROM `SCHEDULES`
    LEFT JOIN `suggestions`
    ON
    `SCHEDULES`.id = `suggestions`.id AND
    `SCHEDULES`.course_number = `suggestions`.course_number AND
    `SCHEDULES`.section = `suggestions`.section
    WHERE
    `SCHEDULES`.course_number = `suggestions`.course_number and
    `SCHEDULES`.section = `suggestions`.section
    Thanks,
    Evan
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Nov 2003
    Posts
    699
    Rep Power
    95
    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.
    Evan
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,242
    Rep Power
    4279
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo