SQL Subqueries: Nested Queries in WHERE, FROM and SELECT

SQL Tutorial · Chapter 28 of 32

A SQL subquery (also called a nested query or inner query) is a SELECT statement written inside another SQL statement. The inner query runs first and hands its result to the outer query: as a single value to compare against, as a list for IN, or as a temporary table in FROM. Subqueries let you answer two-step questions such as “which orders are above the average amount?” in one statement instead of two.

This guide is for analysts, students and Excel users moving into SQL. You will see the syntax for subqueries in WHERE, FROM and SELECT, five worked examples on the same two sample tables (with the exact result of each), correlated subqueries, the NOT IN trap with NULLs, and when a JOIN or CTE is the better choice. Every query runs in MySQL, SQL Server, PostgreSQL and SQLite unless noted.

SQL Subquery Syntax

-- in WHERE (scalar or list)
SELECT columns FROM table1
WHERE column OPERATOR (SELECT column FROM table2 WHERE condition);

-- in FROM (derived table, needs an alias)
SELECT t.column FROM (SELECT ... FROM table2 GROUP BY ...) AS t;

-- in SELECT (scalar, one value per row)
SELECT column, (SELECT AGG(x) FROM table2 WHERE ...) AS alias FROM table1;

Types of Subqueries at a Glance

Sample Tables Used in This Guide

Note that order 105 belongs to CustomerID 6, who is not in the Customers table, and Emma Brown has no orders. Those two details make the examples more realistic.

Example 1: Scalar Subquery in WHERE

The inner query returns one value, the average order amount (2800 / 6 = 466.67), and the outer query compares every row against it.

SELECT OrderID, Product, Amount
FROM Orders
WHERE Amount > (SELECT AVG(Amount) FROM Orders);

You cannot write WHERE Amount > AVG(Amount) directly, because aggregates are not allowed in WHERE. The subquery is the clean way around that rule.

Example 2: Multi-Row Subquery with IN

SELECT Name, Country
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Amount > 100);

The inner query returns the list (1, 1, 4); the outer query keeps customers whose ID is in that list. Duplicates in the list do not create duplicate output rows, which is one advantage over a JOIN here. The SQL IN operator guide covers lists in more depth.

Example 3: Subquery in FROM (Derived Table)

First total each customer’s spend, then filter and sort those totals. The derived table gets an alias (t here).

SELECT t.CustomerID, t.Total
FROM (SELECT CustomerID, SUM(Amount) AS Total
      FROM Orders
      GROUP BY CustomerID) AS t
WHERE t.Total > 50
ORDER BY t.Total DESC;

For this particular filter you could also use HAVING after GROUP BY. Derived tables become essential when you need a second level of aggregation, such as “the average of each customer’s total”.

Example 4: Scalar Subquery in SELECT

SELECT Name,
       (SELECT COUNT(*) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS Orders
FROM Customers c
ORDER BY Orders DESC, Name;

This is a correlated subquery: it references c.CustomerID from the outer row, so it is evaluated once per customer. Emma Brown still appears with 0, because COUNT(*) on an empty set returns 0 rather than NULL.

Example 5: Correlated Subquery in WHERE

Find each customer’s largest order: keep an order only if its amount equals the maximum for that same customer.

SELECT o.CustomerID, o.OrderID, o.Amount
FROM Orders o
WHERE o.Amount = (SELECT MAX(o2.Amount) FROM Orders o2 WHERE o2.CustomerID = o.CustomerID)
ORDER BY o.CustomerID;

If a customer had two orders tied for the maximum, both would be returned. The same “top row per group” task is often written with ROW_NUMBER(), covered in SQL window functions.

Practical Variations

Find rows with no match: NOT IN vs NOT EXISTS

Customers who have never ordered:

SELECT Name FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);
-- Emma Brown

WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders) gives the same answer on this data, but if the subquery ever returns a NULL, NOT IN returns no rows at all. NOT EXISTS does not have that trap, so prefer it for “missing” checks. See SQL EXISTS and SQL NULL values for why.

Find orphan records

Orders whose customer does not exist (a common data-quality check):

SELECT OrderID, CustomerID FROM Orders
WHERE CustomerID NOT IN (SELECT CustomerID FROM Customers);
-- 105, customer 6

Compare with ANY or ALL

