Introduction to Subqueries (Nested Queries)

As your SQL skills grow, you will encounter questions that a single, flat query cannot answer directly. "Which products cost more than the average price?" or "Which customers have placed at least one order?" are everyday questions that require you to compute an intermediate result first and then use it inside a larger query. This is exactly what subqueries are designed to do.

An SQL subquery is a query nested inside another query. It is one of the most powerful concepts in SQL and, once understood, it opens the door to solving a wide range of data problems elegantly. This guide introduces subqueries from scratch, explains where they can appear, walks through the difference between single-value and multi-value subqueries, and builds up to practical examples with full outputs.

What Is a Subquery?

A subquery (also called a nested query or inner query) is a SELECT statement written inside another SQL statement. The outer statement is called the outer query (or main query). The subquery runs first, produces a result, and then the outer query uses that result as if it were a value, a list, or even a table.

Here is the simplest way to visualize it:

SELECT column1
FROM some_table
WHERE column2 = (
    SELECT MAX(column2)      -- ← This is the subquery
    FROM some_table
);

The subquery is always enclosed in parentheses. The database evaluates the inner query, gets a result, and substitutes that result into the outer query before executing it.

Think of it like a math expression: to compute 5 + (3 × 2), you solve the parentheses first to get 6, then compute 5 + 6 = 11. Subqueries work the same way.

The Sample Schema

We will use a small e-commerce schema throughout this guide:

CREATE TABLE categories (
    category_id   INT PRIMARY KEY,
    category_name VARCHAR(50)
);

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category_id   INT,
    price         DECIMAL(10,2)
);

CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city          VARCHAR(50)
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    total_amount  DECIMAL(10,2)
);

-- categories
INSERT INTO categories VALUES (1, 'Electronics');
INSERT INTO categories VALUES (2, 'Stationery');
INSERT INTO categories VALUES (3, 'Furniture');

-- products
INSERT INTO products VALUES (101, 'Wireless Mouse',      1, 25.99);
INSERT INTO products VALUES (102, 'USB-C Cable',         1, 9.99);
INSERT INTO products VALUES (103, 'Notebook A5',         2, 4.50);
INSERT INTO products VALUES (104, 'Mechanical Keyboard', 1, 89.99);
INSERT INTO products VALUES (105, 'Desk Lamp',           3, 34.50);
INSERT INTO products VALUES (106, 'Pen Pack',            2, 2.99);
INSERT INTO products VALUES (107, 'Standing Desk',       3, 299.99);
INSERT INTO products VALUES (108, 'Monitor Arm',         3, 74.50);

-- customers
INSERT INTO customers VALUES (1, 'Alice Martin', 'New York');
INSERT INTO customers VALUES (2, 'Bob Jones',    'Chicago');
INSERT INTO customers VALUES (3, 'Carol Smith',  'New York');
INSERT INTO customers VALUES (4, 'Dave Wilson',  'Denver');

-- orders
INSERT INTO orders VALUES (1001, 1, '2024-06-10', 115.98);
INSERT INTO orders VALUES (1002, 2, '2024-06-12', 9.99);
INSERT INTO orders VALUES (1003, 1, '2024-06-15', 299.99);
INSERT INTO orders VALUES (1004, 3, '2024-06-18', 39.49);

Subquery in the WHERE Clause

The WHERE clause is the most common place to use a subquery. The subquery computes a value (or set of values), and the outer query filters rows based on that result.

Basic Structure

SELECT columns
FROM table_name
WHERE column operator (
    SELECT column
    FROM another_table
    WHERE condition
);

The operator can be a comparison (=, >, <, >=, <=, !=) for single-value subqueries, or a set operator (IN, NOT IN, ANY, ALL, EXISTS) for multi-value subqueries.

Single-Value Subqueries (Scalar Subqueries)

A single-value subquery (also called a scalar subquery) returns exactly one row and one column. Because the result is a single value, you can use it with standard comparison operators like =, >, or <.

Example: Products Priced Above Average

This is the classic SQL subquery example. First, let's see what the average price is:

SELECT AVG(price) AS avg_price
FROM products;

Now, to find products priced above this average, you could hardcode 67.81 into a WHERE clause. But that value changes whenever products are added or removed. A subquery solves this dynamically:

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

Output:

How it works step by step:

1. The database executes the inner query: SELECT AVG(price) FROM products and gets 67.81.
2. The outer query becomes effectively: SELECT product_name, price FROM products WHERE price > 67.81.
3. Three products match the condition.

