Writing and Using Subqueries

A subquery is a complete SELECT statement nested inside another SQL statement. The outer statement is commonly called the outer query or main query, while the nested statement is called the inner query or subquery. Subqueries give you a systematic way to break a complicated data retrieval problem into smaller, self-contained logical steps, making it easier to reason about what the query is doing and to verify each piece independently. Virtually every major relational database system — MySQL, PostgreSQL, SQL Server, Oracle, SQLite — supports subqueries, and mastering them dramatically expands what you can express in a single SQL statement.

The fundamental execution rule is straightforward: the inner query always executes first, and the result it produces is then handed to the outer query to use. Depending on where a subquery is placed and how it is written, it can produce:

• A single value (one column, one row) — called a scalar subquery
• A single row with multiple columns
• A list of values (one column, many rows)
• A full result set (multiple columns, multiple rows) — used as a derived table

Choosing the right form depends on where you embed the subquery and what operator connects it to the outer query. The sections below walk through every major placement and usage pattern with detailed explanations and examples.

Subqueries in the WHERE Clause

The most common place to embed a subquery is inside a WHERE clause, where it filters the rows returned by the outer query. Three operator families cover the vast majority of WHERE-clause subquery use cases.

Using = for a scalar subquery. When you are confident the subquery will return exactly one value — one row and one column — you can compare it directly with the equality operator. For example, suppose you want to find all employees who work in the same department as the employee named Alice Johnson:

SELECT employee_id, first_name, last_name, department_id
FROM   employees
WHERE  department_id = (
           SELECT department_id
           FROM   employees
           WHERE  first_name = 'Alice'
             AND  last_name  = 'Johnson'
       );

The inner query runs first, finds Alice Johnson's department_id (say, 5), and returns that single value. The outer query then executes as if you had written WHERE department_id = 5. If the inner query accidentally returns more than one row, the database raises a runtime error, so use = only when uniqueness is guaranteed — for example, when filtering on a primary key or a column with a unique constraint.

Using IN for a list of values. When the subquery may return multiple rows, replace = with IN. The outer query then keeps any row whose column value appears anywhere in that list. Suppose you want all orders placed by customers who live in California:

SELECT order_id, customer_id, order_date, total_amount
FROM   orders
WHERE  customer_id IN (
           SELECT customer_id
           FROM   customers
           WHERE  state = 'CA'
       );

The inner query returns a list of every customer_id from California — potentially thousands of values. The outer query keeps only the orders whose customer_id is anywhere in that list. NOT IN works symmetrically, keeping rows whose value does not appear in the subquery result. Be careful with NOT IN when the subquery might return NULL values: if even one NULL is in the list, NOT IN returns no rows at all because SQL cannot determine whether a value is "not equal to unknown".

Using comparison operators with ANY and ALL. For range-based filtering, you can combine standard comparison operators (>, <, >=, <=, <>) with the keywords ANY or ALL:

• value > ANY (subquery) — TRUE if the value is greater than at least one value in the subquery result (equivalent to greater than the minimum)
• value > ALL (subquery) — TRUE if the value is greater than every value in the subquery result (equivalent to greater than the maximum)

-- Find products priced higher than at least one product in category 3
SELECT product_id, product_name, price
FROM   products
WHERE  price > ANY (
           SELECT price
           FROM   products
           WHERE  category_id = 3
       );

-- Find products priced higher than every product in category 3
SELECT product_id, product_name, price
FROM   products
WHERE  price > ALL (
           SELECT price
           FROM   products
           WHERE  category_id = 3
       );

Subqueries in the FROM Clause (Derived Tables)

When you place a subquery in the FROM clause, the database treats its result set as a temporary, virtual table for the duration of that query. This virtual table is called a derived table. Derived tables do not persist anywhere — they exist only while the query runs and are discarded afterwards, so they never consume disk space or affect other sessions.

A critical syntax requirement: every derived table must have an alias. Without an alias the outer query has no name by which to reference the virtual table, and the database will reject the statement.

The classic use case for derived tables is performing an aggregation first and then filtering or joining on those aggregated results — something you cannot do directly with a WHERE clause (which runs before aggregation). Consider finding all departments whose average salary exceeds $75,000:

SELECT dept_summary.department_id,
       dept_summary.dept_name,
       dept_summary.avg_salary
FROM (
    SELECT d.department_id,
           d.department_name  AS dept_name,
           AVG(e.salary)      AS avg_salary
    FROM   employees e
    JOIN   departments d ON e.department_id = d.department_id
    GROUP BY d.department_id, d.department_name
) AS dept_summary
WHERE dept_summary.avg_salary > 75000;

The inner query calculates the average salary per department and gives that result set the alias dept_summary. The outer query then filters dept_summary using a plain WHERE clause, something that would require a HAVING clause or a CTE if written differently. You can also JOIN a derived table to other tables, ORDER BY it, and apply any other outer-query clauses to it just like a real table.

Subqueries in the SELECT Clause (Scalar Subqueries)

A subquery placed inside the SELECT list is called a scalar subquery. It must return exactly one column and exactly one row for every row processed by the outer query. The returned value appears as an additional column in the output. This technique lets you attach a computed reference value, a count, or an aggregate to each row without writing an explicit JOIN.

SELECT e.employee_id,
       e.first_name,
       e.last_name,
       e.salary,
       (SELECT AVG(salary)
        FROM   employees e2
        WHERE  e2.department_id = e.department_id) AS dept_avg_salary
FROM   employees e;

For every employee row in the outer query, the scalar subquery calculates the average salary of that employee's department and places the result in the dept_avg_salary column. Notice that the inner query references e.department_id from the outer query — this makes it a correlated scalar subquery (correlated subqueries are discussed in detail next).

