SQL Subqueries Explained: Correlated, EXISTS & NOT IN

Try to write this query in your head: "list employees who earn more than the average salary." You start with WHERE salary > ... — and hit a wall. Greater than what? You don't know the average; it lives inside the data itself. You'd naturally do it in two steps: first find the average, then use it. SQL lets you write both steps as one query inside another — a subquery. This lesson takes you from that first simple nesting all the way to the hardest classic in SQL interviews.

What is a subquery?

A subquery (inner query, nested query — same thing) is a SELECT written inside another statement, in brackets. The engine runs the inside first, and the outside uses its result:

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

Alice | 55000
Carol | 62000
Eve   | 70000

Scalar subqueries — exactly one value, or trouble

The subquery above is a scalar subquery: it's expected to return exactly one row, one column — a single value — so it can stand anywhere a value can. Two edge cases you must know:

• It returns two rows? Runtime error — MySQL says "subquery returns more than 1 row".
• It returns zero rows? No error — it quietly becomes NULL, the comparison becomes UNKNOWN, and rows silently vanish. The sneakier bug of the two.

Scalar subqueries also work in the SELECT list — handy for attaching a company-wide figure to every row:

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

Multi-row subqueries — IN

When the inner query returns a list, compare with IN. "Employees who work in a Bangalore-located department":

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

Read inside-out: the inner query builds the list of Bangalore department ids; the outer keeps employees whose dept_id appears in that list. ANY and ALL are the comparison cousins (salary > ALL (…)), worth recognising even if you rarely write them.

Subqueries in FROM — a temporary table you invent

A subquery in the FROM clause acts like a table that exists only for this query (a derived table — it must be given an alias). This is how you aggregate first and join later — the clean fix for lesson 2's fan-out trap:

SELECT c.customer_name, t.orders_count
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) AS orders_count
    FROM orders
    GROUP BY customer_id
) t ON t.customer_id = c.customer_id;

Correlated subqueries — when inner peeks outside

Now make the opening question harder: "employees who earn more than the average of their own department." Can the inner query run once and hand back one number? No — Engineering's average and Sales' average differ. The inner query must know whose row we're currently checking:

SELECT e.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   -- ← the correlation
);

See e.dept_id inside the bracket? The inner query is reaching out and touching the outer query's current row — that makes it correlated, and conceptually it re-runs for every outer row. It's comparing your marks to your own class's average instead of the whole school's.

The performance half of the story: naively, 1 lakh outer rows means 1 lakh inner executions — which is why correlated subqueries have a scary reputation. In practice, modern optimizers usually rewrite them into joins. In an interview, say both halves — naive cost and optimizer rescue. That answer sounds senior.

EXISTS — knock on the door, don't inventory the house

Sometimes you don't need any values from the other table — only whether a match exists. Has this customer ever placed an order? You don't need the orders; you need a yes or no.

-- customers who HAVE ordered
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- customers who have NEVER ordered
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

EXISTS is a pure boolean test: it never looks at the selected values (that's why SELECT 1 is the convention) and can stop at the first matching row. Notice NOT EXISTS gives you the anti-join from lesson 2 written a second way — interviewers love asking for both.

Wait — the NOT IN trap that empties your result

The most famous subquery gotcha in SQL, and it's cruel because it fails silently. Suppose you find departments with no employees:

SELECT dept_name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM employees);
-- if ANY employee has dept_id NULL → zero rows. Always.

Why? NOT IN expands into inequality checks: x <> 10 AND x <> 20 AND x <> NULL. That last comparison is UNKNOWN (remember NULL from lesson 1?), and an AND-chain containing UNKNOWN can never become fully TRUE — so the entire query returns zero rows. No error, no warning, just an empty report at 6 pm before a demo.

The safe rewrite they want to hear: NOT EXISTS with a correlated subquery — it checks row existence per outer row, so NULLs in the data can't poison the logic. Explaining why (three-valued logic) is what upgrades your answer from correct to impressive.

The classic: second highest salary

If SQL selection rounds had one national anthem, it's this. Without window functions, the clean trick is a nested subquery:

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

second_highest_salary | 85000

Asked for the Nth highest? This pattern nests deeper and gets ugly — which is exactly the moment to mention window functions as the cleaner tool. Knowing when a technique runs out is as impressive as knowing the technique.

Common mistakes

• NOT IN over a subquery that can return NULL — use NOT EXISTS (or filter IS NOT NULL inside).
• Expecting a scalar subquery to error on zero rows — it returns NULL and your rows silently vanish instead.
• Forgetting the alias on a derived table — FROM (SELECT …) needs a name, even if you never use it.
• Writing a correlated subquery when a plain one works — if the inner query doesn't reference the outer row, don't correlate it.
• Using SELECT * inside EXISTS — harmless but sloppy; the convention is SELECT 1.

Quick recap

Practice Zone — PYQs from real selection rounds

Four write-the-query challenges, easy to genuinely hard — the last one (relational division: "customers who bought every Essential") is a pattern most working engineers can't write cold. Attempt it anyway; the walkthrough is in the solution.

Q1Correlated subquerieseasy

What makes a subquery a correlated subquery?

Asked in

AIt returns more than one rowBIt references a column from the outer query's current rowCIt appears in the FROM clauseDIt uses an aggregate function

Q2The NOT IN NULL trapmedium

WHERE dept_id NOT IN (SELECT dept_id FROM employees) — and the subquery's results happen to include a NULL. What does the outer query return?

Asked in

AAll departments with no employees, as intendedBNo rows at all — the whole query silently returns emptyCAn error about NULL in a listDAll departments, including matched ones

Q3EXISTS vs INmedium

What does EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id) actually check?

Asked in

AWhether the subquery returns the value 1BWhether the subquery returns at least one rowCWhether customer_id equals 1DWhether the orders table exists

Q4Scalar subqueriesmedium

A scalar subquery is used where one single value is expected — e.g. WHERE salary > (SELECT AVG(salary) ...). What happens at runtime if it returns two rows?

Asked in

AThe engine picks the first rowBThe rows are averaged automaticallyCThe query fails with a runtime errorDThe comparison becomes NULL

Q5Rewriting NOT IN safelyhard

The subquery under my NOT IN can return NULLs. What's the standard safe rewrite?

Asked in

ASwitch to NOT EXISTS with a correlated subqueryBWrap the whole query in DISTINCTCUse != instead of NOT INDAdd ORDER BY inside the subquery

Q6Performancehard

On a naive engine, how is a correlated subquery over 1,00,000 outer rows executed?

Asked in

AThe inner query runs once and gets cachedBThe inner query conceptually re-runs once per outer rowCBoth queries run in parallel and mergeDThe engine always refuses and asks for a JOIN

FAQ

Next lesson: ranking rows without collapsing them — Lesson 5: Window Functions →