1. Subqueries
Thanks for hopping back in! Now that you've mastered the basics of conditional logic, it's time to explore subqueries.
2. What are subqueries?
Subqueries allow the result of one query to be used by another query.
Here's the idea of a subquery; start with a large table, filter it using a subquery, and use these results to generate a final output.
Subqueries allow for more readable and modular logic, making data manipulation and transformation much easier!
Subqueries are typically used in one-of-two ways, retrieving records directly from a subquery and to filter dynamically using `WHERE` ... `IN.`
3. Subqueries and FROM
The most traditional way to use subqueries is to retrieve data from the result set of another query. This decouples data manipulation and transformation from analysis.
More importantly, it makes queries easier to ready and understand.
Here is the general syntax to `SELECT` results from a subquery. This should look and feel like a traditional query. But, rather than pulling records `FROM` a table, they're pulled from a query nested in parenthesis after `FROM`.
This structure makes the entire query quite portable and easy to adjust as needed.
Like regular queries, a subquery can still use `JOIN` and `WHERE` clauses.
4. Before a subquery
Let’s write a query, then show how we can use a subquery to simplify it. Here, we calculate average and max temperature differentials on freezing winter days, but the query is hard to follow and needs repeated logic. A subquery will help!
5. After a subquery
Check it out! In the subquery, we're finding the temperature differential from all winter days with a temperature below freezing. These results would look something like this output.
Next, we're retrieving and aggregating records from the subquery to generate the final output. This structure makes it much easier to isolate our analysis from the actual data preparation.
Organizing our query this way is handy if we wanted to do something like convert temperatures to Celsius.
6. WHERE ... IN ( ... )
Subqueries can also be used to create a small result set that's eventually used in a `WHERE` clause to check whether a value is `IN` a non-constant set of results.
The syntax looks like this.
Typically, the query in parenthesis after `WHERE` `IN` returns a single column. This allows us to check whether or not a certain field is in that result set.
This pattern can be used in other places in a query, and can even be paired with tools like `AVG`, `MAX`, and `MIN`.
7. WHERE ... IN( ... )
In this example, we want to find the temperature of all home games that our sports team won. To do this, we use a subquery to return the date for all home wins in the `game_schedule` table. This helps to filter the records `SELECT`'ed from `weather`.
8. WHERE ... IN ( ... )
The output looks like this. Here's the best part; if we instead wanted to explore "away" "losses", all we'd have to do is adjust the subquery, and we'd have a new result set!
9. Let's practice!
Now it's your turn! Let's reinforce subqueries with some practice!