Nested Queries in SQL
FREE
Overview
Nested query is one of the most useful functionalities of SQL. Nested queries are useful when we want to write complex queries where one query uses the result from another query. Nested queries will have multiple SELECT statements nested together. A subquery is a SELECT statement nested within another SELECT statement.
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
| id | name | salary | role |
|---|---|---|---|
| 1 | Augustine Hammond | 10000 | Developer |
| 2 | Perice Mundford | 10000 | Manager |
| 3 | Cassy Delafoy | 30000 | Developer |
| 4 | Garwood Saffen | 40000 | Manager |
| 5 | Faydra Beaves | 50000 | Developer |
Awards
| id | employee_id | award_date |
|---|---|---|
| 1 | 1 | 2022-04-01 |
| 2 | 3 | 2022-05-01 |
Independent Nested Queries
Example 1: IN
Select all employees who won an award.
Output
| id | name |
|---|---|
| 1 | Augustine Hammond |
| 3 | Cassy Delafoy |
Example 2: NOT IN
Select all employees who never won an award.
Output
| id | name |
|---|---|
| 2 | Perice Mundford |
| 4 | Garwood Saffen |
| 5 | Faydra Beaves |
Example 3: ALL
Select all Developers who earn more than all the Managers
Output
| id | name | salary | role |
|---|---|---|---|
| 5 | Faydra Beaves | 50000 | Developer |
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
| id | name | salary | role |
|---|---|---|---|
| 5 | Faydra Beaves | 50000 | Developer |
| 3 | Cassy Delafoy | 30000 | Developer |
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
| id | name | salary | role |
|---|---|---|---|
| 4 | Garwood Saffen | 40000 | Manager |
| 5 | Faydra Beaves | 50000 | Developer |
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:
| role | avg(salary) |
|---|---|
| Developer | 30000 |
| Manager | 25000 |
Additional Resources
Conclusion
- A nested query in SQL contains a query inside another query, and the outer query will use the result of the inner query.
- We can classify nested queries into independent and co-related nested queries.
- In independent nested queries, the order of execution is from the innermost query to the outermost query
- In co-related nested queries, the inner query uses the values from the outer query so that the inner query is executed for every row processed by the outer query
- Co-related nested query runs slow when compared with independent nested query.