What Is a Subquery in SQL?

Introduction

A subquery is a SQL query written inside another SQL statement. It is also called an inner query, nested query, or nested SELECT. The surrounding statement is the outer query or main query. The inner query produces information that the outer statement consumes as a value, a set of values, a table-like result, or an existence answer.

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the company average salary. If it returns 70,000, the outer query behaves as if it were filtering for salary > 70000. The statement expresses a two-step business question in one unit: calculate the comparison value, then return employees above it.

Why Subqueries Are Useful

Many requirements depend on intermediate information. A report may need products priced above their category average, customers who have at least one completed order, employees in departments located in Chicago, or records missing from a target system. The intermediate result may be a single number, a list of identifiers, or a table that needs further filtering and aggregation.

Subqueries keep this dependency close to the statement that uses it. They are not automatically better than joins, common table expressions, or window functions. Their value comes from expressing the logic clearly and giving the surrounding SQL a result of the correct shape.

Inner Query and Outer Query

The outer query defines the final operation. The inner query is enclosed in parentheses and supplies data to one part of that operation. In logical reasoning, evaluate the innermost independent query, understand its result, and then determine how the outer query consumes it.

SELECT ...
FROM ...
WHERE value > (
    SELECT ...
    FROM ...
);

A correlated subquery is different because it refers to the current outer row. It cannot be understood as one independent result, although the optimizer may transform it into an efficient join-like plan.

The Result Shape Is the First Decision

Before choosing an operator, determine what the subquery can return. A scalar subquery supplies one value. A single-column, multi-row subquery supplies a set. A derived table supplies rows and columns. Some database operations support row-valued comparisons using multiple columns.

Ask how many rows and columns can be produced, whether nulls or duplicates are possible, and whether the outer context accepts that shape. Most subquery errors come from an outer operator expecting one value while the inner query can return several rows.

Single-Row Subquery

A single-row subquery returns at most one row. Scalar comparison operators such as =, <>, >, >=, <, and <= commonly consume it.

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The aggregate always produces one result row, even when the table is empty, although the value may be null. All employees tied for the maximum salary remain because the outer comparison applies to every employee.

Multi-Row Subquery

A multi-row subquery returns several values. Use an operator designed for a set, such as IN, ANY, or ALL.

SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = 'Chicago'
);

Using equality here is unsafe because several departments may be located in Chicago. An equality comparison does not mean "equals one of these rows" and normally raises an error when the subquery returns more than one row.

Scalar Subquery

A scalar subquery returns one value and can appear wherever the database allows a scalar expression. It can be used in a select list, comparison, arithmetic expression, or data-modification statement.

SELECT employee_name,
       salary,
       (SELECT AVG(salary) FROM employees) AS company_average,
       salary - (SELECT AVG(salary) FROM employees) AS difference
FROM employees;

The optimizer may calculate the independent average once rather than once per employee. Do not rely on textual repetition to infer physical execution; inspect the plan when performance matters.

Subquery in WHERE

The WHERE clause is the most familiar location. The inner result can supply a threshold, membership set, or existence decision. The outer query then filters rows according to that result.

SELECT product_id, product_name, price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

This compares every product with the global average. A correlated version can compare each product with its own category average.

Subquery with IN

IN asks whether an outer value equals any non-null value in the returned set. It is natural for membership questions.

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE status = 'COMPLETED'
);

Duplicate customer IDs in the inner result do not duplicate outer customers because IN tests membership rather than joining rows. When only relationship existence matters, EXISTS may communicate the intent more directly.

The NOT IN and NULL Problem

NOT IN requires special care. If the inner set contains null, comparisons can become unknown, and the query may return no rows even when many outer values appear absent.

-- Risky if orders.customer_id can be NULL
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);

Filtering nulls inside the subquery can make the intended set explicit, but NOT EXISTS is usually safer for anti-relationship questions because its logic is based on matching rows rather than three-valued set comparison.

EXISTS Subquery

EXISTS is true when the correlated or independent subquery returns at least one row. The selected expression inside is not the point; SELECT 1 is a conventional way to show that only existence matters.

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'COMPLETED'
);

