Subqueries
Nest one query inside another for multi-step logic
Intermediate 16 minsubquerynested
Queries Inside Queries
A subquery is a complete SELECT statement placed inside another query. The inner query runs first, and its result feeds into the outer query. Here's where subqueries appear:
In WHERE (most common) — Filter based on a computed value:
WHERE price > (SELECT AVG(price) FROM products). The subquery computes the average price first, then the outer query uses that number to filter the products table.In FROM (derived table) — The subquery acts as a temporary table:
FROM (SELECT customer_id, SUM(total) AS spend FROM orders GROUP BY customer_id) AS sub. The alias is always required when using subqueries in FROM.In SELECT (scalar) — Compute a single value per row:
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count. This is a "correlated" subquery because it references the outer table.Single vs. multiple rows — Scalar subqueries (returning one value) work with
=, >, < operators. Multi-row subqueries need IN, EXISTS, ANY, or ALL to handle the multiple values returned.Knowledge Canvas
How Subqueries Work
Nest one query inside another
- A subquery is a SELECT inside another SQL statement
- Can appear in WHERE, FROM, SELECT, or HAVING
- Scalar subquery returns one value; table subquery returns rows
- Correlated subqueries reference the outer query — run per row
Execution Flow
How the database processes nested queries
1
Uncorrelated: inner query runs ONCE, result is reused2
Correlated: inner query runs for EACH outer row3
FROM subquery: creates a derived table (inline view)4
Scalar in SELECT: computes one value per output rowSubquery vs JOIN vs CTE
Three ways to combine data
Subquery: nested, compact
JOIN: flat, performant
CTE: named, readable
Subquery: anonymous, inline
Correlated: per-row execution
JOIN: set-based execution
Use subquery for simple filters
Use JOIN for combining columns
Subquery Pitfalls
Where things go wrong
- Scalar subquery returning multiple rows → error
- Correlated subqueries can be extremely slow
- Deep nesting (3+ levels) → unreadable — use CTEs
= (subquery)errors if subquery returns more than one row;IN (subquery)accepts any number of rows
Performance Notes
Subquery optimization
- Uncorrelated subqueries are optimized well by most engines
- Correlated subqueries: consider rewriting as JOIN
- EXISTS is often faster than IN for large subqueries
- Derived tables themselves aren't indexed, but the base tables inside the subquery still use their indexes
Syntax
Syntax Template
1-- Scalar subquery in WHERE — returns one value
2SELECT *
3FROM products
4WHERE price > (
5 SELECT AVG(price)
6 FROM products
7);
8
9-- List subquery with IN — returns many values
10SELECT *
11FROM customers
12WHERE id IN (
13 SELECT customer_id
14 FROM orders
15 WHERE total > 100
16);
17
18-- Derived table in FROM — must have an alias
19SELECT sub.customer_id, sub.spend
20FROM (
21 SELECT
22 customer_id,
23 SUM(total) AS spend
24 FROM orders
25 GROUP BY customer_id
26) AS sub;
Scalar subqueryReturns one value — use with =, >, <List subqueryReturns multiple values — use with IN, EXISTSDerived tableSubquery in FROM, must have an alias Sample Data
products
10 rows
| id | name | price |
|---|---|---|
| P101 | Wireless Mouse | 24.99 |
| P102 | Office Desk | 189 |
| P103 | LED Desk Lamp | 45.5 |
| P104 | USB-C Cable | 12.99 |
| P105 | Bluetooth Speaker | 79 |
| P106 | Office Chair | 95 |
| P107 | Coffee Maker | 89.99 |
| P108 | Notebook Set | 8.5 |
| P109 | Wireless Headphones | 159 |
| P110 | Standing Desk | 425 |
customers
12 rows
| id | name | city | country | |
|---|---|---|---|---|
| C1001 | Aarav Sharma | [email protected] | Mumbai | India |
| C1002 | Sara Chen | [email protected] | Singapore | Singapore |
| C1003 | James Wilson | [email protected] | London | UK |
| C1004 | Maria Garcia | [email protected] | Madrid | Spain |
| C1005 | Yuki Tanaka | [email protected] | Tokyo | Japan |
| C1006 | Priya Patel | [email protected] | Delhi | India |
| C1007 | Alex Johnson | [email protected] | New York | USA |
| C1008 | Chen Wei | [email protected] | Shanghai | China |
| C1009 | Emma Brown | [email protected] | Sydney | Australia |
| C1010 | Omar Hassan | [email protected] | Dubai | UAE |
| C1011 | Lena Muller | [email protected] | Berlin | Germany |
| C1012 | Ravi Kumar | [email protected] | Bangalore | India |
orders
12 rows
| id | customer_id | order_date | status | total |
|---|---|---|---|---|
| 5001 | C1001 | 2024-01-15 | delivered | 59.98 |
| 5002 | C1002 | 2024-02-08 | delivered | 449 |
| 5003 | C1001 | 2024-02-20 | shipped | 12.5 |
| 5004 | C1003 | 2024-03-05 | delivered | 89.99 |
| 5005 | C1006 | 2024-03-15 | pending | 245 |
| 5006 | C1005 | 2024-03-22 | delivered | 159 |
| 5007 | C1004 | 2024-04-02 | shipped | 45.5 |
| 5008 | C1009 | 2024-04-12 | cancelled | 79 |
| 5009 | C1012 | 2024-04-22 | delivered | 425 |
| 5010 | C1007 | 2024-05-05 | pending | 18.99 |
| 5011 | C1008 | 2024-05-18 | delivered | 134 |
| 5012 | C1010 | 2024-06-08 | cancelled | 65.5 |
Worked Example
Find products that cost more than the average price.
SQL
1SELECT
2 name,
3 price
4FROM products
5WHERE price > (SELECT AVG(price) FROM products)
6ORDER BY
7 price DESC;
The inner query computes the average price (~105). The outer query then filters to products above that threshold. The subquery runs once, returns a single number, and the outer query uses it as a comparison value.
Output
3 rows
| name | price |
|---|---|
| Standing Desk | 425 |
| Office Desk | 189 |
| Wireless Headphones | 159 |
Common Mistakes
❌
Subquery returns multiple rows with =
SQL
1SELECT
2 name,
3 price
4FROM products
5WHERE price = (SELECT price FROM products WHERE category = 'Electronics');
The subquery returns multiple prices (one per Electronics product) but = expects exactly one value. Run it — SQLite raises a 'sub-select returns N columns - expected 1' or returns wrong rows depending on the row order.
Use IN instead of = when the subquery can return multiple rows.
Key Concepts
1Scalar subquery (1 value) → use with =, >, <
2List subquery (multiple rows) → use with IN, EXISTS, ANY
3Derived tables (FROM subquery) must always have an alias
4Inner query runs first — its result feeds the outer query
Pro Tip
Many questions need two-step reasoning: compute something first, then use it. Subqueries express this naturally.
When to Use
Finding above-average items, filtering by computed thresholds, inline summaries.
Find all customers who have placed at least one order over 200. Show their name. Use a subquery with IN.
Your Query