Stop 03 · SQL for Data AnalysisTopics · 5/10

Subqueries

In short

"Which orders are above the average order?" needs the average first, then the filter. A subquery lets you do both in one query, like asking a question inside a question.

A query nested inside another query, used when you need an intermediate result before the final calculation — for example filtering against a computed average.

The jaadu opens a big gift box to find smaller boxes nested inside.

Think of it like this

A gift box inside a gift box

You open a big box and find a small box inside. You must open the small one first to get what's needed for the big one. A subquery is the inner box: the database runs it first and hands its answer to the outer query.

Watch it happen

Orders above the average

Outer query

Subquery

orders

need the average

1/5The outer query can't filter until it knows the average amount.

The big ideas

What you need to know

Scalar subquery

Returns one value, like an average, and can sit wherever a number would go.

IN subquery

WHERE customer_id IN (SELECT ...) filters against a list returned by the inner query.

Subquery in FROM

A subquery can act as a temporary table that the outer query selects from. It needs an alias.

Correlated subquery

Refers to the outer row, so it runs once per row. Powerful but can be slow on big tables.

See it in code

Walk through the code

sql

1SELECT order_id, customer_id, amount2FROM orders3WHERE amount > (SELECT AVG(amount) FROM orders);4 5SELECT name6FROM customers7WHERE customer_id IN (8  SELECT customer_id FROM orders WHERE amount > 50009);

1/2The bracketed query returns the average. The outer WHERE compares every order to it.

Two common shapes: compare against one computed value, and filter against a list.

Save yourself the pain

Common mistakes

Oops

Using = with a subquery that can return several rows.

Do this instead

Use IN for lists. = only works when the subquery returns exactly one value.

Oops

Nesting four subqueries deep until nobody can read it.

Do this instead

Once it gets deep, switch to CTEs (WITH) so each step has a name.

Your mission

Questions inside questions

~25 min

Quick check

What does WHERE amount > (SELECT AVG(amount) FROM orders) do?

Go deeper

1Write a query filtering rows above the average value using a subquery.
2Practice a subquery in the WHERE clause versus one in the FROM clause.
3Learn when a subquery is clearer than a JOIN, and when it isn't.