Here we will go into the complex topic of using subqueries to create temporary tables, sometimes called a Derived Table
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 will learn about another type of subquery, 0:00 the temporary table or the derived table. 0:04 Most of the time, when we create derived tables in a query 0:08 it's because the data we need isn't stored in the exact format or 0:12 structure that we want. 0:15 This happens a lot when doing a lot of aggregation or reporting queries. 0:17 Let's start with a simple example of a derived table. 0:22 In our last video, we used to subquery in the in clause. 0:27 Let's take that query and make it a derived table just to see how it works. 0:31 This is our query where we used a subquery to get all cars that are model year 0:36 2015. 0:41 Now, we can move the subquery to the from clause. 0:46 And make it behave just like another table. 0:51 Let's delete the WHERE clause. 0:54 And Alias the sale table as S. 0:57 Let's add an INNER JOIN. 1:02 This is to join the Sales table with the resulting dataset from the subquery. 1:06 In order to have join criteria, we have to give the dataset a name. 1:12 So we have to create an alias t for 1:16 temp, we could pick anything here. 1:22 This alias here has created a temporary table or a derived table. 1:27 You have to alias derived tables so you can refer to it in the join criteria. 1:41 Otherwise, the database would not be able to parse the query. 1:54 Notice, that we can now see the car ID column twice. 2:03 The second instance of the CarID column is from the derived table. 2:09 Using a subquery in the IN clause, didn't give you this ability. 2:13 Since derived tables are, basically, temporary in memory tables. 2:18 We can add any number of columns we want. 2:22 And display them in our final results. 2:25 Let's add model year to the temporary table to say how to do this. 2:27 Using temporary tables in this manner are a little more complex than 2:38 the simple IN statement, 2:42 but they give you much more flexibility. To review 2:44 To create a temporary or derived table. 2:48 [SOUND] Where you'd, 2:49 normally, put the second tables name, you write a subquery. 2:54 Unlike writing subqueries in an IN clause, you can add as many columns as you'd like. 2:58 Then to use it as a table in your join criteria, you must alias the table. 3:03 That alias can be used in the join criteria. 3:10 In our next video, 3:13 you will see a much more complex example of using derived tables. 3:14
You need to sign up for Treehouse in order to download course files.Sign up