Module 2 — Subqueries

Why subqueries exist

Sometimes a question can't be answered by looking at raw table data directly — you first need to compute something (a max, an average, a list of ids, a yes/no check), and only then compare each row against that computed thing. A subquery is a SELECT statement nested inside another SQL statement, used to produce that intermediate value, list, or table on the fly.

What it is

A subquery is just a SELECT wrapped in parentheses, placed somewhere another SQL statement expects a value, a list, or a table. It can appear in WHERE, SELECT, FROM, or even HAVING.

When to use it

• You need to compare a row against an aggregate (max, avg, count) computed from the same or a different table.
• You need to check existence of a related row without pulling back any of its columns (data validation, filtering).
• You need to build a small computed lookup table on the fly, used only within this one query.

When NOT to use it

• When a plain JOIN says the same thing more simply and you actually need columns from the other table — joins are usually easier for a reader to follow than deeply nested subqueries.
• When a correlated subquery would force a slow row-by-row loop (see Execution Flow below) and an equivalent JOIN or window function would run in a single pass.

Schema & sample data for this module

We reuse employees/departments from Module 1 (same data), and add a customers/orders pair for the data-quality/production examples.

DDL (recap from Module 1)

-- employees / departments: identical to Module 1 (16 employees, 6 departments, Legal has 0 employees,
-- Tanvi Desai & Sameer Gupta have NULL dept_id, Priya/Rohit(103) tie at 145000, Ananya/Vikram tie at 92000)

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(50),
    country       VARCHAR(50)
);

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT,             -- can be a "dangling" id that doesn't exist in customers (data quality issue)
    order_date   DATE,
    total_amount DECIMAL(10,2)
);

INSERT INTO customers VALUES
(201, 'Rahul Bansal',   'India'),
(202, 'Grace Lin',      'Singapore'),
(203, 'Omar Haddad',    'UAE'),
(204, 'Laura Fischer',  'Germany');

INSERT INTO orders VALUES
(5001, 201, '2024-01-10', 2500.00),
(5002, 202, '2024-01-11', 1200.00),
(5003, 201, '2024-01-15',  800.00),
(5004, 203, '2024-02-01', 4300.00),
(5005, 999, '2024-02-05',  650.00);   -- customer_id 999 DOES NOT EXIST in customers -- orphaned order (data bug)

Scalar subquery

What it is: a subquery that returns exactly one row and one column — a single value. It can be used anywhere a plain value could go: in WHERE, in SELECT, even inside arithmetic.

Why it exists: you often need to compare a row against a single computed number — the overall max, the overall average, a specific lookup value — without hardcoding that number.

Easy example

Find employees who earn more than the company-wide average salary.

SELECT emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Line by line: the inner query SELECT AVG(salary) FROM employees runs first, completely on its own, and produces one number (roughly 100,687.5). The outer query then just does a plain comparison, as if you'd typed WHERE salary > 100687.5 directly.

Scalar subquery in SELECT

SELECT
    emp_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

IN subquery

What it is: a subquery that returns a list of values (one column, many rows), used with IN to check membership.

Easy example

Find all employees who work in a department located in Bangalore.

SELECT emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE location = 'Bangalore'
);

The inner query returns (1, 4) — Engineering and HR. The outer query keeps any employee whose dept_id is in that list.

Medium example — customers who have placed at least one order

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);
-- Rahul, Grace, Omar -- Laura is excluded, she has never ordered

Correlated subquery

What it is: a subquery that references a column from the outer query. Because the inner query's result depends on which outer row is currently being processed, it conceptually has to be re-evaluated once per outer row.

Easy example

Find employees who earn more than the average salary of their own department (not the company-wide average).

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e.dept_id   -- <- this line correlates the subquery to the outer row
);

Line by line: for every outer row e, the inner query recomputes the average salary using only rows from e's own department (e2.dept_id = e.dept_id). Sneha Kapoor (Engineering, 98000) is compared against Engineering's average (~143,250), not the whole company's.

NOT IN & the NULL trap

This is one of the most important gotchas in all of SQL, and it comes up constantly in interviews.

The problem, demonstrated

Goal: find customers who have never placed an order.

-- Looks reasonable...
SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);
-- With OUR data (no NULLs in orders.customer_id), this correctly returns Laura Fischer.

Now watch what happens if just one row in the subquery has a NULL customer_id — which is extremely plausible in real data (an order placed as a guest checkout, with no customer linked yet):

INSERT INTO orders VALUES (5006, NULL, '2024-02-10', 300.00);  -- a guest checkout order

