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 Querying Relational Databases Subqueries Using a Subquery to Create a Temporary Table (Part 2)

John Yzaguirre
John Yzaguirre
22,025 Points
Posted by John Yzaguirre
John Yzaguirre
22,025 Points

Why 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
Steven Parker
231,268 Points
Steven Parker
Steven Parker
231,268 Points

:point_right: 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.

Giuseppe Ardito
Giuseppe Ardito
14,130 Points
Giuseppe Ardito
Giuseppe Ardito
14,130 Points

I 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?

Matthew Stevenson
Matthew Stevenson
4,877 Points
Matthew Stevenson
Matthew Stevenson
4,877 Points

I'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.