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

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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:

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 .

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.

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

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.

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

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.

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

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

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.

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.

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.

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary