5 SQL Subquery Examples

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.

5 Subquery Examples in SQL

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.

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:

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:

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:

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:

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:

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!