Back to articles list May 7, 2020 - 7 minutes read What Is a Nested Query in SQL? 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 Have you ever wished that you could build a query with several SELECT statements? Good news! You can do this – and a lot more – with SQL’s nested queries. If you’ve been learning SQL (and maybe writing some queries) for a while, you’ve probably encountered cases where it looks like you need another SELECT statement inside your main statement. You may wonder “Is it possible to use nested SELECTs in SQL?”. Yes, it’s possible! In this article, I’ll explain the nested query (aka the nested SELECT) and how to use it efficiently. What Is a Nested SELECT? A nested SELECT is a query within a query, i.e. when you have a SELECT statement within the main SELECT. To make the concept clearer, let’s go through an example together. In this article, we’re going to work with data from a fictional high school. The database includes three tables: students, teachers, and classes. You can see the tables below: Students idnameclass_idGPA 1Jack Black33.45 2Daniel White13.15 3Kathrine Star13.85 4Helen Bright23.10 5Steve May22.40 Teachers idnamesubjectclass_idmonthly_salary 1Elisabeth GreyHistory32,500 2Robert SunLiterature[NULL]2,000 3John ChurchillEnglish12,350 4Sara ParkerMath23,000 Classes idgradeteacher_idnumber_of_students 110321 211425 312128 Let’s say you want to find all students that have above-average GPAs. However, you don’t know the average GPA score. Certainly, you can use a query to find out: SELECT AVG(GPA) FROM students; You’ll get a number (3.19) that you can use to solve the initial task – showing all information for students with a GPA above this average: SELECT * FROM students WHERE GPA > 3.19; But can you solve this task in one step? You can with a nested query. Here is how it looks: SELECT * FROM students WHERE GPA > ( SELECT AVG(GPA) FROM students); Our subquery here returns a single value (i.e. a table with a single column and a single row). This is important for the comparison operator to work. With the average GPA score returned by the inner query, the outer query can select the students who satisfy our filter condition (i.e. a GPA score above average). And here is the result: idnameclass_idGPA 1Jack Black33.45 3Kathrine Star13.85 The proper term for this nested SELECT statement is a subquery. There are many different scenarios where SQL subqueries are very helpful. More Examples of Nested SQL Queries First of all, you can put a nested SELECT within the WHERE clause with comparison operators or the IN, NOT IN, ANY, or ALL operators. The second group of operators are used when your subquery returns a list of values (rather than a single value, as in the previous example): The IN operator checks if a certain value is in the table returned by the subquery. The NOT IN operator filters out the rows corresponding to the values not present in that table returned by a subquery. The ANY operator is used with comparison operators to evaluate if any of the values returned by the subquery satisfy the condition. The ALL operator is also used with comparison operators to evaluate if all values returned by the subquery satisfy the condition. Let’s see how the IN operator works. In this example, you’ll calculate the average number of students in classes where the teacher teaches History or English: SELECT AVG(number_of_students) FROM classes WHERE teacher_id IN ( SELECT id FROM teachers WHERE subject = 'English' OR subject = 'History'); Here you use a subquery to select only those teacher IDs that correspond to teachers of English or History. Note that our subquery returns a list of values, i.e. a table with one column (id) and multiple rows that satisfy the condition of the inner query. Then, in our outer query, you calculate the average number of students only for those classes that satisfy the above condition. For every teacher_id, the IN operator checks if that ID is present in the table returned by the inner query; this ensures that only the classes corresponding to these teachers are considered in the calculation. If you want more examples using the IN, NOT IN, ANY, or ALL operators, check out our SQL Subqueries guide. Multiple subqueries in one statement Ready for more challenging examples? Well, it’s actually possible to have multiple nested SELECTs in one statement. Let’s say you want to show all information about the students in the class with the highest number of students. To answer this question, you’ll need to find the class with the maximal number of students and then define that class. Finally, you’ll need to show information about the students in that class. You can use a subquery within another subquery to answer this question: SELECT * FROM students WHERE class_id = ( SELECT id FROM classes WHERE number_of_students = ( SELECT MAX(number_of_students) FROM classes)); Pretty handy, isn’t it? Using subqueries outside of WHERE Furthermore, subqueries are not limited to use in the WHERE clause. For example, you can also use a nested query in the FROM clause. In the next example, our subquery will return, not a single value, but a table. Let’s find out what subject area corresponds to the highest average teacher salary. You’ll first need to calculate the average salary by subject, then use this table to find the maximum average salary: SELECT subject, MAX(salary_by_subject.avg_salary) AS max_salary FROM ( SELECT subject, AVG(monthly_salary) AS avg_salary FROM teachers GROUP BY subject) salary_by_subject; Note that the inner query will return a table with multiple rows and columns. To be more specific, the table will have two columns, subject and avg_salary, to show the average salary of teachers based on their main subject. The number of rows will correspond to the number of unique subjects taught in the school (i.e. as specified in the teachers table). You also need to specify an alias for this table – salary_by_subject. Then, the outer query simply calculates the maximum average salary based on the salary_by_subject table and returns this value, together with the name of the corresponding subject: subjectmax_salary Math3,000 As you can see, our school’s math teachers have the highest average monthly salary ($3000). For some tasks, you may need the inner query to use information from the outer query. When two queries are related like this, we call them correlated subqueries. This is a more advanced topic that is very well explained in our beginner’s guide to correlated subqueries and this practical tutorial on writing correlated subqueries. Additional Tips on Using Nested SELECTs SQL subqueries are a powerful tool. They allow us to perform tasks more efficiently by having only one query instead of several. When using nested queries, keep these considerations in mind: Subqueries can return single values or tables (with one or many rows and columns). You can include a subquery: In the WHERE clause, to filter data. In the FROM clause, to specify a new table. In the SELECT clause, to specify a certain column. In the HAVING clause, as a group selector. Subqueries should always be enclosed in parentheses (). Different database management systems have certain limitations on the number of subquery levels (e.g. up to 32 levels in SQL Server). However, in practice, you’ll rarely have more than 2-3 levels of nested queries. Subqueries are often computationally inefficient. Thus, I recommend avoiding nested queries when other options are available (e.g. JOINs). Time to Practice SQL Subqueries! Now that you’ve learned so much about nested queries in SQL, you’re probably eager to start practicing them! Luckily, we have LOTS of interactive exercises for you to practice different SQL subqueries. First of all, our SQL Basics course has a big section on subqueries. Make sure to check it out! If you want to become a really confident user of nested queries in SQL, I also recommend completing the “Subqueries” section in the SQL Practice Set. You can also check our YouTube channel - We Learn SQL. There you will find episodes about SQL Subqueries and their variations. Remember to subscribe and, if you like it, give it a thumbs up. Subqueries will definitely make you a much more powerful SQL user. Happy learning! Tags: sql learn sql subquery You may also like 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 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 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 Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. 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 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 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 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.