SQL Subqueries Explained: Nested SELECT Statements

A subquery is a SELECT statement nested inside another query, wrapped in parentheses. The inner query runs first (conceptually), and its result feeds into the outer query — as a single value, a list, or an entire virtual table. They're most useful when a JOIN would be awkward or when you only need an aggregate result to compare against, not the joined rows themselves.

Scalar subqueries — a single value

A scalar subquery returns exactly one row, one column — a single value you can use anywhere an expression is allowed, including right inside a comparison:

-- Employees earning more than the company average
SELECT first_name, salary
FROM Employees
WHERE salary > (
    SELECT AVG(salary) FROM Employees
);

The inner query computes one number (the average salary across the whole table); the outer query then compares every employee's salary against that single number.

Subqueries with IN — a list of values

When the inner query returns multiple rows of a single column, pair it with IN (or NOT IN):

-- Products that have never been ordered
SELECT product_id, name
FROM Products
WHERE product_id NOT IN (
    SELECT DISTINCT product_id FROM Orders
);

Watch out: if the inner query's column can contain NULL, NOT IN can silently return zero rows for the whole query, because comparing anything to NULL is neither true nor false. If NULLs are possible, filter them out inside the subquery (WHERE product_id IS NOT NULL) or use NOT EXISTS instead.

EXISTS — does at least one row match?

EXISTS doesn't care what the subquery returns, only whether it returns any row at all. It's often faster than IN for large datasets, since the database can stop as soon as it finds one match.

-- Customers who have placed at least one order
SELECT company
FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id
);

This is a correlated subquery — notice it references c.customer_id from the outer query. Unlike the earlier examples, this inner query can't run on its own; it re-evaluates once per outer row.

Subqueries in FROM — a virtual table

A subquery can also stand in for a table, letting you query the results of another query:

SELECT dept_id, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM Employees
    GROUP BY dept_id
) dept_averages
WHERE avg_salary > 90000;

Here the inner query pre-aggregates by department, and the outer query filters those already-computed averages — something you couldn't do directly with WHERE (see our GROUP BY vs HAVING guide for why).

Subquery vs JOIN: which one?

Both often solve the same problem. General rule of thumb:

• Need columns from both tables in the result? Use a JOIN.
• Only need to filter or compare against a value/list computed from another table? A subquery is usually more readable.
• Checking existence without needing any data from the other table? EXISTS reads more clearly than a JOIN + DISTINCT.

Related reading

• SQL JOINs Explained: INNER, LEFT, RIGHT & FULL
• 25 SQL Interview Questions and Answers