The engine can often stop searching after finding a qualifying row. The outer customer appears once regardless of how many completed orders exist.

NOT EXISTS Subquery

NOT EXISTS returns outer rows for which no qualifying inner row exists. It is the preferred expression for many absence checks.

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

This finds customers with no order and behaves predictably when unrelated inner columns contain null. The correlation key itself must still be complete, including tenant or environment components where required.

Correlated Subquery

A correlated subquery refers to columns from the current outer row. It logically asks a related question for each row.

SELECT e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Each employee is compared with the average of that employee's department. The alias e.department_id creates the correlation. Include every key component required to define the correct group.

Non-Correlated Subquery

A non-correlated subquery does not reference the outer query and can be evaluated independently. The global salary-average example is non-correlated. The optimizer may evaluate it once, cache its result, or integrate it into a larger plan.

Correlated does not automatically mean slow, and non-correlated does not guarantee fast. Modern optimizers can transform many forms into semi-joins, anti-joins, aggregates, or regular joins. Data volume, indexes, selectivity, and estimates determine the actual cost.

Subquery in FROM as a Derived Table

A subquery in FROM produces a table-like result called a derived table. It must normally have an alias, and its output columns become available to the surrounding query.

SELECT d.department_id, d.average_salary
FROM (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS d
WHERE d.average_salary > 70000;

Derived tables are useful for separating aggregation or transformation from later filtering. The optimizer may inline them rather than materializing them.

Subquery in SELECT

A scalar subquery in the select list can calculate an attribute for each outer row. For example, it can count a customer's orders.

SELECT c.customer_id,
       c.customer_name,
       (
           SELECT COUNT(*)
           FROM orders AS o
           WHERE o.customer_id = c.customer_id
       ) AS order_count
FROM customers AS c;

This is readable, but a left join to a preaggregated order count may be more efficient or easier to extend when several child metrics are needed. Compare plans and preserve customer grain.

Subquery in HAVING

A subquery can supply a threshold for grouped results. The following returns departments whose average salary exceeds the company average.

SELECT department_id, AVG(salary) AS department_average
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

The outer aggregation is per department, while the subquery produces one global comparison value. Always distinguish group-specific and global calculations.

ANY and ALL

ANY compares an outer value with at least one value in the inner set. ALL requires the comparison to hold for every value. For example, a salary greater than ALL salaries in department 10 exceeds that department's maximum non-null salary.

Empty sets and null values affect these operators in ways that deserve explicit testing. Aggregates such as MIN or MAX may state the business intention more clearly when the comparison is specifically against an extreme.

Aggregate Subqueries

Aggregates naturally reduce many rows into one value or one row per group. They are common in threshold and comparison questions: above average, equal to maximum, below budget, or greater than historical total.

An aggregate without GROUP BY normally returns one row, even when its value is null. An aggregate with GROUP BY can return several rows, so the surrounding operator must accept a set or the grouping must be constrained to one expected group.

Second-Highest Salary

One method finds the largest salary below the maximum and then returns every employee tied at that value.

SELECT employee_id, employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (
        SELECT MAX(salary)
        FROM employees
    )
);

This demonstrates nested subqueries. A dense ranking window function may be clearer when several ranks are needed.

Nested Subqueries

A nested subquery contains another subquery. Evaluate the innermost logical result first and work outward. Deep nesting can become difficult to review, especially when several aliases and null rules interact.

Use descriptive aliases, format each level clearly, and consider named CTEs when the logic represents distinct reusable stages. Refactoring for readability does not automatically improve performance, but it reduces reasoning and maintenance errors.

Subqueries in UPDATE

A subquery can calculate values or select target rows for an update.

UPDATE employees
SET bonus = salary * 0.05
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE performance_rating = 'HIGH'
);

Preview the target set with an equivalent SELECT, use a transaction where appropriate, and verify whether the inner result can change as rows are modified. Database-specific update semantics may apply.

Subqueries in DELETE and INSERT

A delete can use NOT EXISTS to target orphan or expired rows, while INSERT ... SELECT can read from a subquery that transforms or aggregates data. These statements can affect many rows, so validate counts, constraints, null behavior, and transaction boundaries before execution.

