Lesson 9 • Intermediate Track

Subqueries

By the end of this lesson you'll be able to put one query inside another — calculating a value (like the average price) and feeding it straight into a bigger query. Subqueries let you answer questions a single flat query simply can't, such as "which products cost more than average?".

What You'll Learn

✓What a subquery is and how it runs inside-out
✓Scalar subqueries that return one value in WHERE
✓Putting a scalar subquery in the SELECT list
✓Matching a list of values with IN (...)
✓Correlated subqueries and the EXISTS test
✓Avoiding the classic single-row and NULL traps

Before you start: finish GROUP BY & HAVING so you're comfortable with aggregate functions like AVG, MAX, and COUNT — subqueries lean on them constantly. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like sqliteonline.com or db-fiddle.com. Every example below shows the expected result so you can check yourself.

Our Sample Table: `products`

Every query in this lesson runs against this same products table — the one from the SELECT lesson. The average price of these 6 rows is 26.955 (≈ 26.96), so keep that number handy: most examples filter around it.

Result:

id	product_name	category	price	stock
1	Wireless Mouse	Electronics	24.99	120
2	Coffee Mug	Kitchen	9.5	300
3	Mechanical Keyboard	Electronics	79	45
4	Notebook	Stationery	3.25	500
5	Desk Lamp	Home	32	80
6	USB-C Cable	Electronics	12.99	200

1. What Is a Subquery?

A subquery is simply a query inside another query, wrapped in parentheses. The inner query runs first; its answer is handed to the outer query, which then does its job. People also call it a nested or inner query — same thing.

The reason subqueries exist is that some questions have two parts. You can't filter on "the average price" until you've worked out what the average price is. The subquery answers that first question so the outer query can ask the second.

💬 Real-world analogy

A subquery is like answering one question so you can ask the next. To find "everyone taller than the class average", you first measure the class and compute the average (the inner question), and only then can you point at the taller students (the outer question). SQL runs it in exactly that order — inside-out.

Subqueries are categorised by what they return. That shape decides where you can use them:

Type	Returns	Typically used in
Scalar	Exactly one value (one row, one column)	`SELECT`, `WHERE`
List / table	A column of values (many rows)	`IN (...)`
Correlated	Re-runs per outer row	`WHERE`, `EXISTS`

2. Scalar Subquery in WHERE

A scalar subquery returns a single value, so you can drop it anywhere a single value would go — including the right-hand side of a comparison like >, =, or <. This is the most common subquery you'll write.

Here we ask for every product priced above the average. The inner (SELECT AVG(price) FROM products) computes 26.955 first, then the outer query keeps the rows where price > 26.955.

Above-average products

Compare each row's price to a scalar subquery.

Try it Yourself »

SQL

-- A scalar subquery returns ONE value. Here it works out the
-- average price, and the outer query keeps only rows above it.
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- The inner query runs first:  AVG(price) = 26.955
-- Then the outer query keeps every product priced above 26.955.

Result — 2 rows:

product_name	price
Mechanical Keyboard	79
Desk Lamp	32

Only two products clear the 26.955 bar: the keyboard at 79.00 and the lamp at 32.00. The mouse (24.99) just misses it.

Your Turn: above-average products

Fill in the blank so the subquery computes the average price. The expected result is in the comments so you can check yourself.

🎯 Your Turn: scalar subquery

Replace the ___ blank with the averaging function.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — fill in the blank, then press "Try it Yourself"
-- Goal: list the products that cost MORE than the average price.

SELECT product_name, price
FROM products
WHERE price > (SELECT ___ FROM products);   -- 👉 the function for the average

-- ✅ Expected result (2 rows): products priced above ~26.96
--    Mechanical Keyboard | 79.00 ,  Desk Lamp | 32.00

3. Scalar Subquery in the SELECT List

Because a scalar subquery is just a value, you can also put it in the SELECT list as a column. The same value then appears on every row, which is handy for showing each product next to a benchmark — and even for calculating the gap.

Average as a column

Add the company average to every row, then compute the difference.

Try it Yourself »

