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 IN with Subqueries to Filter Data

Leandro Botella Penalva
Leandro Botella Penalva
17,618 Points
Posted by Leandro Botella Penalva
Leandro Botella Penalva
17,618 Points

Subquery vs INNER JOIN

In this video Andrew uses a subquery to fetch sales from a car which model year is 2015.

SELECT * FROM Sale WHERE CarID IN (SELECT CarID FROM Car WHERE ModelYear = 2015);

However this could also be done using an INNER JOIN:

SELECT Sale.* FROM Sale INNER JOIN Car ON Sale.CarID = Car.CarID WHERE ModelYear = 2015;

Which way is considered more efficient and therefore a better practice?

4 Answers

Steven Parker
Steven Parker
229,732 Points
Steven Parker
Steven Parker
229,732 Points

There's probably not much difference in efficiency here.

These examples are simple enough that there would not be much difference. But by knowing both techniques, you'll be able to choose one to apply to more complicated real-world situations. You may even find it beneficial to use both at the same time for certain queries.

Bruce McMinn
Bruce McMinn
10,030 Points
Bruce McMinn
Bruce McMinn
10,030 Points

Thanks for asking this question, I had the same one.

jlampstack
jlampstack
23,932 Points
jlampstack
jlampstack
23,932 Points

I found this link to be helpful in explaining the differences

https://www.essentialsql.com/what-is-the-difference-between-a-join-and-subquery/

Stephanie Marek
Stephanie Marek
2,831 Points
Stephanie Marek
Stephanie Marek
2,831 Points

I agree, I don't see the benefit in this vs. a JOIN. Thanks for asking!