Nested Subqueries in SQL
What is a Nested Subquery?
A nested subquery (also called a subquery or inner query) is a query placed inside another
SQL query. The outer query, also called the main query, uses the results of the subquery to
perform further operations. Subqueries can be used in SELECT, FROM, and WHERE clauses to
retrieve data dynamically.
Types of Nested Subqueries
1. Single-row Subqueries – Returns a single value.
2. Multi-row Subqueries – Returns multiple values.
3. Multi-column Subqueries – Returns multiple columns.
4. Correlated Subqueries – Uses values from the outer query in the subquery.
1. Single-row Subqueries
A single-row subquery returns only one value. It is commonly used with comparison
operators (=, >, <, >=, <=).
Example: Find employees earning more than the average salary.
SELECT * FROM EMPLOYEE
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE);
Explanation:
The subquery (SELECT AVG(SALARY) FROM EMPLOYEE) calculates the average salary.
The outer query selects employees whose salaries are greater than the calculated
average.
2. Multi-row Subqueries
A multi-row subquery returns multiple values and is used with operators like IN, ANY, and
ALL.
Example: Find employees who work in departments that have at least one manager.
SELECT * FROM EMPLOYEE
WHERE DEPT_ID IN (SELECT DEPT_ID FROM EMPLOYEE WHERE ROLE = 'Manager');
Explanation:
The subquery retrieves DEPT_IDs where employees are managers.
The outer query selects employees from those departments.
3. Multi-column Subqueries
A multi-column subquery returns multiple columns and is used for comparisons.
Example: Find employees with the same job title and salary as a specific employee (John
Doe).
SELECT * FROM EMPLOYEE
WHERE (JOB_TITLE, SALARY) =
(SELECT JOB_TITLE, SALARY FROM EMPLOYEE WHERE NAME = 'John Doe');
Explanation:
The subquery retrieves JOB_TITLE and SALARY of 'John Doe'.
The outer query finds employees with the same job title and salary.
4. Correlated Subqueries
A correlated subquery depends on the outer query and executes once for each row
processed by the outer query.
Example: Find employees whose salaries are higher than the average salary of their
department.
SELECT * FROM EMPLOYEE e1
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE e2 WHERE e1.DEPT_ID =
e2.DEPT_ID);
Explanation:
The subquery calculates the average salary per department.
The outer query selects employees whose salaries exceed their department’s
average.
Example
CREATE TABLE EMPLOYEE (
EMP_ID INT,
NAME VARCHAR(100),
JOB_TITLE VARCHAR(100),
SALARY DECIMAL(10,2),
DEPT_ID INT,
ROLE VARCHAR(50)
);
INSERT INTO EMPLOYEE VALUES
(1, ‘gayathri’, 'Software Engineer', 60000, 101, 'Employee'),
(2, ‘durga', 'Manager', 90000, 101, 'Manager'),
(3, ‘Meenakshi’, 'Software Engineer', 65000, 101, 'Employee'),
(4, 'harini', 'HR Executive', 50000, 102, 'Employee'),
(5, ‘safrina', 'Manager', 85000, 102, 'Manager'),
(6, ‘rakshana', 'HR Executive', 52000, 102, 'Employee'),
(7, 'abinaya', 'Accountant', 48000, 103, 'Employee'),
(8, 'divya’, 'Software engineer', 60000, 103, 'Employee');
1. Single row query
SELECT *
FROM EMPLOYEE
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEE
);
Step 1: Calculate Average Salary
Total Salary = 510000
Number of Employees = 8
Average = 63750
✅ Output
EMP_ID NAME SALARY
2 durga 90000
3 Meenakshi 65000
5 safrina 85000
2️⃣ Multi-Row Subquery
Employees working in departments that have at least one Manager
SELECT *
FROM EMPLOYEE
WHERE DEPT_ID IN (
SELECT DEPT_ID
FROM EMPLOYEE
WHERE ROLE = 'Manager'
);
Subquery Returns:
101
102
(1, ‘gayathri’, 'Software Engineer', 60000, 101, 'Employee'),
(2, ‘durga', 'Manager', 90000, 101, 'Manager'),
(3, ‘Meenakshi’, 'Software Engineer', 65000, 101, 'Employee'),
(4, 'harini', 'HR Executive', 50000, 102, 'Employee'),
(5, ‘safrina', 'Manager', 85000, 102, 'Manager'),
(6, ‘rakshana', 'HR Executive', 52000, 102, 'Employee'),
3️⃣ Multi-Column Subquery
Employees with same job title and salary as ‘gayathri'
SELECT *
FROM EMPLOYEE
WHERE (JOB_TITLE, SALARY) = (
SELECT JOB_TITLE, SALARY
FROM EMPLOYEE
WHERE NAME = gayathri’
);
✅ Output
1, ‘gayathri’, 'Software Engineer', 60000, 101, 'Employee'
8, 'divya’, 'Software engineer', 60000, 103, 'Employee'
4️⃣ Correlated Subquery
Employees earning more than their department average
SELECT *
FROM EMPLOYEE e1
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEE e2
WHERE e1.DEPT_ID = e2.DEPT_ID
);
Department 101
Employees: 60000, 90000, 65000
Average = 71667
Above average:
durga (90000)
Department 102
Employees: 50000, 85000, 52000
Average = 62333
Above average:
safrina (85000)
Department 103
Employees: 48000, 60000
Average = 54000
Above average:
divya (60000)
EMP_ID NAME DEPT_ID SALARY
2 durga 101 90000
5 safrina 102 85000
8 divya 103 60000