What Are the Different Types of SQL Subqueries?

Subqueries can be used in many business cases. What subquery types does SQL offer? And how can you use them efficiently? In this article, I’ll guide you through different subquery types and the typical situations when they are useful.

What Is an SQL Subquery?

A subquery, or nested query, is a query placed within another SQL query. There are many different scenarios where you may want to include a query in the WHERE, FROM, or SELECT clauses of your main query.

It’s always easier to grasp new concepts when they are presented with examples. So, let’s just start! Imagine that we run several art galleries and have the following tables in our database:

galleries

paintings

sales_agents

managers

One of the most straightforward subquery use cases is to include it in the WHERE clause to filter results. For example, if you wanted to see information about only those sales agents who received a higher-than-average agency fee last month, you could use the following SQL query:

SELECT *
FROM sales_agents
WHERE agency_fee > 
(SELECT AVG(agency_fee)
 FROM sales_agents);

Here, your subquery calculates the average agency fee your sales team received last month and returns a single value ($2728). Then you use this value to filter the results from your main query and return information for only those sales agents whose agency fee was higher than average:

SQL subqueries may return single values or entire tables. There can be nested subqueries or correlated subqueries. Each of these subquery types works well for certain use cases. If you’d like more detailed info on this, read our beginner’s guide to SQL subqueries. In this article, I’ll provide examples of different subquery types in SQL and guide you through the typical scenarios when this kind of subquery is particularly handy.

Scalar Subqueries

When a subquery returns a single value, or exactly one row and exactly one column, we call it a scalar subquery. This type of subquery is frequently used in the WHERE clause to filter the results of the main query. The subquery in our previous example is a scalar subquery, as it returns a single value (i.e. the average agency fee).

Scalar subqueries can also be used in the main query’s SELECT statement. For example, let’s say we want to see the average price of all our paintings next to the price of each painting.

SELECT name AS painting,
	 price,
	 (SELECT AVG(price)
  FROM paintings) AS avg_price
FROM paintings;

The subquery here returns a scalar value ($5840) that is simply added to each row of the resulting table:

Note that the subquery (also called the inner query) in this example is totally independent of the main query (also called the outer query) – you can run the inner query on its own and get a meaningful result.

Multiple-Row Subqueries

If your subquery returns more than one row, it can be referred to as a multiple-row subquery. Note that this subquery type includes (1) subqueries that return one column with multiple rows (i.e. a list of values) and (2) subqueries that return multiple columns with multiple rows (i.e. tables).

Subqueries that return one column and multiple rows are often included in the WHERE clause to filter the results of the main query. In this case, they are usually used with operators like IN, NOT IN, ANY, ALL, EXISTS, or NOT EXISTS that allow users to compare a particular value with the values in the list returned by the subquery.

Want to learn more about SQL Subqueries with the IN operator? Watch an episode of our We Learn SQL series on Youtube. Remember to subscribe to our channel.

For example, let’s say you want to calculate the average agency fee for those agents who are not managers. You can use the following subquery to answer this question:

SELECT AVG(agency_fee)
FROM sales_agents
WHERE id NOT IN (SELECT id
                 FROM managers);

The inner query will return a list of all manager IDs. Then the outer query filters only those sales agents who are not in the managers list and calculates an average agency fee paid to these agents. The query returns a single value – the average agency fee paid to non-managers ($1885).

Check out our SQL subqueries guide for more examples of multi-row subqueries.

Correlated Subqueries

There are also SQL subqueries where the inner query relies on information obtained from the outer query. These are correlated subqueries. Because of the interdependence between the main query and the inner query, this type of subquery can be more challenging to understand. Read this beginner-friendly guide to become more proficient with correlated subqueries in SQL.

Once again, let’s go straight to the examples! Correlated subqueries are commonly used in the SELECT, WHERE, and FROM statements.

If we want to calculate the  number of paintings found in each of our galleries, we can use the following query. Note the correlated subquery in the SELECT statement:

SELECT city, 
 (SELECT count(*)
  FROM paintings p
  WHERE g.id = p.gallery_id) total_paintings
FROM galleries g;

Here, the subquery returns a scalar value with the total number of paintings in the corresponding gallery. The main query displays this information together with the city where that art gallery is located.

You can also see that, in contrast to our previous examples, here the inner query depends on the outer query. We pull the gallery ID from the galleries table, which is in the outer query. In other words, you cannot run the inner query as an independent query – it will just throw an error.

Note also that, in this case, you could use JOIN instead of a subquery and get the same result:

SELECT g.city, count(p.name) AS total_paintings
FROM galleries g
JOIN paintings p
ON g.id = p.gallery_id
GROUP BY g.city;

JOINs usually perform faster than subqueries. However, if you find subqueries more intuitive for your particular case, it’s fine to use them. You can read more about using subqueries vs. JOINs in our comprehensive guide.

Finally, correlated subqueries can also be used in the WHERE statement. For instance, let’s say we want to get information about those sales agents whose agency fee was equal or higher than the average fee for their gallery. We can run the following query to get the required result:

SELECT last_name, 
       first_name, 
       agency_fee
FROM sales_agents sa1
WHERE sa1.agency_fee >= (SELECT avg(agency_fee)
                         FROM sales_agents sa2 
                         WHERE sa2.gallery_id = sa1.gallery_id);

The inner query, in this case, returns the average agency fee for the gallery of the respective sales agent. The outer query returns the information about only those sale agents who satisfy the condition included in the WHERE statement (i.e. an agency fee equal to or greater than their gallery average).

Again, the subquery in this example is a correlated subquery, as it can’t be run independently of the outer query. If you want to learn more, check out this easy-to-follow tutorial on writing correlated subqueries.

Even though different SQL subquery types cover lots of typical situations, there are some cases when you may want to use Common Table Expressions (CTEs) instead of subqueries. If you’re interested in learning more about CTEs, check out this article that will guide you through the differences between subqueries and CTEs.

Time to Practice Different SQL Subquery Types!

You’ve learned that there are many types of subqueries in SQL. Depending on the task at hand, you can apply scalar, multi-row, or correlated subqueries to get the result you need.

We’ve already walked through several examples of subqueries and found out where you can use them. However, to become a really powerful SQL user, you need more practice with different subquery types. It’s time for interactive exercises!

Our SQL Basics course has a comprehensive section on subqueries, where detailed explanations and examples are combined with dozens of exercises. Check it out! To get additional practice, I also recommend completing the Subqueries section in the SQL Practice Set.

More practice = more professional SQL queries! Happy learning!