Subqueries

Reviewed & published by Brayan K

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?".

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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

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.

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.

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:

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.

-- The table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
CREATE TABLE products (
    id           INTEGER PRIMARY KEY,
    product_name TEXT,
    category     TEXT,
    price        REAL,
    stock        INTEGER
);

INSERT INTO products (id, product_name, category, price, stock) VALUES
    (1, 'Wireless Mouse',      'Electronics', 24.99, 120),
    (2, 'Coffee Mug',          'Kitchen',      9.50, 300),
    (3, 'Mechanical Keyboard', 'Electronics', 79.00,  45),
    (4, 'Notebook',            'Stationery',   3.25, 500),
    (5, 'Desk Lamp',           'Home',        32.00,  80),
    (6, 'USB-C Cable',         'Electronics', 12.99, 200);

-- 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.

-- ✅ Expected result:
-- 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 — 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.

-- 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.

-- ✅ Expected result:
-- product_name | price | avg_price | diff
-- Wireless Mouse | 24.99 | 26.955 | -1.9649999999999999
-- Coffee Mug | 9.5 | 26.955 | -17.455
-- Mechanical Keyboard | 79 | 26.955 | 52.045
-- Notebook | 3.25 | 26.955 | -23.705
-- Desk Lamp | 32 | 26.955 | 5.045000000000002
-- USB-C Cable | 12.99 | 26.955 | -13.964999999999998

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.

-- 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
);

-- ✅ Expected result:
-- 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 — 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 ,  Notebook | Stationery

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.

-- 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."

-- ✅ Expected result:
-- 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.

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
-- 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

Practice quiz

Continue this course

• Previous: The HAVING Clause
• Next: INSERT, UPDATE, DELETE — Add, change, and remove data in your database tables
• Quick reference: SQL cheat sheet › Subqueries & CTEs

Related lessons

• GROUP BY & HAVING