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)

Posted by Claudio Paladini
Claudio Paladini
13,403 Points

How is this different from a join?

As I understand this, this subquery creates a temporary table which is then joined to another.

How is it different from a non-subquerey join?

Using subqueries to filter is clear to me, but this isn't..

1 Answer

Steven Parker
Steven Parker
231,269 Points
Steven Parker
Steven Parker
231,269 Points

For some purposes, there's not much obvious difference. But if you study query plans (an advanced topic), you might find some differences in regards to efficiency. And subqueries don't always act as a table, they can be used to return a single value.

The more obvious differences are found in the ways they can be used:

  • JOINs are used in the FROM clause, but subqueries can be used in most clauses (SELECT, WHERE, etc.)
  • JOINs return rows, but subqueries return a result that might be used as a column or filter term
  • subqueries can have their own filters or aggregate functions
Claudio Paladini
Claudio Paladini
13,403 Points

grazie mille!