Back to articles list May 29, 2020 - 7 minutes read What Are the Different Types of SQL Subqueries? Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql learn sql subquery 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 idcity 1London 2New York 3Munich paintings idnamegallery_idprice 1Patterns35000 2Ringer14500 3Gift13200 4Violin Lessons26700 5Curiosity29800 sales_agents idlast_namefirst_namegallery_idagency_fee 1BrownDenis22250 2WhiteKate33120 3BlackSarah21640 4SmithHelen14500 5StewartTom32130 managers idgallery_id 12 23 41 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: idlast_namefirst_namegallery_idagency_fee 2WhiteKate33120 4SmithHelen14500 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: paintingpriceavg_price Patterns50005840 Ringer45005840 Gift32005840 Violin Lessons67005840 Curiosity98005840 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; 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). 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. citytotal_paintings London2 New York2 Munich1 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). last_namefirst_nameagency_fee BrownDenis2250 WhiteKate3120 SmithHelen4500 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! Tags: sql learn sql subquery You may also like Beginner’s Guide to the SQL Subquery Did you know that you can use a SQL query inside another SQL query? Find out the ABCs of subqueries and improve your SQL skills! Read more SQL Subqueries The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in FROM and WHERE clauses, and explore the difference between correlated and nested subqueries. Read more Learn to Write a SQL Correlated Subquery in 5 Minutes A correlated subquery is a subquery that is executed many times—once for each record (row) returned by the outer (main) query. Sounds complex? Not! Read more Correlated Subquery in SQL: A Beginner’s Guide Correlated subqueries are the only way to solve some SQL statements. But they can be very slow. In this post, we’ll talk about why, how, and when to use them. Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Converting Subqueries to Joins No more badly performed queries! Find out how you can convert SQL subqueries to joins and improve your query efficiency. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.