What Is a Nested Query in SQL?

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.

If you want to practice SQL, check out our SQL Practice track. It offers multiple hands-on interactive SQL courses with exercises to cover nested SELECT statements and other challenging SQL features.

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

Teachers

Classes

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:

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:

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 our SQL Practice track. It contains interactive SQL courses with hands-on exercises to practice subqueries and other challenging constructions in SQL.

Subqueries will definitely make you a much more powerful SQL user. Happy learning!