Subqueries

A subquery is a SELECT inside another SQL statement — a query whose result feeds the query around it. It's the first tool that lets SQL answer questions in layers ("find the rows that beat the average" needs the average first), and once it clicks, a whole class of "I'd need two queries for this" problems collapse into one.

The shape that matters most: scalar subqueries

The simplest and most common subquery returns a single value, and you use it anywhere a value is legal — most powerfully, in a WHERE:

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

Read it inside-out: the parenthesized (SELECT AVG(salary) …) runs first, produces one number (say 71000), and the outer query becomes WHERE salary > 71000. This is your exercise exactly — and it's a question you cannot answer with a single flat query, because WHERE salary > AVG(salary) is illegal (you can't use an aggregate in a WHERE that way). The subquery computes the aggregate separately, then the outer query filters against it. Layers.

A scalar subquery must return exactly one row and one column. Return more and SQL errors; a common fix is adding LIMIT 1 or an aggregate to guarantee singularity.

Subqueries in other positions

Because a subquery is "just a value" (or a set of values, or a table), it slots into several places:

-- in SELECT: a value computed per output row
SELECT name, salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- with IN: match against a SET of values
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);   -- customers who ordered

-- with NOT IN: the complement (careful with NULLs — see below)
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- in FROM: a subquery as a derived table (must be aliased!)
SELECT dept, avg_sal FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY department
) AS dept_avgs WHERE avg_sal > 60000;

IN (subquery) is the workhorse: it tests membership against the set the subquery returns. EXISTS (subquery) is its cousin — true if the subquery returns any row — and is often clearer for "does a related row exist?"

Two traps worth knowing now

NOT IN and NULL — if the subquery's result set contains even one NULL, NOT IN returns no rows at all (a consequence of NULL's "unknown" logic from the fundamentals course). When a NOT IN mysteriously returns nothing, a NULL in the subquery is the usual culprit; NOT EXISTS or a LEFT JOIN … IS NULL sidesteps it. (The next lessons' EXCEPT is another clean answer to "in A but not B".)

Correlated subqueries — a subquery can reference the outer query's current row:

SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);

Here the inner query runs once per outer row (recomputing the average for that employee's department) — powerful, but potentially slow, because it's a loop. Recognize the shape; reach for it when the comparison must vary per row.

Your exercise: Above-Average Salaries

Find employees earning strictly more than the company-wide average — the canonical scalar-subquery problem. The whole solution is the first example in this lesson: WHERE salary > (SELECT AVG(salary) FROM employees). Two graded details: strictly greater (>, not >= — someone earning exactly the average is not above it), and the output order (INSERT order here, so no ORDER BY needed — but read every exercise's ordering requirement, because "one per line in some order" and "sorted" are different tests). It's one line of SQL that does something a flat query can't — the essence of why subqueries exist.