The Complete Guide to SQL Subqueries

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:

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

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:

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:

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

First, launch the following query:

Let’s assume this will return 420.

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

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:

What happens here is that:

1. The DBMS engine will execute this SQL subquery first:

1. 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:

1. A subquery in SQL must be enclosed within round brackets.
2. Many DBMSs, such as Oracle, MySQL or SQL Server, may require giving SQL subqueries an alias.
3. You cannot use a subquery in the ORDER BY clause.
4. The BETWEEN operator does not support subqueries.
5. 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:

1. INSERT
2. SELECT
3. UPDATE
4. 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:

Example:

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.

Example:

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:

Example:

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:

Example:

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.

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:

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:

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.