SQL

-- A scalar subquery can also sit in the SELECT list, adding the
-- same single value to every row so you can compare against it.
SELECT
    product_name,
    price,
    (SELECT AVG(price) FROM products) AS avg_price,   -- 26.955 on every row
    price - (SELECT AVG(price) FROM products) AS diff -- how far above/below
FROM products;

-- avg_price is identical for all 6 rows; diff shows the gap from the mean.

Result — 6 rows (first 3 shown):

product_name	price	avg_price	diff
Wireless Mouse	24.99	26.955	-1.965
Coffee Mug	9.5	26.955	-17.455
Mechanical Keyboard	79	26.955	52.045

avg_price is the same 26.955 on every row; diff is positive only for products above the mean (the keyboard is +52.045).

4. Subquery with IN (...)

When the inner query returns several values rather than one, you can't use =. Use IN (...) instead — it checks whether a column's value appears anywhere in the list the subquery produces.

Below, the inner query finds the categories of any product priced over 50. Only the Mechanical Keyboard (79.00) qualifies, so the list is just 'Electronics'. The outer query then returns every Electronics product.

Pro tip: a subquery feeding IN must return exactly one column. Selecting two columns there is an error — IN compares against a single list, not a table.

IN with a subquery

Match a column against a list the subquery returns.

Try it Yourself »

SQL

-- IN checks membership against a LIST of values the subquery returns.
-- Step 1 (inner):  which categories contain a product over 50?
--                  only Mechanical Keyboard (79.00) qualifies -> 'Electronics'
-- Step 2 (outer):  return every product whose category is in that list.
SELECT product_name, category, price
FROM products
WHERE category IN (
    SELECT category FROM products WHERE price > 50
);

Result — 3 rows:

product_name	category	price
Wireless Mouse	Electronics	24.99
Mechanical Keyboard	Electronics	79
USB-C Cable	Electronics	12.99

All three Electronics products come back — even the cheap USB-C Cable — because they share the category that contained the over-50 product.

Your Turn: match a list with IN

Two blanks: add the membership keyword and the "less than" operator so the subquery lists categories that contain a product cheaper than 10.

🎯 Your Turn: IN subquery

Complete the IN (...) membership test.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — fill in the two blanks to use a subquery with IN.
-- Goal: list every product whose category also appears in 'Kitchen'
--       OR 'Home' — i.e. categories that contain a cheap product (price < 10).

SELECT product_name, category
FROM products
WHERE category ___ (                 -- 👉 the keyword that checks a list
    SELECT category FROM products WHERE price ___ 10   -- 👉 "less than"
);

-- ✅ Expected result (2 rows): the cheap-category products
--    Coffee Mug | Kitchen ,
...

5. Correlated Subqueries & EXISTS

The subqueries so far were independent — the inner query ran once, on its own, with a fixed answer. A correlated subquery is different: it references a column from the outer row, so it re-runs once for every outer row, giving a fresh answer each time.

EXISTS pairs naturally with correlated subqueries. It doesn't care what the inner query returns — only whether it returns any row at all. That's why you'll see SELECT 1 inside: the value is irrelevant, the existence is the point.

Pro tip: EXISTS can stop the moment it finds one matching row, so on large tables it's often faster than building a full list for IN. It also sidesteps the NOT IN + NULL trap covered below.

Correlated EXISTS

Keep products that aren't the cheapest in their category.

Try it Yourself »

SQL

-- A CORRELATED subquery references the outer row (alias p) and re-runs
-- once per row. EXISTS just asks "did the inner query find ANY row?".
SELECT p.product_name, p.category
FROM products p
WHERE EXISTS (
    SELECT 1               -- the value doesn't matter, only that a row exists
    FROM products cheaper
    WHERE cheaper.category = p.category   -- correlation: links inner to outer
      AND cheaper.price < p.price
);

-- Meaning: "keep products that are NOT the cheapest in their category
...

Result — 2 rows:

product_name	category
Wireless Mouse	Electronics
Mechanical Keyboard	Electronics

