intro
Let’s master SQL subqueries. Reduce the number of queries you perform and start embedding advanced query logic into single SQL queries.
To perform many tasks, you may need to launch several queries or use queries with many JOINs. In the first case, you might prefer to operate 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, SQL subqueries exist!
An SQL subquery allows you to execute a query within the scope of another query. This means that subqueries do not involve any of the drawbacks mentioned above.
Let’s now dig into subqueries in SQL. At the end of this blog post, you will be a subquery SQL master!
What Is a Subquery in SQL?
An SQL 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.
Typically, 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.
For more details, follow these links from the official documentations:
SQL Subquery Example
This is what an query that involves an SQL subquery 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;
Time to discover 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. That can lead to benefits in terms of readability, maintainability, and even performance.
Let’s better understand why SQL subqueries are important with an example. 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 above query 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;
Now, for this example, suppose the result is 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.
Now, remember that a subquery in SQL can come in handy in many other situations. Learn more about how you can use a subquery in SQL.
When to Use a Subquery in SQL
SQL subqueries let you nest queries within other queries, making data retrieval more dynamic and efficient. These make them ideal for the following scenarios:
How to Use Subqueries in SQL
When it comes to subqueries, there are a few rules to consider:
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:
See how to use SQL subqueries in these CRUD 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. To do so, it selects the users with more than 200 points with an SQL subquery.
Subqueries in a SELECT Clause
SQL subqueries are allowed in the SELECT, FROM, WHERE, JOIN, and HAVING SQL clauses of a SELECT query.
Syntax:
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
2
id,
3
nickname,
4
points,
5
(SELECT AVG(points) FROM users) AS avg_points
6
FROM users;
The above query returns the list of users, with a special field representing the number of average points in each row. This field, avg_points is populated on the fly using the subquery SQL mechanism.
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. To do so, it retrieves the IDs of the top users with a subquery.
Subqueries in a DELETE Clause
You can employ one or more SQL 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. Again, the top users are retrieved using a subquery.
How to Handle Long Queries Relying on the SQL Subquery Mechanism
SQL subqueries are great for compartmentalizing specific sections of business logic in data selection. However, they can make a query long and complex. To manage lengthy queries with one or more subqueries, the best approach is to use an SQL formatter.
Ideally, your database client should offer this capability, such as DbVisualizer, which lets you format SQL queries with just a few clicks.
For example, suppose you have the following long and messy query with multiple subqueries:
1
SELECT e.employee_id, e.employee_name, (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) AS department, (SELECT AVG(s.salary)
2
FROM salaries s WHERE s.department_id = e.department_id) AS avg_department_salary
3
FROM employees e
4
WHERE e.salary > (SELECT AVG(salary) FROM employees);
To format it in DbVisualizer, simply paste the query, right-click on it, and select the "Format SQL > Format Buffer" option.
Selecting the “Format SQL > Format Buffer” option
This will automatically format the SQL subquery statement for you, producing a much more readable result:
The resulting SQL query will be:
1
SELECT
2
e.employee_id,
3
e.employee_name,
4
( SELECT
5
d.department_name
6
FROM
7
departments d
8
WHERE
9
d.department_id = e.department_id
10
) AS department,
11
( SELECT
12
AVG(s.salary)
13
FROM
14
salaries s
15
WHERE
16
s.department_id = e.department_id
17
) AS avg_department_salary
18
FROM
19
employees e
20
WHERE
21
e.salary >
22
( SELECT
23
AVG(salary)
24
FROM
25
employees
26
);
Much better, right?
Note: The query formatting feature is only available in DbVisualizer Pro. You can test it with a 21-day free trial.
Conclusion
In this blog post, you learned what an SQL subquery is, when to use it, and how to apply it effectively in SQL. You also explored real-world subquery SQL examples involving CRUD operations.
SQL subqueries are a powerful tool for enhancing your queries, enabling you to perform multiple operations simultaneously. Yet, since subqueries impact both performance and readability, it is important to use them wisely.
As demonstrated, using a database client with built-in SQL subquery support and formatting features, like DbVisualizer, can help you manage subqueries more efficiently and avoid unnecessary complexity. Try DbVisualizer for free!
FAQ
What is a correlated subquery in SQL?
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:
What is an example of an SQL JOIN subquery?
You can write an SQL JOIN subquery 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
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.
What is an example of an Oracle SQL subquery?
In Oracle, subqueries follow the same syntax discussed earlier. So, you can use an Oracle SQL subquery as shown below:
1
SELECT employee_id, name
2
FROM employees
3
WHERE department_id = (SELECT department_id FROM departments WHERE name = 'Sales')
This query retrieves employees who belong to the "Sales" department. The subquery first finds the department_id for "Sales," and the main query selects employees matching that ID.
What is the difference between an SQL subquery and a derived table?
An SQL subquery is a query nested within another query, often used to return a single value or a set of values. On the other hand, a derived table, is a type of subquery that generates a temporary table or result set in the scope of a FROM clause. Unlike regular subqueries, derived tables are explicitly given an alias and are typically used only to provide a result set for the outer query to operate on.