Do not add an arbitrary LIMIT 1 to suppress a cardinality error in data modification. Determine why multiple candidates exist and encode the required deterministic rule.

Subquery Versus JOIN

A join is natural when the final result needs columns from related rows. A subquery is natural when the outer query needs a value, set, aggregate, or relationship test. Either form may be transformed into a similar execution plan.

A join can multiply outer rows when several children match. An EXISTS subquery returns an existence answer without producing child combinations. Choose based on result meaning first, then compare plans if performance differs.

Subquery Versus CTE

A common table expression gives a name to a query stage and can make multi-step logic easier to read. It is especially helpful when a derived result is referenced several times or when recursive processing is required.

A CTE is not automatically faster and is not necessarily materialized. Database versions and query shapes differ. Use it for clarity, then inspect the execution plan to understand whether work is duplicated, inlined, or stored.

Subquery Versus Window Function

Window functions calculate group metrics while retaining detail rows. Department averages can be computed once over partitions and then filtered in an outer query.

SELECT employee_id, employee_name, salary
FROM (
    SELECT e.*,
           AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees AS e
) AS ranked
WHERE salary > dept_avg;

This can be clearer than a correlated aggregate when several analytic values are needed, but the best form depends on the database and workload.

NULL Behavior

A scalar subquery returning no rows generally produces null. A scalar subquery returning more than one row normally raises an error. Aggregate subqueries can also produce null when no non-null input values exist.

Comparisons with null evaluate to unknown, not true or false. Decide whether missing inner data should exclude the outer row, use a default through COALESCE, or require separate handling. Do not treat null as zero or an empty string unless that is the business rule.

Cardinality and Arbitrary LIMIT

If a scalar subquery unexpectedly returns several rows, the design assumption is wrong or not enforced. Adding LIMIT 1 hides the evidence and may select a different row on different executions.

Use a unique constraint when one row must exist, aggregate when one value should summarize many rows, or apply a deterministic ranking when the requirement is latest, highest priority, or otherwise ordered. Cardinality must come from business logic, not convenience.

Performance and Indexing

Subquery cost depends on rows processed, correlation frequency, filter selectivity, indexes, statistics, aggregation, sorting, and optimizer transformations. A correlated existence check on orders can benefit from an index beginning with customer_id and including selective status or date columns according to workload.

Inspect the actual plan. Look for repeated full scans, large intermediate sets, inaccurate estimates, sorts, spills, and non-sargable expressions. Do not assume all subqueries are slow or that rewriting one as a join guarantees improvement.

Scalar Subquery with No Rows or Too Many Rows

A scalar subquery has a strict contract: it must produce at most one value for the surrounding expression. When it produces no row, databases generally supply null. The outer comparison then evaluates to unknown unless the query handles that null explicitly. This can be correct for an optional relationship, but it should not be accidental.

When the scalar subquery produces two or more rows, the statement normally fails because SQL cannot choose one value. Do not silence the error with an unordered LIMIT 1. If one row is required, enforce uniqueness. If one value should summarize several rows, use an aggregate. If the requirement is latest or highest priority, define deterministic ordering and ranking, including a stable tie-breaker.

Latest Record Per Customer

A common requirement is the most recent order, payment, status, or address for each parent. A correlated subquery can compare each child timestamp with the maximum timestamp for that parent.

SELECT o.order_id, o.customer_id, o.order_date
FROM orders AS o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders AS o2
    WHERE o2.customer_id = o.customer_id
);

This preserves ties: if two orders share the latest timestamp, both are returned. That may be correct. If exactly one row is required, use a ranking rule that orders by timestamp and then by a stable identifier. An index such as (customer_id, order_date) can support the relationship, but test the actual plan and data distribution.

ORDER BY and Top-N Subqueries

Ordering inside a subquery matters only when the SQL construct uses that order, such as a top-N operation, window function, or database-specific limiting clause. A bare ORDER BY in an intermediate result does not guarantee the final output order; only the outermost order controls presentation.