The strict one-row-one-column rule is enforced at runtime. If the subquery returns two or more rows for any outer row, the database immediately raises an error such as "Subquery returns more than 1 row". Always ensure the subquery's logic guarantees uniqueness — for example, by aggregating with AVG, SUM, MAX, or by filtering to a single known primary key.

A genuine performance concern: because the scalar subquery may re-execute once per row of the outer query, scanning large tables row-by-row can be orders of magnitude slower than the equivalent JOIN. Modern query optimizers sometimes detect and rewrite scalar subqueries as joins automatically, but this is not guaranteed. On large datasets, benchmark both approaches.

Correlated vs. Non-Correlated Subqueries

This distinction is one of the most important performance and design concepts in subquery writing.

A non-correlated subquery is completely self-contained. It does not reference any column from the outer query, so the database can execute it once, cache the result, and reuse that cached result for every row of the outer query. This makes non-correlated subqueries generally efficient:

-- Non-correlated: the inner query runs once and returns a fixed list
SELECT product_id, product_name, price
FROM   products
WHERE  category_id IN (
           SELECT category_id
           FROM   categories
           WHERE  active = 1
       );

A correlated subquery contains a reference to a column in the outer query (here, e.department_id is the correlation). Because the inner query's result depends on the current outer row's values, it must re-execute for every row the outer query processes:

-- Correlated: the inner query re-executes for every employee row
SELECT e.employee_id,
       e.first_name,
       e.salary
FROM   employees e
WHERE  e.salary > (
           SELECT AVG(salary)
           FROM   employees e2
           WHERE  e2.department_id = e.department_id
       );

This query finds every employee earning more than their own department's average salary — a genuinely row-by-row comparison that a simple non-correlated subquery cannot express. The trade-off is cost: if the employees table has 50,000 rows, the inner query potentially executes 50,000 times. For large tables, rewriting as a JOIN against a derived table or CTE that pre-computes department averages is usually much faster:

-- Equivalent rewrite using a derived table (often faster)
SELECT e.employee_id,
       e.first_name,
       e.salary
FROM   employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM   employees
    GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id
WHERE  e.salary > dept_avg.avg_salary;

Using EXISTS and NOT EXISTS with Subqueries

EXISTS and NOT EXISTS are special predicate operators designed specifically to work with subqueries. Rather than comparing values, they test only whether the subquery produces any rows at all.

EXISTS returns TRUE if the subquery returns one or more rows, and FALSE if it returns no rows. The actual column values inside the subquery are irrelevant — by convention, developers often write SELECT 1 or SELECT * inside an EXISTS subquery to make this clear:

-- Find customers who have placed at least one order
SELECT c.customer_id, c.first_name, c.last_name
FROM   customers c
WHERE  EXISTS (
           SELECT 1
           FROM   orders o
           WHERE  o.customer_id = c.customer_id
       );

NOT EXISTS is the logical inverse — it returns TRUE only when the subquery returns no rows, making it ideal for finding records that have no matching related records:

-- Find customers who have never placed an order
SELECT c.customer_id, c.first_name, c.last_name
FROM   customers c
WHERE  NOT EXISTS (
           SELECT 1
           FROM   orders o
           WHERE  o.customer_id = c.customer_id
       );

A significant performance advantage of EXISTS over IN is short-circuit evaluation: the database stops scanning the inner table the moment it finds the first matching row. For large related tables this can reduce I/O dramatically. Furthermore, unlike NOT IN, NOT EXISTS is not affected by NULL values in the subquery — it remains logically correct even when the related table contains nulls.

Best Practices for Writing Subqueries

Writing subqueries that are correct, readable, and performant requires deliberate habits. The following guidelines represent widely accepted best practices among SQL practitioners.

• Indent consistently. Each level of nesting should be indented relative to the outer query. Readers should be able to see the subquery boundaries at a glance. Combine indentation with meaningful aliases (dept_avg, recent_orders) to communicate intent.
• Choose the right operator for the job. Use = only when uniqueness is guaranteed. Use IN for list matching when NULL contamination is not a risk. Prefer EXISTS / NOT EXISTS for existence checks, especially on large tables or when the related table might contain NULLs.
• Guard against multi-row scalar subqueries. Before deploying a query with a scalar subquery in the SELECT list or with the = operator, verify — ideally with test data that represents production volumes and edge cases — that the subquery cannot produce more than one row. Adding a LIMIT 1 or wrapping with MAX() / MIN() can serve as a safeguard where appropriate.
• Evaluate correlated subqueries for performance. A correlated subquery is a loop inside SQL. On small tables this is fine; on tables with millions of rows it can make queries unacceptably slow. Always consider whether the logic can be rewritten as a JOIN or a Common Table Expression (CTE) using the WITH clause, which pre-computes a result set once and lets the outer query reference it by name — often the clearest and most performant alternative.
• Use CTEs for multi-level nesting. When subqueries become deeply nested (a subquery inside a subquery inside another subquery), the code becomes very difficult to read and debug. CTEs break the logic into named, sequential steps:

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM   employees
    GROUP BY department_id
),
high_earners AS (
    SELECT e.employee_id, e.first_name, e.salary, e.department_id
    FROM   employees e
    JOIN   dept_avg da ON e.department_id = da.department_id
    WHERE  e.salary > da.avg_salary
)
SELECT he.employee_id,
       he.first_name,
       he.salary,
       d.department_name
FROM   high_earners he
JOIN   departments d ON he.department_id = d.department_id
ORDER BY he.salary DESC;

The CTE version is longer but each named block can be read and tested independently, making maintenance far easier than an equivalent multi-level nested subquery. As a general rule: use subqueries when they are simple and localized; reach for CTEs when the logic becomes complex, repeated, or deeply nested.