Databases SQL Reporting by Example Day 3: Getting Good at Grouping Janis' Schedule

Steve Rogers
Steve Rogers
2,343 Points

Left Outer Join question

So I got an unexpected result when doing a left outer join. Maybe I created the join wrongly though.

WITH Janis_Classes AS (SELECT * FROM teachers JOIN classes ON teachers.id = classes.teacher_id JOIN subjects ON subjects.id = classes.subject_id WHERE first_name = 'Janis' AND last_name = 'Ambrose' ) SELECT * FROM Periods LEFT OUTER JOIN Janis_Classes ON Period_id = Period_id

...gave me a result that showed basically everything on both tables but not period 4 where Janis does not have a class.

While...

WITH Janis_Classes AS (SELECT * FROM teachers JOIN classes ON teachers.id = classes.teacher_id JOIN subjects ON subjects.id = classes.subject_id WHERE first_name = 'Janis' AND last_name = 'Ambrose' ) SELECT * FROM Periods LEFT OUTER JOIN Janis_Classes ON Periods.id = Period_id

...gave me a nice table that only showed the 7 rows with the 7 periods and the classes Janis had for them, including period 4.

The only difference between the two queries was at the end of the Left Outer Join: Period_id = Period_id vs. Periods.id = Period_id

Can anyone explain why I got such different results. I don't really understand how I am deciding what to select to accomplish this particular join.

Thanks.

1 Answer

Steven Parker
Steven Parker
177,536 Points

For the JOIN criteria to work correctly, the terms being compared must each come from a different table. In the first example, the term "Period_id" from one table is being compared with itself, which is always true. So you get only the rows from that table joined with the columns from the other.

But when you specify the comparison correctly, you get all the rows from the first table, whether or not they correspond to rows in the other, along with the columns from the second table when they do.