For top-N per group, window functions are usually clearer than a deeply correlated ordering subquery. For a single global top value, MAX or MIN states the intention directly and handles ties differently from selecting one arbitrary row. Decide whether the requirement is one record, one value, or every record tied at the extreme.

Products Above Their Category Average

A correlated aggregate can compare each product with the average within its own category rather than the global catalog average.

SELECT p.product_id, p.product_name, p.price
FROM products AS p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products AS p2
    WHERE p2.category_id = p.category_id
);

The correlation changes the comparison group for every category. Null category IDs need an explicit business rule because equality does not match null to null. A window-function version can calculate all category averages in one logical stage and is useful when the average must also be displayed.

Nested Aggregate Through a Derived Table

SQL generally does not allow one aggregate to be nested directly inside another at the same query level. A derived table can calculate first-stage values and let the outer query aggregate those results. For example, calculate each customer's total spend and then calculate the average of those customer totals.

SELECT AVG(customer_total) AS average_customer_spend
FROM (
    SELECT customer_id, SUM(total_amount) AS customer_total
    FROM orders
    WHERE status = 'COMPLETED'
    GROUP BY customer_id
) AS customer_totals;

The two levels have different grains: one row per customer inside and one overall value outside. This separation prevents high-order customers from being weighted merely because they have more order rows.

Duplicate Values and Set Semantics

A multi-row subquery can return duplicates. Membership operators such as IN care whether a value is present, so repeated inner values do not repeat outer rows. A derived table joined to another table behaves differently because every duplicate row can participate in matching and expand the result.

Do not add DISTINCT automatically. First determine whether duplicate values are valid, whether uniqueness should be enforced, and whether existence semantics would express the requirement better. Removing duplicates can add sorting or hashing cost and may conceal a broken relationship.

Group-Specific Maximum and Ties

To return every employee tied for the highest salary in each department, correlate a maximum calculation with the department key.

SELECT e.employee_id, e.employee_name,
       e.department_id, e.salary
FROM employees AS e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

The equality intentionally preserves ties. Replacing it with a top-one subquery would change the requirement to one winner per department. When the output needs first, second, and third ranks or additional group metrics, DENSE_RANK or another window function often provides a clearer model.

How the Optimizer May Transform Subqueries

SQL describes the required result, not a mandatory row-by-row execution procedure. An optimizer may decorrelate a subquery, convert EXISTS into a semi-join, convert NOT EXISTS into an anti-join, push filters into a derived table, or evaluate an independent scalar result once.

This is why syntax alone cannot establish performance. Compare actual plans, row counts, reads, CPU, memory, and elapsed time. If a correlated query performs badly, confirm that the correlation keys are indexed and selective, but also investigate stale statistics, non-sargable expressions, skew, and incorrect cardinality estimates. Rewrite only when the alternative remains semantically equivalent and measurements show a real benefit.

E-Commerce Examples

An online store may use EXISTS to find customers with a completed order, NOT EXISTS to find products never sold, a scalar aggregate to compare a product with category average price, and a derived table to compare order totals with the average customer spend.

Choose a form that preserves the required grain. Joining customers to every qualifying order and then adding DISTINCT is less direct than testing existence when only customer qualification matters.

Software Testing Examples

A test dashboard can use EXISTS to find test cases that ever failed and NOT EXISTS to find tests with no failure. "Never failed" is not the same as "executed and never failed" because a test with no executions also has no failure.

For the second requirement, combine one EXISTS condition proving at least one execution with one NOT EXISTS condition excluding failures. This demonstrates why precise business language matters.

API and Inventory Examples

An API catalog can identify endpoints with no request in the last seven days through NOT EXISTS. Inventory can find products below warehouse-specific averages with a correlated aggregate or identify products missing any inventory record with an anti-subquery.

Include environment, tenant, warehouse, and date components in correlation keys where necessary. An incomplete correlation can compare records from unrelated domains and produce plausible but false results.

Banking and Payroll Examples

A banking query can identify accounts whose balance is below their customer's average, transactions above an account-specific threshold, or accounts with no activity in a reporting period. Payroll can compare an employee's salary with a department average or detect active employees without a payroll row for a required period.

