Subqueries: When and Why to Use Them

Welcome to **Phase 4: Subqueries & CTEs**! Today is **Day 46**, and we're learning to think in layers.

What is a Subquery?

A subquery is just a `SELECT` statement inside another `SELECT`, `UPDATE`, or `DELETE` statement. It's like a formula inside a formula in Excel.

Imagine you want to find all employees who earn more than the **company average**. You don't know the average yet!

SELECT name, salary

FROM employees

WHERE salary > (

SELECT AVG(salary) FROM employees

);

The database runs the inner query first (finds the average, say $50k) and then runs the outer query (`WHERE salary > 50000`).

**In the WHERE clause**: Like the example above (most common).

**In the FROM clause**: To treat a query result like a temporary table.

**In the SELECT clause**: To add a single value to every row (though this is often slow).

If your subquery is in the `WHERE` clause with a `>` or `=`, it **must** return exactly one value. If it returns a list, you must use `IN`.

Write a subquery to find all products whose price is higher than the average price in your store.

*Day 47: Correlated Subqueries (Simple Explanation).*