Nested Queries in SQL

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:
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE]
)
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:
CREATE TABLE Employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
salary NUMBER NOT NULL,
role VARCHAR2(100) NOT NULL
);
CREATE TABLE Awards(
 id NUMBER PRIMARY KEY,
employee_id NUMBER NOT NULL,
award_date DATE NOT NULL
);
Let's add data to the tables created above:
INSERT INTO Employees VALUES (1, 'Augustine Hammond', 10000, 'Developer');
INSERT INTO Employees VALUES (2, 'Perice Mundford', 10000, 'Manager');
INSERT INTO Employees VALUES (3, 'Cassy Delafoy', 30000, 'Developer');
INSERT INTO Employees VALUES (4, 'Garwood Saffen', 40000, 'Manager');
INSERT INTO Employees VALUES (5, 'Faydra Beaves', 50000, 'Developer');
INSERT INTO Awards VALUES(1, 1, TO_DATE('2022-04-01', 'YYYY-MM-DD'));
INSERT INTO Awards VALUES(2, 3, TO_DATE('2022-05-01', 'YYYY-MM-DD'));
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.
SELECT id, name FROM Employees
WHERE id IN (SELECT employee_id FROM Awards);
Output
id name
1 Augustine Hammond
3 Cassy Delafoy
Example 2: NOT IN
Select all employees who never won an award.
SELECT id, name FROM Employees
WHERE id NOT IN (SELECT employee_id FROM Awards);
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
SELECT * FROM Employees
WHERE role = 'Developer'
AND salary > ALL (
SELECT salary FROM Employees WHERE role = 'Manager'
);
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
SELECT * FROM Employees
WHERE role = 'Developer'
AND salary > ANY (
SELECT salary FROM Employees WHERE role = '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.
SELECT * FROM Employees emp1
WHERE salary > (
 SELECT AVG(salary)
FROM Employees emp2
WHERE emp1.role = emp2.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:
SELECT role, AVG(salary)
FROM Employees
GROUP BY role;
role avg(salary)
Developer 30000
Manager 25000
Additional Resources
1. Top SQL Query Interview Questions and Answers
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.