A subquery is a query nested inside another query. Think of it as a helper query that provides data to the main query. The subquery is always in parentheses.

Simple example: "Find employees earning more than the average salary." You need the average first, then compare each employee to it. Subquery calculates the average, main query does the comparison.

Subqueries are powerful because they let you break complex problems into steps. Instead of one giant query, you can say "first get this, then use it to get that." This makes SQL more readable and flexible.

**Classification by Location (WHERE the subquery appears):**

1. **SELECT Clause Subquery**

- Adds a calculated column to each row

- Must return single value (scalar)

- Example: Add company average salary to each employee row

- Syntax: SELECT name, (SELECT AVG(salary) FROM employees) AS avg

2. **WHERE Clause Subquery**

- Filters rows based on subquery result

- Most common location

- Can return single value or list

- Example: WHERE salary > (SELECT AVG(salary) FROM employees)

- Example: WHERE dept_id IN (SELECT id FROM departments WHERE location = 'NYC')

3. **FROM Clause Subquery (Derived Table)**

- Creates temporary table for main query

- Must have alias

- Useful for pre-filtering or pre-aggregating

- Example: FROM (SELECT * FROM employees WHERE salary > 50000) AS high_earners

4. **HAVING Clause Subquery**

- Filters groups after aggregation

- Less common than WHERE

- Example: HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)

**Classification by Result Type (WHAT the subquery returns):**

1. **Scalar Subquery**

- Returns exactly one value (1 row, 1 column)

- Used with comparison operators: =, >, <, >=, <=, <>

- Example: (SELECT AVG(salary) FROM employees) returns 75000

- Error if returns multiple rows

2. **Single-Row Subquery**

- Returns one row with multiple columns

- Less common

- Example: (SELECT MIN(salary), MAX(salary) FROM employees)

3. **Multi-Row Subquery**

- Returns multiple rows, one column

- Used with IN, ANY, ALL, EXISTS

- Example: (SELECT id FROM departments WHERE location = 'NYC') returns (1, 3, 5)

4. **Table Subquery**

- Returns multiple rows and columns