Financial queries require explicit null and period handling. Use bounded date ranges and exact status rules, and verify that monetary aggregates operate at the intended grain.

Data Validation and Duplicate Detection

Subqueries support migration and quality checks. A source row can be tested for the absence of its target counterpart. Duplicate business keys can be identified by grouping inside a subquery and joining or filtering against the duplicate set.

SELECT customer_number
FROM customers
WHERE customer_number IN (
    SELECT customer_number
    FROM customers
    GROUP BY customer_number
    HAVING COUNT(*) > 1
);

Return enough identifying detail to investigate each duplicated record rather than only the repeated key.

Security and Multi-Tenant Correlation

Every correlated subquery in a multi-tenant system must respect the tenant boundary. Correlating only on order ID or customer ID can inspect another tenant's rows when identifiers repeat. Include tenant keys in both outer filters and inner relationships, and rely on database access controls as an additional layer.

Authorization checks expressed with EXISTS must test the complete resource, principal, role, and scope relationship. A logically incomplete subquery can become a data-exposure defect.

Common Subquery Mistakes

  • Using = when the inner query can return multiple rows.
  • Using NOT IN without accounting for null.
  • Omitting part of a composite correlation key.
  • Adding LIMIT 1 without deterministic business ordering.
  • Using a join plus DISTINCT when only existence is required.
  • Assuming correlated subqueries are always slow.
  • Assuming joins are always faster.
  • Ignoring result grain, duplicates, and missing rows.
  • Nesting deeply when named stages would be clearer.
  • Selecting unnecessary columns inside derived tables.

Subquery Debugging Workflow

  1. Run the inner query independently when possible.
  2. Count its rows and columns and inspect representative values.
  3. Check nulls, duplicates, and expected uniqueness.
  4. Confirm that the outer operator accepts the returned shape.
  5. Verify every correlation key and tenant boundary.
  6. Test no-match, one-match, multiple-match, and null cases.
  7. Compare the final grain and row count with the requirement.
  8. Inspect the actual execution plan at realistic volume.

Debugging from the inside outward makes both cardinality and logic visible.

Subquery Decision Guide

RequirementUseful form
Compare with one calculated valueScalar aggregate subquery
Match any value in a setIN
At least one related row existsEXISTS
No related row existsNOT EXISTS
Build a table-like processing stageDerived table or CTE
Compare with each row's groupCorrelated aggregate or window function
Return related detail columnsJoin

Interview Questions and Answers

What is a subquery?

It is a query nested inside another SQL statement whose result is consumed by the surrounding statement.

What is a correlated subquery?

It references the current outer row and logically computes or tests a related result for that row.

What is the difference between IN and EXISTS?

IN tests membership in a returned value set. EXISTS tests whether at least one qualifying row exists.

Why can NOT IN be dangerous?

A null in the inner set can make comparisons unknown and unexpectedly eliminate all results.

Can a subquery return multiple columns?

Yes, in table contexts such as FROM, and in database-supported row comparisons. A scalar context expects one column and at most one row.

Are subqueries slower than joins?

Not inherently. Optimizers often transform equivalent forms. Performance depends on plan, data, indexes, statistics, and cardinality.

Core Mental Model

Outer question
  -> needs intermediate information
  -> subquery produces a result
  -> result has a shape
  -> outer context consumes that shape
  -> final result

Always ask:
1. How many rows can the subquery return?
2. How many columns can it return?
3. Can it return NULL or duplicates?
4. Does it depend on the current outer row?
5. Is the outer operator appropriate?

Key Takeaway

A subquery is an inner SQL statement that supplies a value, set, table-like result, or existence decision to an outer statement. Its correctness depends on matching the inner result shape to the surrounding operator and handling cardinality, nulls, duplicates, and correlation keys deliberately.

Use joins when related columns belong in the output, EXISTS or NOT EXISTS for relationship tests, CTEs for readable named stages, and window functions for analytics that preserve detail. Choose the clearest correct expression, then verify its execution plan with realistic data rather than relying on assumptions about syntax.