Using subquerires in conjunction with the IN clause.
Subqueries are one of the most advanced concepts to learn in SQL query writing. Seeing lots of examples and getting lots of practice will help solidify the concept.
As you begin using subqueries, be advised that a poorly structured subquery written against a large table or tables can impact overall query performance. Be careful if you are writing subqueries against data sets that consume tables with row counts in the million-plus row range.
This course does not cover the concept called Correlated Subqueries, which are slightly different in how they tie in with the outer query. Most of the time a regular subquery will do what you need, but correlated subqueries can be handy, too. These are especially heavy weight on the database engine, as they run the subquery many times; once per row in the outer query.
See all of the SQL used in Querying Relational Databases in the Querying Relational Databases Cheat Sheet.
In this video, we're going to see how to use the IN keyword with a subquery. 0:00 Let's have a quick reminder of what the IN clause is. 0:05 You can use the IN keyword in your WHERE statement to enumerate of a list of things 0:09 to filter by. 0:14 Lets say we wanted to see specific car sales for ID's 1, 3, and 5. 0:16 Now, let's say we don't know exactly which car IDs we need. 0:23 This is where a subquery can help us out. 0:27 If we wanted to see all sales of cars with the model year of 2015, 0:30 we can replace this enumerated list of IDs with a query 0:36 that gets us the car IDs that meets our criteria. 0:40 First, let's write the query that gets the IDs we want. 0:43 So SELECT FROM 0:46 Car WHERE ModelYear = 2015. 0:54 This becomes our subquery that we can put inside 1:02 the parentheses replacing 1, 3, and 5. 1:07 You can also use a subquery in a NOT IN, as well. 1:16 Let's say, we really wanted to see all car sales except for the model year 2015. 1:20 We just have to add NOT In front of IN to get a different result. 1:25 The one trick to remember here is that you can only 1:34 select one column in your subquery. 1:38 if you have more than one you'll get an error. 1:42 So to review. 1:46 You can use a subquery in an IN clause. 1:47 When writing a subquery in this way you should only return one column of data. 1:50 This creates a set of values in the IN clause to search for. 1:55 Instead of writing the set manually ourselves, 1:59 we can get the database to do the heavy lifting for us. 2:02 [SOUND] You can also use a subquery in a NOT IN clause. 2:05
You need to sign up for Treehouse in order to download course files.Sign up