SELECT customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);
-- Now returns ZERO ROWS. Not an error -- just silently, completely empty.

Why this happens

NOT IN (list) is logically equivalent to column != v1 AND column != v2 AND column != v3 ... for every value in the list. If any one value in that list is NULL, that one comparison becomes column != NULL, which evaluates to UNKNOWN — not TRUE, not FALSE. And TRUE AND TRUE AND UNKNOWN evaluates to UNKNOWN overall, which is treated as "not true", so the row gets filtered out. This happens for every row being tested, since every row's chain of ANDs includes that same poisoned NULL comparison — so the whole result set silently collapses to empty.

The safe fix

SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Correctly returns Laura Fischer, regardless of any NULLs in orders.customer_id

NOT EXISTS never compares values directly against NULL the way NOT IN does — it just asks "does at least one matching row exist," and a NULL row in the subquery simply never matches anything, which is exactly the harmless behavior you want.

ANY & ALL

What they are: operators that compare a value against every value returned by a subquery, using a comparison operator (=, >, <, etc.) combined with ANY or ALL.

• > ANY (subquery) — true if the value is greater than at least one value returned (equivalent to > MIN(...)).
• > ALL (subquery) — true if the value is greater than every value returned (equivalent to > MAX(...)).

Example — employees earning more than ALL Marketing employees

SELECT emp_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary FROM employees WHERE dept_id = 3   -- Marketing
)
AND dept_id != 3;   -- exclude Marketing itself from the comparison

This is functionally identical to comparing against MAX(salary) FROM employees WHERE dept_id = 3, but written with ALL. Most engineers actually prefer the MAX()/MIN() scalar subquery form shown in the Level 1 section — it's more widely understood at a glance than ANY/ALL, which some engineers rarely encounter.

EXISTS & NOT EXISTS

What it is: a boolean test — TRUE if the subquery returns at least one row, FALSE if it returns none. EXISTS never cares what columns the subquery selects (writing SELECT 1 or SELECT * inside makes no difference) — only whether a row exists.

This is the "SEMI JOIN" pattern from Module 1, formalized.

Easy example

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Rahul, Grace, Omar (Laura excluded -- she has no matching order row)

Execution flow internals: how correlated subqueries really run

Logically, a correlated subquery behaves like a nested loop: for every row the outer query considers, the database substitutes that row's values into the inner query and re-evaluates it from scratch.

SELECT e.emp_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id
);
-- "find employees who manage at least one other employee"

Conceptual execution, row by row:

1. Take outer row: Arjun Mehta (emp_id=101). Run inner query with manager_id = 101 → finds Priya & Rohit → EXISTS is TRUE → keep Arjun.
2. Take outer row: Priya Nair (emp_id=102). Run inner query with manager_id = 102 → finds Sneha → EXISTS is TRUE → keep Priya.
3. Take outer row: Sneha Kapoor (emp_id=104). Run inner query with manager_id = 104 → finds nobody → EXISTS is FALSE → drop Sneha.
4. ...continue for all 16 rows.

Rewriting subqueries as joins

Many subqueries have an equivalent JOIN form. Knowing both, and knowing when to prefer which, is a strong interview signal.

IN subquery → INNER JOIN + DISTINCT

-- Subquery form
SELECT customer_name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- Equivalent JOIN form
SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Both give the same result here. The JOIN form needs DISTINCT because a customer with multiple orders would otherwise be duplicated (Module 1's cardinality issue) — the subquery form never had that problem in the first place, since IN only checks membership.

EXISTS → semi-join (same idea)

-- EXISTS form (preferred for pure existence checks)
SELECT c.customer_name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- JOIN + DISTINCT form (works, but needs the DISTINCT safety net)
SELECT DISTINCT c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Subqueries in SELECT, FROM, and WHERE

Subquery in FROM (a "derived table" / inline view)

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) dept_avgs
WHERE avg_salary > 100000;

The inner query is treated as if it were a real table — you must give it an alias (dept_avgs here), and every database requires this alias even though you never reference it by name in a simple case like this.

Subquery in SELECT (scalar, per-row)

SELECT
    d.dept_name,
    (SELECT COUNT(*) FROM employees e WHERE e.dept_id = d.dept_id) AS headcount
FROM departments d;

This is a correlated scalar subquery run once per department row — functionally similar to a LEFT JOIN + GROUP BY, but sometimes easier to read for a single extra computed column. At scale, prefer the JOIN + GROUP BY form (Module 1) since it's usually a single pass instead of one subquery execution per outer row.

