SQL Subqueries: Nested Queries in WHERE, FROM and SELECT

SQL Tutorial · Chapter 28 of 32

A SQL subquery is a SELECT statement nested inside another statement. The inner query runs first and its result feeds 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 a single statement.

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;

Sample Tables

Example 1: Scalar Subquery in WHERE

The inner query returns one value (the average, 466.67) and the outer query compares every row against it.

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

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.

Example 3: Subquery in FROM (Derived Table)

First total each customer’s spend, then filter and sort those totals. The derived table must have an alias.

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;

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.

Example 5: Correlated Subquery in WHERE

Find each customer’s largest order: keep an order only if no other order by the same customer is bigger.

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;

Subquery vs JOIN

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 SELECT can be slow on large tables; a JOIN with GROUP BY or a window function usually scales better.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• Subqueries in WHERE, FROM and SELECT work in all four. Derived tables need an alias everywhere except SQLite, which tolerates its absence.
• 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 Mistakes

• A scalar subquery returning more than one row: “subquery returns more than 1 row”. Use IN, or add LIMIT 1 / an aggregate.
• Forgetting the alias on a derived table.
• Selecting more than one column in a subquery used with IN or =.
• Using ORDER BY inside a subquery where it has no effect (except with LIMIT).

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

Related Chapters

• SQL EXISTS – the most efficient correlated subquery
• SQL IN – multi-row subqueries
• SQL Joins Introduction – the alternative to many subqueries
• SQL from Scratch: full course overview

FAQ

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 value, a list or a temporary table.

What is a correlated subquery?

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

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.

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