The beauty is that this query always returns the correct result, even as the product catalog changes. The average is recalculated every time the query runs.

More Scalar Subquery Examples

Find the most expensive product:

SELECT product_name, price
FROM products
WHERE price = (
    SELECT MAX(price)
    FROM products
);

Find the cheapest Electronics product:

SELECT product_name, price
FROM products
WHERE category_id = 1
  AND price = (
      SELECT MIN(price)
      FROM products
      WHERE category_id = 1
  );

Find orders with a total above the average order amount:

SELECT
    order_id,
    customer_id,
    total_amount
FROM orders
WHERE total_amount > (
    SELECT AVG(total_amount)
    FROM orders
);

The average order amount is 116.36. Only Alice's second order (299.99) exceeds it.

What Happens If a Scalar Subquery Returns More Than One Row?

If you use a comparison operator (=, >, <, etc.) with a subquery that returns multiple rows, the database will raise an error.

Suppose you want to find all products in categories that contain at least one product priced above $50. You might try:

SELECT product_name
FROM products
WHERE category_id = (
    SELECT category_id
    FROM products
    WHERE price > 50
);

ERROR: more than one row returned by a subquery used as an expression

Three products cost more than $50 (Mechanical Keyboard, Monitor Arm, and Standing Desk), spanning two different categories, so the inner query returns multiple rows. The = operator expects exactly one value on the right side, so the query fails.

Fix: Use IN instead of = when the subquery can return multiple rows:

SELECT product_name
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM products
    WHERE price > 50
);

All Electronics and Furniture products are returned because both categories contain at least one item above $50. Only the two Stationery products (Notebook A5 and Pen Pack) are excluded.

This leads us directly to multi-value subqueries.

Multi-Value Subqueries

A multi-value subquery returns multiple rows (but still a single column). Since you cannot compare a single value to a list using =, SQL provides special operators to work with sets of values.

The IN Operator

IN checks whether a value matches any value in the list returned by the subquery.

Find all customers who have placed at least one order:

SELECT customer_name, city
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Output:

The subquery returns the set {1, 2, 3} (the customer IDs found in the orders table). The outer query then filters customers whose customer_id is in that set. Dave Wilson (customer 4) has no orders and is excluded.

The NOT IN Operator

NOT IN does the opposite: it finds rows whose value is not in the subquery result.

Find customers who have never placed an order:

SELECT customer_name, city
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

The ANY and ALL Operators

ANY and ALL extend comparison operators to work with multi-value subqueries.

ANY returns TRUE if the comparison is true for at least one value in the subquery result:

-- Products cheaper than ANY electronics product
-- (i.e., cheaper than the most expensive electronics product)
SELECT product_name, price
FROM products
WHERE price < ANY (
    SELECT price
    FROM products
    WHERE category_id = 1
);

The electronics prices are {25.99, 9.99, 89.99}. A product qualifies if it is cheaper than at least one of these values, in other words, cheaper than the maximum (89.99). Everything except the Standing Desk (299.99) and the Mechanical Keyboard (89.99) itself shows up.

ALL returns TRUE if the comparison is true for every value in the subquery result:

-- Products cheaper than ALL electronics products
-- (i.e., cheaper than the cheapest electronics product)
SELECT product_name, price
FROM products
WHERE price < ALL (
    SELECT price
    FROM products
    WHERE category_id = 1
);

A product qualifies only if it is cheaper than every electronics price, i.e., cheaper than the minimum (9.99). Only the two Stationery products qualify.

Subqueries vs Joins: When to Choose Which

A common question is whether to use a subquery or a join. In many cases, both can produce the same result.

Subquery approach (customers who placed orders):

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Join approach (same result):

SELECT DISTINCT c.customer_name
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id;

Both return the same three customers. Here are guidelines for choosing:

Nesting Subqueries Inside Subqueries

Subqueries can be nested multiple levels deep. Each level is evaluated from the innermost query outward.

Find the names of customers who placed the highest-value order:

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount = (
        SELECT MAX(total_amount)
        FROM orders
    )
);

How it evaluates:

1. Innermost query: SELECT MAX(total_amount) FROM orders returns 299.99.
2. Middle query: SELECT customer_id FROM orders WHERE total_amount = 299.99 returns {1}.
3. Outer query: SELECT customer_name FROM customers WHERE customer_id IN (1) returns Alice Martin.

Subqueries in Other Clauses

While the WHERE clause is the most common location, subqueries can appear elsewhere too. Here is a brief overview of other placements so you are aware of them.