-- orders bigger than every order placed by customer 1
SELECT OrderID, Amount FROM Orders
WHERE Amount > ALL (SELECT Amount FROM Orders WHERE CustomerID = 1);
-- no rows: nothing beats 1200

ANY and ALL work in MySQL, SQL Server and PostgreSQL. SQLite does not support them; use > (SELECT MAX(...)) instead.

Rewrite a derived table as a CTE

A Common Table Expression gives the subquery a name at the top, which is easier to read and reuse:

WITH CustomerTotals AS (
    SELECT CustomerID, SUM(Amount) AS Total
    FROM Orders
    GROUP BY CustomerID
)
SELECT CustomerID, Total
FROM CustomerTotals
WHERE Total > 50
ORDER BY Total DESC;

Subquery vs JOIN: Which Should You Use?

Many subqueries can be rewritten as joins, and the optimizer often produces the same plan. Use a JOIN when you need columns from both tables in the output; use a subquery when you only need the second table to filter or to supply a single computed value. Correlated subqueries in the SELECT list can be slow on large tables; a JOIN with GROUP BY or a window function usually scales better.

Database Differences: MySQL, SQL Server, PostgreSQL, SQLite

• Subqueries in WHERE, FROM and SELECT work in all four. MySQL and SQL Server require an alias on every derived table; SQLite and PostgreSQL 16 or later accept one without an alias, but adding it keeps your SQL portable.
• MySQL cannot reference the table being modified in a subquery inside UPDATE or DELETE (“You can’t specify target table”); wrap it in another derived table.
• All four support Common Table Expressions (WITH t AS (...)) as a more readable alternative to FROM subqueries.

Common Errors and How to Fix Them

Tips for Writing Subqueries

• Run the inner query on its own first and check its result before wrapping it.
• Alias every table in correlated subqueries (o, o2, c) so it is obvious which column belongs to which level.
• Index the columns used to connect a correlated subquery to the outer query, such as Orders.CustomerID.
• Once you nest more than two levels deep, switch to CTEs for readability.

Practice Exercise

1. List orders whose Amount is below the average Amount.
2. List the names of customers who have ordered a Laptop.

-- 1
SELECT OrderID, Amount FROM Orders WHERE Amount < (SELECT AVG(Amount) FROM Orders);
-- 102, 103, 104, 105

-- 2
SELECT Name FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Product = 'Laptop');
-- Anita Sharma, Wei Chen

For more practice, try the SQL quiz with 75 questions or keep the SQL reference open while you work.

If your data lives in Excel and you want to send it to a SQL database without writing queries by hand, the Excel VBA based Application with SQL database on NextGenTemplates gives you Excel forms to add, update and delete records stored in a SQL database.

To practise the same filtering and calculation ideas on the spreadsheet side, the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy comes with practice files.

Frequently Asked Questions

What is a subquery in SQL?

A SELECT statement placed inside another SQL statement. The inner query’s result is used by the outer query as a single value, a list or a temporary table.

What is a correlated subquery?

A subquery that references a column from the outer query, so it is re-evaluated for each outer row. It is powerful for per-row comparisons but can be slow on large tables without an index.

Are subqueries slower than joins?

Not necessarily. Modern optimizers often rewrite subqueries as joins internally. Correlated subqueries in the SELECT list are the ones most likely to be slow.

Can a subquery return more than one column?

Yes, in FROM (a derived table) or with EXISTS. With =, > or IN the subquery must return exactly one column.

What is the difference between a subquery and a CTE?

A CTE is a named subquery defined with WITH at the start of the statement. It produces the same result as a derived table but is easier to read, and it can be referenced more than once in the same query.

How many levels can subqueries be nested?

SQL Server allows up to 32 levels and other databases allow many more, but anything past two or three levels is hard to maintain. Use CTEs instead.

Wrapping Up

Subqueries let one query use the answer of another: a scalar value in WHERE, a list for IN, a derived table in FROM, or a per-row value in SELECT. Start with Example 1 and Example 2 on your own data, use NOT EXISTS for “missing” checks, and move to a JOIN, CTE or window function when the query grows. Next, read SQL EXISTS to master the most common correlated subquery.

📅 Last updated: October 2026

Chapter 28 of 32 · SQL from Scratch: all 32 chapters