Nested Queries in SQL

A nested query (subquery) is a query placed inside another query. The inner query executes first and its result is used by the outer query for more complex data retrieval.

Syntax

SELECT column1, column2
FROM table1
WHERE column1 IN (
    SELECT column1
    FROM table2
    WHERE condition
);

Types of Nested Queries

Sample Tables

-- employees: emp_id, emp_name, dept_id
-- (1,John,1), (2,Mary,2), (3,Bob,1), (4,Alice,3), (5,Tom,1)

-- departments: dept_id, dept_name
-- (1,Sales), (2,Marketing), (3,Finance)

-- sales: sale_id, emp_id, sale_amt
-- (1,1,1000),(2,2,2000),(3,3,3000),(4,1,4000),(5,5,5000),(6,3,6000),(7,2,7000)

Example 1: Non-Correlated (IN)

Find employees in the Sales department ?

SELECT emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE dept_name = 'Sales'
);

+----------+
| emp_name |
+----------+
| John     |
| Bob      |
| Tom      |
+----------+

Example 2: Correlated (EXISTS)

Find employees who have made at least one sale ?

SELECT emp_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM sales s WHERE e.emp_id = s.emp_id
);

+----------+
| emp_name |
+----------+
| John     |
| Mary     |
| Bob      |
| Tom      |
+----------+

Alice is excluded because she has no record in the sales table.

Example 3: ALL Operator

Find employees whose ID matches all sale records above $1000 ?

SELECT emp_name
FROM employees
WHERE emp_id = ALL (
    SELECT emp_id FROM sales WHERE sale_amt > 5000
);

Operators Summary

Conclusion

Nested queries allow complex data retrieval by embedding one query inside another. Non-correlated subqueries run independently and pass results to the outer query, while correlated subqueries reference outer query values and execute per row. Use IN/ALL/ANY for independent subqueries and EXISTS for correlated ones.