Introduction to Subqueries

A subquery (sometimes called an inner query or nested query) is a complete SQL query written inside another SQL query. The outer query, often called the outer query or main query, uses the result produced by the subquery to do its own work — filter rows, compute a value, or act as a source table to select from.

Think of a subquery as a way to answer a smaller question first, and then feed that answer into a bigger question. For example, "which products cost more than the average product price" is really two questions chained together: first, "what is the average product price" (the subquery), and then, "which products are above that number" (the outer query). SQL lets you write both in a single statement instead of running two separate queries and gluing the results together in application code.

SQL

-- The inner query computes one number: the average price
-- The outer query uses that number to filter products
SELECT product_name, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

Tip

Subqueries are always evaluated as part of executing the outer query. A subquery can be as simple as a single value, or as complex as a full query with its own joins, GROUP BY, and ORDER BY clauses.

Where a Subquery Can Appear

Subqueries are not restricted to one spot in a statement — SQL allows them almost anywhere a value, a row, or a table is expected. The clause it sits in changes what shape of result it is allowed to return.

Location	Also Called	What It Returns	Example Use
In `SELECT`	Scalar subquery	A single value (one row, one column)	Show each row alongside a company-wide average
In `FROM`	Derived table / inline view	A full result set treated as a table	Pre-aggregate data, then filter or join the result
In `WHERE` / `HAVING`	Filtering subquery	A single value, a list of values, or a set of rows	Keep only rows matching a computed condition
Standalone with `EXISTS`	Existence check	Not a value at all — just whether any row exists	Test for the presence of related rows

Why Use a Subquery at All?

You could often get the same answer with a JOIN, a temporary table, or two separate queries run from your application. Subqueries earn their place because they let you express multi-step logic — "first compute this, then use it here" — in a single, self-contained SQL statement. That has real benefits.

Readability for step-by-step logic — the query visually mirrors how you'd explain the problem out loud: "find customers who did X, where X is defined by this smaller query."
No round trips — the database computes the intermediate result internally, instead of your application code running one query, reading the result, and building a second query.
Atomicity — the whole computation happens in one query execution against a single, consistent snapshot of the data.
Encapsulation — a subquery used as a derived table can pre-shape or pre-aggregate data before the outer query ever touches it, keeping the outer query simpler.

Note

A subquery is not always the fastest option — a correlated subquery in particular can be rewritten as a JOIN for better performance in many databases. Subqueries are primarily a tool for expressing logic clearly; the following pages dig into each specific form and when to reach for it.

A Quick Look at Each Form

SQL

-- 1. Scalar subquery in SELECT — one value per outer row
SELECT product_name, price, (SELECT AVG(price) FROM products) AS avg_price
FROM products;

-- 2. Derived table in FROM — a subquery acting as a table
SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_averages
WHERE avg_salary > 60000;

-- 3. Filtering subquery in WHERE
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE total > 1000
);

-- 4. Existence check with EXISTS
SELECT customer_id, name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Each of these forms is covered in depth on its own page — scalar subqueries, subqueries in WHERE, derived tables in FROM, correlated subqueries, and EXISTS all have their own rules, gotchas, and performance trade-offs worth understanding individually.