SQL subqueries are basic tools if you want to communicate effectively with relational databases. In this article, I provide five subquery examples demonstrating how to use scalar, multirow, and correlated subqueries in the WHERE, FROM/JOIN, and SELECT clauses.
A subquery, or nested query, is a query placed within another SQL query. When requesting information from a database, you may find it necessary to include a subquery into the SELECT
, FROM
,
JOIN
, or WHERE
clause. However, you can also use subqueries when updating the database [i.e. in INSERT
, UPDATE
, and DELETE
statements].
There are several types of SQL subqueries:
- Scalar subqueries return a single value, or exactly one row and exactly one column.
- Multirow subqueries return either:
- One column with multiple rows [i.e. a list of values], or
- Multiple columns with multiple rows [i.e. tables].
- Correlated subqueries, where the inner query relies on information obtained from the outer query.
You can read more about the different types of SQL subqueries elsewhere; here, I want to focus on examples. As we all know, it’s always easier to grasp new concepts with real-world use cases. So let’s get started.
Let’s say we run an art gallery. We have a database with four tables: paintings
, artists
, collectors
, and sales
. You can see the data stored in each table below.
id | name | artist_id | listed_price |
11 | Miracle | 1 | 300.00 |
12 | Sunshine | 1 | 700.00 |
13 | Pretty woman | 2 | 2800.00 |
14 | Handsome man | 2 | 2300.00 |
15 | Barbie | 3 | 250.00 |
16 | Cool painting | 3 | 5000.00 |
17 | Black square #1000 | 3 | 50.00 |
18 | Mountains | 4 | 1300.00 |
id | first_name | last_name |
1 | Thomas | Black |
2 | Kate | Smith |
3 | Natali | Wein |
4 | Francesco | Benelli |
id | first_name | last_name |
101 | Brandon | Cooper |
102 | Laura | Fisher |
103 | Christina | Buffet |
104 | Steve | Stevenson |
id | date | painting_id | artist_id | collector_id | sales_price |
1001 | 2021-11-01 | 13 | 2 | 104 | 2500.00 |
1002 | 2021-11-10 | 14 | 2 | 102 | 2300.00 |
1003 | 2021-11-10 | 11 | 1 | 102 | 300.00 |
1004 | 2021-11-15 | 16 | 3 | 103 | 4000.00 |
1005 | 2021-11-22 | 15 | 3 | 103 | 200.00 |
1006 | 2021-11-22 | 17 | 3 | 103 | 50.00 |
Now let’s explore this data using SQL queries with different types of subqueries.
Example 1 - Scalar Subquery
We’ll start with a simple example: We want to list paintings that are priced higher than the average. Basically, we want to get painting names along with the listed prices, but only for the ones that cost more than average. That means that we first need to find this average price; here’s where the scalar subquery comes into play:
SELECT name, listed_price FROM paintings WHERE listed_price > [ SELECT AVG[listed_price] FROM paintings ];
Our
subquery is in the WHERE
clause, where it filters the result set based on the listed price. This subquery returns a single value: the average price per painting for our gallery. Each listed price is compared to this value, and only the paintings that are priced above average make it to the final output:
Pretty woman | 2800.00 |
Handsome man | 2300.00 |
Cool painting | 5000.00 |
If this seems a bit complicated, you may want to check out our interactive SQL Basics course and brush up on your essential SQL skills.
Examples 2 – Multirow Subquery
Now let’s look into subqueries that return one column with multiple rows. These subqueries are often included in the WHERE
clause to filter the results of the main query.
Suppose we want to list all collectors
who purchased paintings from our gallery. We can get the necessary output using a multirow subquery. Specifically, we can use an inner query to list all collectors’ IDs present in the sales
table – these would be IDs corresponding to collectors who made at least one purchase with our gallery. Then, in the outer query, we request the first name and last name of all collectors whose ID is in the output of the inner query. Here’s the code:
SELECT first_name, last_name FROM collectors WHERE id IN [ SELECT collector_id FROM sales ];
And here’s the output:
Laura | Fisher |
Christina | Buffet |
Steve | Stevenson |
Interestingly, we could get the same result without a subquery by using an INNER JOIN
[or just JOIN
]. This join type returns only records that can be found in both tables. So, if we join the collectors
and the sales
tables, we’ll get a list of collectors with corresponding records in the sales
table. Note: I have also used the DISTINCT
keyword here to remove duplicates from the output.
Here’s the query:
SELECT DISTINCT collectors.first_name, collectors.last_name FROM collectors JOIN sales ON collectors.id = sales.collector_id;
You can read more about choosing subquery vs. JOIN elsewhere in our blog.
Example 3 – Multirow Subquery with Multiple Columns
When a subquery returns a table with multiple rows and multiple columns, that subquery is usually found in the FROM
or JOIN
clause. This allows you to get a table with data that was not readily available in the database [e.g. grouped data] and then join
this table with another one from your database, if necessary.
Let’s say that we want to see the total amount of sales for each artist who has sold at least one painting in our gallery. We may start with a subquery that draws on the sales
table and calculates the total amount of sales for each artist ID. Then, in the outer query, we combine this information with the artists’ first names and last names to get the required output:
SELECT artists.first_name, artists.last_name, artist_sales.sales FROM artists JOIN [ SELECT artist_id, SUM[sales_price] AS sales FROM sales GROUP BY artist_id ] AS artist_sales ON artists.id = artist_sales.artist_id;
We assign a
meaningful alias to the output of our subquery [artist_sales
]. This way, we can easily refer to it in the outer query, when selecting the column from this table, and when defining the join condition in the ON
clause. Note: Databases will throw an error if you don't provide an alias for your subquery output.
Here’s the result of the query:
Thomas | Black | 300 |
Kate | Smith | 4800 |
Natali | Wein | 4250 |
So, within one short SQL query, we were able to calculate the total sales for each artist based on the raw data from one table [sales
], and then join this output with the data from another table [artists
].
Subqueries can be quite powerful when we need to combine information from multiple tables. Let’s see what else we can do with subqueries.
Example 4 – Correlated Subquery
The following example will demonstrate how subqueries:
- Can be used in the SELECT clause, and
- Can be correlated [i.e. the main or outer query relies on information obtained from the inner query].
For each collector, we want to calculate the number of paintings purchased through our gallery. To answer this question, we can use a subquery that counts the number of paintings purchased by each collector. Here’s the entire query:
SELECT first_name, last_name, [ SELECT count[*] AS paintings FROM sales WHERE collectors.id = sales.collector_id ] FROM collectors;
Notice how the inner query in this example actually
runs for each row of the collectors
table:
- The subquery is placed in the
SELECT
clause because we want to have an additional column with the number of paintings purchased by the corresponding collector. - For each record of the
collectors
table, the inner subquery calculates the total number of paintings purchased by a collector with the corresponding ID.
Here’s the output:
Brandon | Cooper | 0 |
Laura | Fisher | 2 |
Christina | Buffet | 3 |
Steve | Stevenson | 1 |
As you see, the output of the subquery [i.e. the number of paintings] is different for each record and depends on the output of the outer query [i.e. the corresponding collector]. Thus, we are dealing with a correlated subquery here.
Check out this guide if you want to learn how to write correlated subqueries in SQL. For now, let’s have one more correlated subquery example.
Example 5 – Correlated Subquery
This time, we want to show the first names and the last names of the artists who had zero sales with our gallery. Let’s try to accomplish this task using a correlated subquery in the WHERE
clause:
SELECT first_name, last_name FROM artists WHERE NOT EXISTS [ SELECT * FROM sales WHERE sales.artist_id = artists.id ];
Here is what's going on in this query:
- The outer query lists basic information on the artists, first checking if there are corresponding records in the
sales
- The inner query looks for records that correspond to the artist ID that is currently being checked by the outer query.
- If there are no corresponding records, the first name and the last name of the corresponding artist are added to the output:
Francesco | Benelli |
In our example, we have only one artist without any sales yet. Hopefully, he’ll land one soon.
It’s Time to Practice SQL Subqueries!
In this article, I’ve covered several SQL subquery examples to give you a general understanding of how subqueries can be leveraged in SQL. However, often common table expressions [CTEs] can do better than subqueries.
If you want to practice SQL subqueries and other foundational SQL topics, try our SQL Basics interactive course. It includes 129 coding challenges on querying multiple tables, aggregating and grouping data, joining tables, writing subqueries, and much more.
Want to become a SQL master? Check out our SQL From A to Z learning track. It goes beyond the basics and includes 7 interactive courses covering standard SQL functions, SQL data manipulation language [DML], basic SQL reports, window functions, common table expressions [CTEs], and GROUP BY extensions.
Thanks for reading, and happy learning!