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 trialJohn Yzaguirre
22,025 PointsWhy does LEFT OUTER JOIN work but JOIN does not?
In this video, things get pretty complicated and it's hard to wrap my head around it. So I was playing around with the query and noticed JOIN doesn't give an error, it instead returns no results. I'm sure it's something to do with the aggregated data from the sub queries but still SO CONFUSING!
Could someone try to explain why that is happening? THANKS!
SELECT sr.LastName, Loc1.StLouisAmount, Loc2.ColumbiaAmount FROM SalesRep AS sr
LEFT OUTER JOIN (
SELECT SalesRepID, SUM(SaleAmount) AS StLouisAmount
FROM Sale AS s WHERE s.LocationID = 1
GROUP BY SalesRepID
)
AS Loc1 ON sr.SalesRepID = Loc1.SalesRepID
LEFT OUTER JOIN (
SELECT SalesRepID, SUM(SaleAmount) AS ColumbiaAmount
FROM Sale AS s WHERE s.LocationID = 2
GROUP BY SalesRepID
)
AS Loc2 ON sr.SalesRepID = Loc2.SalesRepID;
2 Answers
Steven Parker
231,184 Points
A LEFT OUTER JOIN
returns all rows from the first ("left") table.
On the other hand, a "normal" or INNER JOIN
will only return rows where both tables (or subqueries) match on the specified terms.
So in a case where no rows match, the normal join will return no rows, but the LEFT OUTER JOIN
will still return rows for the entire first table.
Matthew Stevenson
4,877 PointsI'm sure you will have figured this out by now, but I had the same question and wanted to share the answer for anyone else.
If you look at the results we are getting from the query you can see that each sales rep can have sales in one location but not necessarily in the other (in fact there are no sales reps in this database with sales in both StLouis and Columbia).
If we use inner joins, the resulting data set will contain only rows that match on the specified relationships throughout all tables. In other words we would only see sales reps that exist in all three of our tables: SalesRep, Loc1 (temporary table), and Loc2 (temporary table). So if there was a sales rep with sales in both StLouis and Columbia, that guy would be returned for a query using inner joins. Because there are actually no sales reps that exist in all three tables, using inner joins will return nothing. The teachers notes under this video have a nice Venn diagram which illustrates the concept - https://teamtreehouse.com/library/inner-joins.
The left outer join is returning all users in the first table, then the matching users in the second table and the matching users in the 3rd table. See the Venn diagram on this page for 3 table left outer join - http://www.studio-eole.net/wp-content/uploads/2018/04/sql-join-multiple-tables-real-world-sql-join-examples-728x627.png.
I hope that's accurate and will help someone.
Giuseppe Ardito
14,130 PointsGiuseppe Ardito
14,130 PointsI am sorry, this is still not very clear to me: "sr.SalesRepID = Loc1.SalesRepID" and "sr.SalesRepID = Loc2.SalesRepID" should not always match? Should it not work also with an INNER JOIN?