How interviewers trick people on subqueries

1. The NOT IN + NULL trap — covered in depth above. This is probably the single most common SQL interview gotcha across all companies.
2. The forgotten correlation trap — they ask for "above department average" and quietly check whether you correlate the inner query by dept_id, or accidentally compute the company-wide average instead.
3. The "subquery returns multiple rows" trap — they ask you to use a scalar subquery (= (SELECT ...)) against data where the subquery could return more than one row, to see if you catch the runtime error before running it.
4. The EXISTS vs IN vs JOIN performance question — "which is fastest?" The honest answer is "it depends on the optimizer, data size, and indexes — but EXISTS is generally safest for a pure existence check, since it can short-circuit and never risks duplicate rows." Interviewers want to see you avoid a dogmatic one-line answer.
5. The double-negative trap — "find employees NOT in a department that has NO employees earning over 100000" nests two negations; many candidates fumble translating this into nested NOT EXISTS clauses correctly.

3 Hard Interview Questions

SELECT dept_name, avg_salary
FROM (
    SELECT d.dept_name, AVG(e.salary) AS avg_salary
    FROM departments d
    JOIN employees e ON d.dept_id = e.dept_id
    GROUP BY d.dept_name
) dept_avgs
WHERE avg_salary = (
    SELECT MAX(avg_salary)
    FROM (
        SELECT AVG(salary) AS avg_salary
        FROM employees
        GROUP BY dept_id
    ) all_avgs
);
-- Result: Engineering (~143,250)

SELECT customer_id, total_spent
FROM (
    SELECT customer_id, SUM(total_amount) AS total_spent
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) customer_totals
WHERE total_spent > (
    SELECT AVG(total_spent)
    FROM (
        SELECT customer_id, SUM(total_amount) AS total_spent
        FROM orders
        WHERE customer_id IS NOT NULL
        GROUP BY customer_id
    ) t
);

SELECT m.emp_name
FROM employees m
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id
)
AND NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id AND e.salary >= 90000
);

SELECT m.emp_name
FROM employees m
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id)
AND 90000 > ALL (
    SELECT e.salary FROM employees e WHERE e.manager_id = m.emp_id
);

2 FAANG-Level Questions

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Overall max is Arjun at 185000 -> second highest is 160000 (Karan Verma)

SELECT DISTINCT e1.salary
FROM employees e1
WHERE 1 = (
    SELECT COUNT(DISTINCT e2.salary)
    FROM employees e2
    WHERE e2.salary > e1.salary
);
-- "exactly 1 distinct salary is greater than mine" = I am the second highest

SELECT d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
)
AND NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary <= 90000
);
-- Result: none of our 5 non-empty departments qualify (each has at least one employee at or below 90000)

SELECT d.dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary <= 90000
);
-- Result: Legal included, since it has no violating employees (vacuously true)

1 Production Scenario

-- Data quality gate: fail the load (or quarantine these rows) if any exist
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);
-- Flags order 5005 (customer_id 999, doesn't exist in customers)

10 Practice Questions

Write the SQL yourself before expanding each answer.

SELECT emp_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id AND e.salary > 150000
);

SELECT emp_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.emp_id
);

SELECT customer_id
FROM (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    WHERE customer_id IS NOT NULL
    GROUP BY customer_id
) t
WHERE order_count > 1;
-- Rahul Bansal (201) placed 2 orders

SELECT e.emp_name
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
);

SELECT dept_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);
-- Legal

SELECT e.emp_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary = (
    SELECT MIN(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
);

SELECT e.emp_name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.manager_id = e.manager_id
);
-- Note: employees with manager_id = NULL (Arjun, Karan, Meera, Divya, Ishita) compare against
-- the average of OTHER NULL-manager employees -- since NULL = NULL is never true in the correlation,
-- double check this edge case: most engines treat "e2.manager_id = e.manager_id" as UNKNOWN when both are NULL,
-- so top-level employees would each compare against an EMPTY set (AVG of nothing = NULL), and salary > NULL is UNKNOWN --
-- meaning all NULL-manager employees are silently excluded. Flag this out loud as a known limitation.

SELECT order_id, total_amount
FROM orders
WHERE customer_id IN (
    SELECT customer_id FROM customers WHERE country != 'India'
);
-- Grace Lin's and Omar Haddad's orders (5002, 5004)
-- Note: order 5005 (customer_id 999) is correctly excluded, since 999 isn't in the customers list at all