intro
Let’s learn everything you need to know to master subqueries. Reduce the number of queries you perform and start embedding advanced query logic into single SQL queries.
Many logical operations require several queries to be executed or involve queries with many JOINs. In the first case, you have to work at the application level to get the desired result. In the second, you may have to write long queries that are difficult to read and maintain. Luckily, subqueries exist!
In SQL, a subquery allows you to execute a query within the scope of another query. This means that subqueries do not involve the drawbacks mentioned above.
Let’s now dig into SQL subqueries. At the end of this blog post, you will be a subquery master!
What Is a Subquery in SQL?
In SQL, a subquery is a query that is nested inside another query. SQL subqueries are also called nested queries or inner queries, while the SQL statement containing the subquery is typically referred to as an outer query.
Generally, a subquery in SQL is used to retrieve data that will be used in the outer query. In this scenario, the inner query is executed first. Then, its results are used to evaluate the outer query. However, keep in mind that the order of execution of the inner query and the outer query depends on the specific scenario.
You can use SQL subqueries in SELECT, INSERT, UPDATE, and DELETE statements. Specifically, you can nest a subquery in the SELECT, FROM, WHERE, JOIN, and HAVING SQL clauses. Also, you can adopt SQL queries in conjunction with several SQL operators, such as =, <, >, >=, <=, IN, NOT IN, EXISTS, NOT EXISTS, and more.
This is what an SQL query that involves asubquery looks like:
1
-- selecting the list of users
2
-- with the longest nicknames
3
SELECT id, nickname
4
FROM users
5
WHERE LENGTH(nickname) > (
6
SELECT AVG(LENGTH(nickname))
7
FROM users
8
)
Here, the subquery is the following query embedded between parentheses:
1
SELECT AVG(LENGTH(nickname))
2
FROM users
Let’s now find out the benefits of using SQL subqueries.
Why Use SQL Subqueries?
Subqueries are particularly useful because they allow you to embed specific query logic into a more general query. Thus, by running a single query, you can get results that would naturally require multiple queries. This can lead to benefits in terms of readability, maintainability, and even performance.
Let’s better understand why subqueries are important with an example. Let’s assume you want to find all the users with above-average points. You may be tempted to write the following SQL query:
1
SELECT id, nickname
2
FROM users
3
WHERE points > AVG(points)
But keep in mind that you cannot use aggregate operators inside the WHERE clause. So, the query above will result in an error.
Instead, what you need to do is:
First, launch the following query:
1
-- getting the average number of points
2
SELECT AVG(points)
3
FROM users
Let’s assume this will return 420.
Then, you can achieve the end goal with the query below:
1
SELECT id, nickname
2
FROM users
3
WHERE points > 420
As you can see, finding the list of users with above-average points would require two queries. This also means that if you want to perform this operation programmatically, you would have to save the result of the first query in a variable at the application level, and then use it in the WHERE clause of the second.
Otherwise, you can simply achieve the same result with a single query involving a subquery:
1
SELECT id, nickname
2
FROM users
3
WHERE points > (
4
SELECT AVG(points)
5
FROM users
6
)
What happens here is that:
1
SELECT AVG(points)
2
FROM users
In other terms, this logic corresponds exactly to the two queries above, but in a single, more readable, query. This was just a simple example to understand how powerful SQL subqueries are. Keep in mind that a subquery in SQL can come in handy in many other situations.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
Let us now try to understand more about how you can use a subquery in SQL.
How to Use Subqueries in SQL
When it comes to SQL subqueries, there are a few rules to keep in mind:
These rules define how you can use a subquery in SQL. Let’s now look at some examples to understand the syntax required by SQL subqueries.
Subqueries in CRUD Operations
CRUD stands for Create, Read, Update, and Delete and includes the four most common SQL statements you generally perform in a database:
Let's see how to use SQL subqueries in these SQL statements.
Subqueries in an INSERT Clause
You can use a subquery in the WHERE clause of an INSERT statement to select data from one or more tables and insert them into a new table.
Syntax:
1
INSERT INTO new_table_name [ (column1 [, column2 ]) ]
2
SELECT [ * | (column1 [, column2 ]) ]
3
FROM existing_table
4
[ WHERE ... [ (SELECT ...) ] ]
Example:
1
INSERT INTO top_users_copy
2
SELECT * FROM users
3
WHERE id IN (
4
SELECT id
5
FROM users
6
WHERE points > 200
7
)
This query will copy all users with more than 200 points into the best_users_copy new table.
Subqueries in a SELECT Clause
SQL subqueries are allowed in the SELECT, FROM, WHERE, JOIN, and HAVING SQL clauses of a SELECT query.
1
SELECT column_1 [, column_2 ] [, (SELECT ...) ]
2
FROM exisisting_table
3
[ JOIN [ (SELECT ...) ] ON ... ]
4
[ WHERE ... [ (SELECT ...) ] ]
5
[
6
GROUP BY column_1 [, column_2 ]
7
HAVING ... [ (SELECT ...) ] ]
8
]
Example:
1
SELECT id, nickname, points, (SELECT AVG(points) FROM users) AS avg_points
2
FROM users
This SQL query returns the list of users, with a special column representing the number of average points in each row.
Subqueries in an UPDATE Clause
You can adopt SQL subqueries in the WHERE clause of an UPDATE statement to select the data set that needs to be updated.
Syntax:
1
UPDATE existing_table
2
SET column_name = new_value
3
[ WHERE ... [ (SELECT ...) ] ]
Example:
1
UPDATE users
2
SET points = points * 0.5
3
WHERE id IN (
4
SELECT id FROM top_users_copy
5
)
This query reduces the points of top users in users by half.
Subqueries in a DELETE Clause
You can employ one or more subqueries in the WHERE clause of a DELETE SQL statement to select the data to delete.
Syntax:
1
DELETE FROM existing_table
2
[ WHERE ... [ (SELECT ...) ] ]
Example:
1
DELETE FROM users
2
WHERE id IN (
3
SELECT id FROM top_users_copy
4
)
This query deletes all top users read from top_users_copy in the users table.
FAQ About Subqueries
Let’s now answer some questions about SQL subqueries.
A correlated subquery is an SQL subquery that depends on values from the outer. Since the DBMS engine may have to execute the subquery once for each row processed by the outer query, queries involving correlated subqueries can be very slow.
This is an example of a correlated subquery:
1
SELECT id, nickname, points, (SELECT AVG(points)
2
FROM users
3
WHERE role = U.role) AS avg_role_points
4
FROM users U
This query returns the list of users. For each user, there is an extra column containing the average number of points of users with their same role.
Can you use 2 subqueries in an SQL query?
Yes, an outer SQL query can involve an arbitrary number of subqueries. There is no limit to the number of subqueries you can use in an SQL query.
What are the types of subqueries?
There are several types of subqueries. The most important ones are:
Can you JOIN subqueries in SQL?
Yes, you can use subqueries in the JOIN clause and even directly join several subqueries, as in the example below:
1
SELECT U1.id, U1.nickname
2
FROM
3
(
4
SELECT id, points
5
FROM users
6
WHERE points > 200
7
) AS U1
8
JOIN
9
(
10
SELECT user_id
11
FROM subscriptions
12
WHERE deleted_by IS NOT NULL
13
) AS U2 on U1.id = U2.user_id
Are subqueries faster than JOINs?
Usually, JOINs are faster than subqueries. This is because most RDBMS technologies can create a better execution plan when it comes to JOIN queries. At the same time, it depends a lot on the DBMS version and the specific case, so it is hard to say. What is for sure is that JOINs and subqueries enable you to get the same result in several scenarios. The advantage of subqueries is that they generally lead to a more readable query than JOINs. So, if performance is not too important, subqueries may be preferable.
