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! While you're at it, check out some resources Treehouse students have shared here.

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

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

Posted by Steve Rogers
Steve Rogers
3,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.

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
229,732 Points
Steven Parker
Steven Parker
229,732 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.