Subquery in SELECT (Scalar Subquery in Column List)

You can use a scalar subquery as a computed column:

SELECT
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,
    price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

Subquery in FROM (Derived Table)

A subquery in the FROM clause acts as a temporary table:

SELECT
    category_name,
    avg_price
FROM (
    SELECT
        c.category_name,
        AVG(p.price) AS avg_price
    FROM categories AS c
    JOIN products AS p ON c.category_id = p.category_id
    GROUP BY c.category_name
) AS category_averages
WHERE avg_price > 30;

The inner query computes the average price per category. The outer query filters to only categories with an average above 30. The alias category_averages is required for derived tables in most databases.

Subquery in HAVING

Subqueries also work inside HAVING to filter groups based on a computed threshold:

SELECT
    category_id,
    COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) > (
    SELECT AVG(cat_count)
    FROM (
        SELECT COUNT(*) AS cat_count
        FROM products
        GROUP BY category_id
    ) AS counts
);

The average number of products per category is 2.67 (Electronics has 3, Stationery has 2, Furniture has 3). Only categories with more than 2.67 products are returned: Electronics and Furniture.

Common Mistakes with Subqueries

Mistake 1: Using = When the Subquery Returns Multiple Rows

As discussed earlier, this causes a runtime error. Always use IN when the subquery might return more than one row.

Wrong:

SELECT customer_name
FROM customers
WHERE customer_id = (
    SELECT customer_id
    FROM orders
);
-- ERROR: more than one row returned

Correct:

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Mistake 2: Forgetting That NOT IN Fails with NULLs

If the subquery result contains even one NULL, NOT IN silently returns no rows.

Wrong (silent bug: returns no rows if any NULL exists):

SELECT product_name
FROM products
WHERE category_id NOT IN (
    SELECT category_id    -- What if a row has NULL category_id?
    FROM products
    WHERE price > 50
);

Correct:

SELECT product_name
FROM products
WHERE category_id NOT IN (
    SELECT category_id
    FROM products
    WHERE price > 50
      AND category_id IS NOT NULL
);

Mistake 3: Using a Multi-Column Subquery Where a Single Column Is Expected

A subquery used with IN, =, or comparison operators must return exactly one column.

Wrong:

SELECT product_name
FROM products
WHERE product_id IN (
    SELECT product_id, price   -- Two columns: ERROR
    FROM products
    WHERE price > 50
);

Correct:

SELECT product_name
FROM products
WHERE product_id IN (
    SELECT product_id          -- One column: OK
    FROM products
    WHERE price > 50
);

Putting It All Together: A Real-World Example

Let's combine several concepts into a practical query. The goal: find all customers from New York who have spent more than the average order amount.

SELECT
    c.customer_name,
    c.city,
    o.order_id,
    o.total_amount
FROM customers AS c
JOIN orders AS o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
  AND o.total_amount > (
      SELECT AVG(total_amount)
      FROM orders
  );

Step-by-step evaluation:

1. The subquery computes the average order total: (115.98 + 9.99 + 299.99 + 39.49) / 4 = 116.36.
2. The join connects customers to their orders.
3. The WHERE clause filters to New York customers with orders above 116.36.

Output:

Alice has two orders (115.98 and 299.99), but only the 299.99 order exceeds the average. Carol (also from New York) has one order of 39.49, which is below the average.

Summary

A SQL subquery is a SELECT statement embedded inside another SQL statement, enclosed in parentheses. It allows you to break complex questions into logical steps: compute an intermediate result first, then use it in the outer query.

Here are the key takeaways:

• Scalar subqueries return a single value and work with comparison operators like =, >, <. Aggregate functions (AVG, MAX, MIN, SUM, COUNT) are natural fits for scalar subqueries.
• Multi-value subqueries return multiple rows and require set operators like IN, NOT IN, ANY, or ALL.
• Subqueries in WHERE are the most common and most important pattern to master first.
• Subqueries can also appear in SELECT, FROM, and HAVING clauses for more advanced use cases.
• Watch out for common traps: using = with a multi-row subquery, NOT IN with NULL values, and multi-column subqueries where a single column is expected.
• When subqueries become deeply nested, consider rewriting them as CTEs for better readability.
• Modern database optimizers often execute subqueries and equivalent joins with similar performance, so choose the approach that makes your intent clearest.

Once you are comfortable with subqueries, you will find that many questions that previously seemed impossible to answer in SQL become surprisingly straightforward.