Subqueries & Correlated Queries

A subquery is a query nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

Types of Subqueries

1. Scalar Subquery: Returns a single value (one row, one column). Used directly in expressions.
2. Multi-row Subquery: Returns one or more rows (used with IN, ANY, ALL).
3. Correlated Subquery: A subquery that uses values from the outer query. It is executed once for every row processed by the outer query.

Scalar Subquery

SELECT name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

The subquery runs once and returns a single value appended to each row.

Multi-Row Subquery with IN

SELECT name FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'New York'
);

Correlated Subquery

A correlated subquery references a column from the outer query. It cannot run independently:

SELECT name, salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

For each row in e1, the subquery computes the average salary of that employee’s department. This runs once per outer row, which can be slow on large tables.

EXISTS vs IN

EXISTS checks for the existence of any matching row and short-circuits as soon as a match is found. IN collects the full subquery result and compares.

-- EXISTS (faster for large result sets, short-circuits)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- IN (slower for large sets)
SELECT * FROM customers
WHERE id IN (
    SELECT customer_id FROM orders
);

Rule of thumb: Use EXISTS when the subquery can return many rows. Use IN when the subquery returns a small, static set.

ANY and ALL

-- Salary > ANY department 5 employee (greater than at least one)
SELECT name FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE department_id = 5
);

-- Salary > ALL department 5 employees (greater than every single one)
SELECT name FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 5
);

Subqueries in FROM (Derived Tables)

SELECT dept_id, avg_salary
FROM (
    SELECT department_id AS dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 70000;

Derived tables must have an alias and every column must have a name.

SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Original: SELECT * FROM Clients WHERE id IN (SELECT client_id FROM Orders)
SELECT *
FROM Clients c
WHERE EXISTS (
    SELECT 1 FROM Orders o WHERE o.client_id = c.id
);

SELECT product_name
FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM sales);

SELECT name
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE department_id = 5
);

SELECT dept_id, avg_salary
FROM (
    SELECT department_id AS dept_id,
           AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS stats
WHERE avg_salary > 50000;