Nested Queries in SQL

What is a Nested Query in SQL?

A nested query in SQL contains a query inside another query. The outer query will use the result of the inner query. For instance, a nested query can have two SELECT statements, one on the inner query and the other on the outer query.

What are the Types of Nested Queries in SQL?

Nested queries in SQL can be classified into two different types:

• Independent Nested Queries
• Co-related Nested Queries

Independent Nested Queries

In independent nested queries, the execution order is from the innermost query to the outer query. An outer query won't be executed until its inner query completes its execution. The outer query uses the result of the inner query. Operators such as IN, NOT IN, ALL, and ANY are used to write independent nested queries.

• The IN operator checks if a column value in the outer query's result is present in the inner query's result. The final result will have rows that satisfy the IN condition.
• The NOT IN operator checks if a column value in the outer query's result is not present in the inner query's result. The final result will have rows that satisfy the NOT IN condition.
• The ALL operator compares a value of the outer query's result with all the values of the inner query's result and returns the row if it matches all the values.
• The ANY operator compares a value of the outer query's result with all the inner query's result values and returns the row if there is a match with any value.

Co-related Nested Queries

In co-related nested queries, the inner query uses the values from the outer query to execute the inner query for every row processed by the outer query. The co-related nested queries run slowly because the inner query is executed for every row of the outer query's result.

How to Write Nested Query in SQL?

We can write a nested query in SQL by nesting a SELECT statement within another SELECT statement. The outer SELECT statement uses the result of the inner SELECT statement for processing.

The general syntax of nested queries will be:

The SELECT query inside the brackets () is the inner query, and the SELECT query outside the brackets is the outer query. The outer query uses the result of the inner query.

Examples of Nested Query in SQL

We will use the Employees and Awards table below to understand independent and co-related nested queries. We will be using Oracle SQL syntax in our queries.

Let's create the Employees and Awards tables:

Let's add data to the tables created above:

Employees

Awards

Independent Nested Queries

Example 1: IN

Select all employees who won an award.

Output

Example 2: NOT IN

Select all employees who never won an award.

Output

Example 3: ALL

Select all Developers who earn more than all the Managers

Output

Explanation

The developer with id 5 earns (50000) more than all the managers: 2 (10000) and 4 (40000)

Example 4: ANY

Select all Developers who earn more than any Manager

Output

Explanation

The developers with id 3 and 5 earn more than any manager:

• The developer with id 3 earns (30000) more than the manager with id 2 (10000)
• The developer with id 5 earns (50000) more than the managers with id 2 (10000) and 4 (40000)

Co-related Nested Queries

Select all employees whose salary is above the average salary of employees in their role.

Output

Explanation

The manager with id 4 earns more than the average salary of all managers (25000), and the developer with id 5 earns more than the average salary of all developers (30000). The inner query is executed for all rows fetched by the outer query. The inner query uses the role value (emp1.role) of every outer query's row (emp1.role = emp2.role).

We can find the average salary of managers and developers using the below query:

Additional Resources

1. Top SQL Query Interview Questions and Answers