Introduction to Subqueries

The Nested Problem

The head of sales asked: *"Show me all orders that are greater than the average order amount."*

I couldn't just put `> AVG(order_amount)` in the WHERE clause—SQL doesn't let you use aggregate functions there. I needed a two-step calculation: first find the average, then filter by it.

The Quest: The Subquery

A **Subquery** (or **Inner Query**) is a query nested inside another query. It runs first, produces a result, and the outer query uses that result.

The Implementation: WHERE with Subquery

SELECT *

FROM orders

WHERE order_amount > (SELECT AVG(order_amount) FROM orders);

How It Works

1. The inner query runs first: `SELECT AVG(order_amount) FROM orders` → returns `150`.

2. The outer query uses that value: `WHERE order_amount > 150`.

Types of Subqueries

**Scalar Subquery**: Returns a single value (used with `=`, `>`, `<`).

**Row Subquery**: Returns a single row (used with `IN`).

**Table Subquery**: Returns a whole table (used in `FROM` clause).

The "Oops" Moment

I wrote a subquery that returned multiple values and used it with `=`. SQL threw an error: "Subquery returned more than one row."

**Pro Tip**: If your subquery might return multiple rows, use `IN` or `ANY`, not `=`.

The Victory

The sales head saw only the high-value orders in one clean list. Subqueries are the building blocks for incredibly sophisticated analysis.

Your Task for Today

Write a subquery to find all products whose price is above the average product price.

*Day 47: Correlated Subqueries.*