Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community!
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.Start your free trial
Steve Rogers3,285 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.
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.
Steven Parker225,664 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.