Subqueries, EXISTS vs IN and CTEs Explained

A subquery is a query inside another query. Interviewers like them because a few small rules (and one famous NULL trap) separate people who memorized syntax from people who understand SQL.

Kinds of subqueries

-- 1. Scalar: returns one value
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 2. Multi-row: used with IN, ANY, ALL
SELECT name
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name <> 'HR');

-- 3. Derived table: a subquery in FROM (it needs an alias)
SELECT d.department_id, d.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS d
WHERE d.avg_salary > 60000;

IN versus EXISTS

-- Customers who have at least one order
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

• IN compares a value against a list of values. EXISTS only asks "does at least one matching row exist?" and returns TRUE or FALSE. It can stop at the first match, and the columns in its SELECT list do not matter (SELECT 1 is a convention).
• On modern optimizers, IN and EXISTS often get the same execution plan, so do not claim that one is always faster. The real difference is how they treat NULL, below.

The NOT IN trap

Suppose orders.customer_id contains a NULL, like order 13 in the practice data, an order that was never linked to a customer:

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- returns zero rows!

Why? id NOT IN (1, 2, NULL) expands to id <> 1 AND id <> 2 AND id <> NULL. The last comparison is UNKNOWN, so the whole condition can never be TRUE, and no row qualifies.

Three safe alternatives:

-- 1. NOT EXISTS (the usual answer)
SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 2. LEFT JOIN anti-join
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- 3. Keep NOT IN but remove the NULLs
SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

This is called an anti-join: rows in one table that have no match in another. See also customers without orders.

Correlated subqueries

A subquery is correlated when it refers to a column of the outer query. Logically it is evaluated once per outer row, although the optimizer often rewrites it as a join.

-- Employees who earn more than the average of their own department
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(x.salary)
    FROM employees x
    WHERE x.department_id = e.department_id
);

On the practice data this returns Asha (above Engineering's average) and Dev (above Sales' average). Gita is not returned: she has no department, so x.department_id = e.department_id is never true and the average is NULL.

A correlated subquery that the optimizer cannot rewrite can be slow on large tables, because it runs repeatedly. A join or a window function is often the faster alternative.

Common table expressions (CTEs)

A CTE is a named result set that exists for one statement. It makes complex queries readable by splitting them into steps:

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department_id = e.department_id
WHERE e.salary > d.avg_salary;

Use a CTE when you want readability, when you need to reference the same result more than once in a statement, or when you need recursion. A CTE is not stored anywhere and is not automatically faster than a subquery. The optimizer treats it much like one. If you need to reuse a result across several statements, use a temporary table.

Recursive CTEs

A recursive CTE walks a hierarchy, such as who reports to whom:

-- SQL Server (PostgreSQL and MySQL need: WITH RECURSIVE)
WITH chain AS (
    SELECT id, name, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL                 -- anchor: the top of the tree

    UNION ALL

    SELECT e.id, e.name, c.depth + 1         -- recursive step: one level further down
    FROM employees e
    JOIN chain c ON e.manager_id = c.id
)
SELECT name, depth
FROM chain
ORDER BY depth, name;

Set operators

The queries must return the same number of columns with compatible types, and column names come from the first query. Put once, at the very end.

Practise

1. Order 13 in the practice data has no customer. Confirm that the query returns nothing, then fix it three ways.
2. Rewrite the correlated example using a CTE and a join.
3. Take the Logic, Subqueries and Windows quiz.