Subqueries

A subquery is a query inside another query. It's like asking a question within a question.

Why Subqueries?

Sometimes you need to use the result of one query as part of another:

• "Find products priced above the average" (but what IS the average?)
• "Find customers who ordered the most expensive product"

Example: Find above-average priced products

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

How it works:

• The inner query (SELECT AVG(price) FROM products) runs first and returns a number (e.g., 45.5)
• The outer query then uses that number: WHERE price > 45.5

Types of Subqueries

Type	Returns	Used with
Scalar	One value	<code>=</code>, <code>&gt;</code>, <code>&lt;</code>
List	Multiple values	<code>IN</code>, <code>NOT IN</code>
Table	Multiple rows & columns	<code>FROM</code>

⚠️ Common Mistake

Don't forget the parentheses around subqueries!

Your Task

Find all products with a price higher than the average price.