Subqueries are a helpful feature of SQL. How do you write them? And, just as importantly,
how do you know when to use them?
It’s one thing to know that a subquery can be used in a few different places, but if
you don’t know when to use them, then they will just make it harder to write the query you need.
In this video, you’ll learn what a subquery is, find out when you need to use a subquery,
and when to use a subquery or a join, with examples.
Welcome to the Database Star YouTube channel - the place for developers
looking to improve their database and SQL skills.
Let’s get into the video.
So, what is a subquery?
A subquery is a Select query that is contained within another query.
There are a few places you can use a subquery,
which you might have seen in other YouTube videos or guides.
We’ll see some examples of this shortly.
But, putting a query inside another query sounds a bit complicated, right?
Why would you want to do this?
The main reason you would want to use a subquery is when the results of one
query need to be used in another query to get the results you need.
You may not be able to get the result you want simply
by joining tables together without a subquery.
You could write one query to get a result, then use your application code or a stored procedure
to store that result and use it in a second query. That’s definitely an option.
However, databases are very efficient and handling data and processing queries.
Combining two queries by using a subquery inside another query
will let the database do what does best, and give you the result you’re looking for.
So, the main reason to use a subquery is when you want the results of one query
to be used inside another query.
Let’s see some examples of this.
We’ll start with a simple example first. Let’s say you have a list of products,
and each product has a price, as you can see here.
You’ve been asked to write a query to find all products that have a price greater than
the average price of a product. This could be for a report or a data extract or some
application feature. In any case, you’ll need to write an SQL query.
Let’s see that requirement again: find all products that have a price
greater than the average price of a product.
How can we write a query like this?
Step 1 is to find the average price of a product. We can do this using the AVG function in SQL.
Our query may look like this: select AVG(price) from product.
This query will return a single value which is the average price of the products.
In this example, it’s 25.
Step 2 is to write a query that finds all products. This can be done with a Select id,
product name, price from product. We can run this query and see all of the data in the table.
Step 3 is to filter this query by the result of our first query, which found the average. We only
want to see products where the price is greater than the average. So we add a WHERE clause,
to say WHERE price is greater than 25. This 25 value is the value we got from the first query.
When we run this query, this is the result we get. We can see a few products,
all of which have their price greater than 25.
In this example, we needed the result of the AVG query
to be used in another query to find all product details.
Why did we do this? We couldn’t get the average of all of the products in any other way.
We could have used the AVG function in the same query,
but we would then have to group the data by the non-aggregated columns,
which is everything but the AVG, and this would not give us what we needed.
We could have done this using AVG as a window function, or perhaps creating a view to join to.
Those are valid options, but may not work as well, and could be more complicated.
The only problem with the approach we took is that we found the average and hardcoded, or
manually typed the value into the main query. This would be an issue if the underlying data changes.
A subquery will let us combine all of these steps into a single step.
We can do this by replacing the 25 in our WHERE clause with the query that finds the
average price. We add a set of brackets, then add our Select AVG query inside here.
This query now says, find all products where the price is greater than the average of all products.
When we run this query, we’ll get the result we want.
It’s the same as having the queries separated,
but this does not require any manual steps and can still work when the product data is updated.
One thing to keep in mind about this kind of subquery is that the
operator in the WHERE clause needs to be compatible with the subquery result.
This means that some operators expect a single value and some can handle single or multiple.
This example uses greater than, which expects a single value.
The subquery needs to return a single value. If it does not, then there will be an error.
This query is OK because the subquery uses the AVG function and that function will
always return a single value. But, if we had just the price, with no AVG function,
then there could be multiple values returned, and the query would show an error.
So that’s a simple example of a subquery.
There may be other examples of subqueries that you see
in your code or in examples online. They could look something like this:
This shows what a subquery can do. However, it doesn’t quite explain the benefits of a subquery.
Sometimes a subquery can be written using a join. This subquery is an example of that.
We could write this query as one that joins these two tables, without a subquery.
Here’s what it would look like. We can see the product table is joined to the category table,
and categories are filtered on their active status. This will give us the
same results. There is no need to write a subquery to get results in this example.
So, if there are two ways to do this, which one should you choose?
Generally, it’s better to use queries with joins rather than subqueries.
This is because the database can work out what’s needed from joins pretty efficiently.
Joins are also arguably easier to understand - but you may not agree.
So, if you can write a query using joins instead of using a subquery, consider using joins.
One exception to this that I’ve seen is where the subquery filters a lot of rows.
If your subquery is on a table that has millions of rows, for example, and the subquery has a WHERE
clause that filters this to a much smaller number, this may be better than joining to the table.
This is because the database would have less data to work with if its filtered inside the subquery
instead of a join. But, you might find the database handles both approaches in the same way.
If you’re not sure, I would suggest trying both approaches,
a join or a subquery, and seeing how they perform and what the execution plan looks like.
Now we’ve seen when you may need to use a subquery, and whether it
can be used instead of a join, I wanted to share some tips on using subqueries.
First, when you are working on a query with a subquery, it may be one that’s a little
more complicated than most. So, break it down and write the query in parts.
Aim to write the subquery first, run it, and check the results are correct. Write an outer
query to see all data, then add your subquery to it, and do any other filtering you need.
The next tip is that performance of a subquery depends on the data size, any indexes, and whether
it’s executed for every row. Subqueries can be placed in several positions: the Select clause,
the From clause, or the Where clause. Depending on how your query is written, and the database,
this subquery could be executed just once, or once for every row. This could impact performance.
Next, a subquery is a good replacement for a temporary table.
If you have some application code or a stored procedure or a script that uses a temporary table,
you may want to experiment with rewriting the query to use a subquery. You might find it’s
easier or performs better. Or, you might find it performs worse. It could be worth a shot.
Finally, a correlated subquery is a handy feature, but often not required.
A correlated subquery is a subquery that refers to records in the outer row. It can often,
but not always, be written using a join instead, and may perform better as a join. So, if you
use a query with a correlated subquery, it may perform worse than if it was written using a join.
If it’s possible to write it using a join instead, try that.
And that brings us to the end of this video on subqueries in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful? Was it the criteria for when
you would need a subquery, or the tips on writing subqueries, or something else?
Thanks for watching.