SUBQUERY

The Complete Guide to SQL Subqueries

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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 SELECTINSERTUPDATE, and DELETE statements. Specifically, you can nest a subquery in the SELECTFROMWHEREJOIN, and HAVING SQL clauses. Also, you can adopt SQL queries in conjunction with several SQL operators, such as =<>>=<=INNOT INEXISTSNOT EXISTS, and more.

This is what an SQL query that involves asubquery looks like:

Copy
        
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:

Copy
        
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:

Copy
        
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:

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

First, launch the following query:

Copy
        
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:

Copy
        
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:

Copy
        
1 SELECT id, nickname 2 FROM users 3 WHERE points > ( 4     SELECT AVG(points) 5     FROM users 6 )
Example of a single query involving a subquery.
Example of a single query involving a subquery

What happens here is that:

  1. The DBMS engine will execute this SQL subquery first:
Copy
        
1 SELECT AVG(points) 2 FROM users
  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.

DbVisualizer logo

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:

  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 INAVG(), 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:

Copy
        
1 INSERT INTO new_table_name [ (column1 [, column2 ]) ] 2 SELECT [ * | (column1 [, column2 ]) ] 3 FROM existing_table 4 [ WHERE ... [ (SELECT ...) ] ]

Example:

Copy
        
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 an INSERT Clause.
The subquery INSERT example in DbVisualizer

Subqueries in a SELECT Clause

SQL subqueries are allowed in the SELECTFROMWHEREJOIN, and HAVING SQL clauses of a SELECT query.

Copy
        
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:

Copy
        
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 a SELECT Clause.
The subquery SELECT example in DbVisualizer

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:

Copy
        
1 UPDATE existing_table 2 SET column_name = new_value 3 [ WHERE ... [ (SELECT ...) ] ]

Example:

Copy
        
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 an UPDATE Clause.
The subquery UPDATE example in DbVisualizer

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:

Copy
        
1 DELETE FROM existing_table 2 [ WHERE ... [ (SELECT ...) ] ]

Example:

Copy
        
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.

Subqueries in a DELETE Clause.
The subquery DELETE example in DbVisualizer

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:

Copy
        
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:

  • 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:

Copy
        
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.

Dbvis download link img

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.

About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

A Guide to Subqueries in MySQL

author Lukas Vileikis tags MySQL SUBQUERY 6 MINS 2023-03-30
title

SQL Server for Mac: The Ultimate Guide to the Best SQL Server Client

author TheTable tags SQL SERVER 7 min 2024-03-01
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Outer Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 11 min 2024-02-19
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15
title

SQL Views: A Comprehensive Guide

author Bonnie tags VIEWS 5 min 2024-02-12
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