Subqueries in SELECT clause add calculated columns to your result set. Think of them as "extra columns" that come from running mini-queries for each row. You can add order count, total spent, department average, or any calculated value as a new column.

Two types: Scalar subqueries return the same value for all rows (like company average salary). Correlated subqueries return different values per row (like each employee's department average). The key difference is performance: scalar executes once, correlated executes once per row.

Why use them? They let you add context without complex JOINs. Want to show each customer with their order count? Add (SELECT COUNT(*) FROM orders WHERE customer_id = c.id). Want employee salary vs department average? Add (SELECT AVG(salary) WHERE dept = e.dept). Clean and readable.

**How SELECT Subqueries Work:**

When you put a subquery in SELECT, it becomes a calculated column. For each row in the result:

1. Database processes main query

2. For each row, evaluates subquery

3. Subquery result becomes column value

4. Row is returned with all columns including subquery result

Scalar subquery (non-correlated):

- Doesn't reference outer query

- Executes ONCE before main query

- Result cached and reused for all rows

- Very efficient

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

Correlated subquery:

- References columns from outer query

- Executes ONCE PER ROW

- Can't be cached (different for each row)

- Can be slow without indexes

- Example: (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e1.dept)

**Common Use Cases:**

1. Count related records:

- Order count per customer

- Product count per category

- Employee count per department

2. Sum related values:

- Total spent per customer

- Total sales per product

- Total salary per department

3. Calculate per-group aggregates:

- Department average salary

- Category average price

- Region average sales

4. Lookup related values:

- Manager name for employee

- Category name for product

- Latest order date for customer

5. Conditional calculations:

- Count orders above threshold