DbVisualizer

Posted on Apr 3, 2023 • Originally published at dbvis.com

The Complete Guide to SQL Subqueries

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:

-- selecting the list of users
-- with the longest nicknames
SELECT id, nickname
FROM users
WHERE LENGTH(nickname) > (
    SELECT AVG(LENGTH(nickname))
    FROM users
)

Here, the subquery is the following query embedded between parentheses:

SELECT AVG(LENGTH(nickname))
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:

SELECT id, nickname
FROM users
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:

Find the average number of points
Select all users whose points are greater than the average number

First, launch the following query:

-- getting the average number of points
SELECT AVG(points)
FROM users

Let’s assume this will return 420.

Then, you can achieve the end goal with the query below:

SELECT id, nickname
FROM users
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:

SELECT id, nickname
FROM users
WHERE points > (
    SELECT AVG(points)
    FROM users
)

Example of a single query involving a subquery

What happens here is that:

The DBMS engine will execute this SQL subquery first:

SELECT AVG(points)
FROM users

Then, it will replace the subquery with its result and execute the outer query accordingly.

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.

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:

A subquery in SQL must be enclosed within round brackets.
Many DBMSs, such as Oracle, MySQL or SQL Server, may require giving SQL subqueries an alias.
You cannot use a subquery in the ORDER BY clause.
The BETWEEN operator does not support subqueries.
You can use a SQL subquery that returns multiple rows only with multiple value SQL operators or aggregate functions, such as IN, AVG(), and MAX().

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:

INSERT
SELECT
UPDATE
DELETE

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:

INSERT INTO new_table_name [ (column1 [, column2 ]) 
SELECT [ * | (column1 [, column2 ]) ]
FROM existing_table
[ WHERE ... [ (SELECT ...) ] ]

Example:

INSERT INTO top_users_copy
SELECT * FROM users
WHERE id IN (
    SELECT id
    FROM users
    WHERE points > 200
)

Subqueries in a SELECT Clause

SQL subqueries are allowed in the SELECT, FROM, WHERE, JOIN, and HAVING SQL clauses of a SELECT query.

SELECT column_1 [, column_2 ] [, (SELECT ...) ]
FROM exisisting_table
[ JOIN [ (SELECT ...) ] ON ... ]
[ WHERE ... [ (SELECT ...) ] ]
[
    GROUP BY column_1 [, column_2 ]
    HAVING ... [ (SELECT ...) ] ]
]

Example:

SELECT id, nickname, points, (SELECT AVG(points) FROM users) AS avg_points
FROM users

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:

UPDATE existing_table
SET column_name = new_value
[ WHERE ... [ (SELECT ...) ] ]

Example:

UPDATE users
SET points = points * 0.5
WHERE id IN (
    SELECT id FROM top_users_copy
)

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:

DELETE FROM existing_table
[ WHERE ... [ (SELECT ...) ] ]

Example:

DELETE FROM users
WHERE id IN (
    SELECT id FROM top_users_copy
)

FAQ About Subqueries

Let’s now answer some questions about SQL subqueries.

What is a correlated subquery?

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:

SELECT id, nickname, points, (SELECT AVG(points)
    FROM users
    WHERE role = U.role) AS avg_role_points
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:

Single-row subquery
Multi-row subquery
Single-column subquery
Multi-column subquery
Correlated subquery

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:

SELECT U1.id, U1.nickname
FROM
(
    SELECT id, points
    FROM users
    WHERE points > 200
) AS U1
JOIN
(
    SELECT user_id
    FROM subscriptions
    WHERE deleted_by IS NOT NULL
) AS U2 on U1.id = U2.user_id

This query joins all users with more than 200 points with their subscriptions that have not been deleted yet.

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.