Only Electronics has more than one product, so only there can a product be "not the cheapest". USB-C Cable (12.99) is the cheapest Electronics item — nothing is cheaper, so EXISTS finds no row and it drops out. Kitchen, Stationery, and Home each hold a single product (automatically the cheapest of their group), so they drop out too. That leaves just Wireless Mouse and Mechanical Keyboard.

Common Errors (and the fix)

"subquery returned more than one row": you used = (or >) with a subquery that returned several rows. Either make it scalar (add an aggregate like MAX/AVG, or a LIMIT 1), or switch the operator to IN (...).
"sub-select returns N columns - expected 1": a subquery used with IN must select exactly one column. WHERE category IN (SELECT category, price ...) fails — drop the extra column.
NOT IN returns nothing: if the inner list contains a single NULL, NOT IN yields no rows at all. Use NOT EXISTS instead — it handles NULLs correctly.
Slow correlated subquery: because it re-runs once per outer row, a correlated subquery over a big table can crawl. If it's hurting, rewrite it as a JOIN or aggregate.
Missing parentheses: every subquery must be wrapped in ( ). WHERE price > SELECT AVG(price) FROM products is a syntax error — wrap the inner SELECT.

📘 Quick Reference

Pattern	Purpose
WHERE col > (SELECT AVG(col) ...)	Scalar: compare to one computed value
SELECT (SELECT MAX(col) ...) AS x	Scalar subquery as a column
WHERE col IN (SELECT col ...)	Match against a list of values
WHERE col NOT IN (SELECT ...)	Exclude a list (beware NULLs)
WHERE EXISTS (SELECT 1 ... WHERE x = outer.x)	Correlated: does any matching row exist?

Frequently Asked Questions

Q: Which runs first, the inner or the outer query?

For a normal (non-correlated) subquery, the inner one runs first and just once; its result is then used by the outer query. A correlated subquery is the exception — it re-runs for each outer row.

Q: When should I use a subquery instead of a JOIN?

Reach for a subquery when you need a single computed value (like an average) or a simple membership test. Prefer a JOIN when you actually need columns from both tables in the output, or when a correlated subquery is too slow.

Q: What's the difference between IN and EXISTS?

IN compares a value against a list the subquery builds; EXISTS just checks whether the (usually correlated) subquery returns any row. EXISTS is often faster on large data and is safe with NULLs.

Q: Can a subquery use the same table as the outer query?

Yes — that's exactly what the above-average example does. Give the tables different aliases (e.g. p and cheaper) so SQL can tell the inner and outer references apart.

Mini-Challenge: The Priciest Product

Put it together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm.

🎯 Mini-Challenge

Return the product with the maximum price using a scalar subquery.

Try it Yourself »

SQL

-- 🎯 MINI-CHALLENGE
-- Using a SCALAR subquery, return the product(s) with the highest price.
--   1. SELECT product_name and price FROM products
--   2. Keep only the row where price equals the maximum price
--   3. Get that maximum with an inner (SELECT MAX(price) FROM products)
--
-- ✅ Expected (1 row): Mechanical Keyboard | 79.00

-- your query here

🎉 Lesson Complete

✅ A subquery is a query inside a query; the inner one runs first
✅ A scalar subquery returns one value — use it in WHERE or the SELECT list
✅ IN (...) matches a column against a list a subquery returns (one column only)
✅ A correlated subquery re-runs per row; EXISTS tests whether any row matches
✅ Watch for the "more than one row", column-count, and NOT IN + NULL traps
✅ Next: change the data itself with INSERT, UPDATE, and DELETE

Subqueries

What You'll Learn

Our Sample Table: products

1. What Is a Subquery?

2. Scalar Subquery in WHERE

Above-average products

Your Turn: above-average products

🎯 Your Turn: scalar subquery

3. Scalar Subquery in the SELECT List

Average as a column

4. Subquery with IN (...)

IN with a subquery

Your Turn: match a list with IN

🎯 Your Turn: IN subquery

5. Correlated Subqueries & EXISTS

Correlated EXISTS

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: The Priciest Product

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `products`