**Performance by Type:**

Scalar non-correlated subquery:

- Executes once

- Result cached

- Very fast

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

Scalar correlated subquery:

- Executes per outer row

- Can be slow with many rows

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

Multi-row subquery with IN:

- Executes once

- Fast for small result sets (<1000 values)

- Can be slow for large result sets

- Consider EXISTS for large datasets

Derived table (FROM subquery):

- Executes once

- Can improve performance by filtering early

- Consider CTEs for readability

**Optimization Tips:**

1. Prefer non-correlated over correlated

2. Use EXISTS instead of IN for large datasets

3. Index columns used in subqueries

4. Consider JOINs for better performance

5. Use CTEs for complex multi-step logic

6. Test subquery independently

7. Check execution plan with EXPLAIN

**When Subqueries Are Slow:**

- Correlated subqueries on large tables

- IN with large result sets (>10K values)

- Nested subqueries (subquery in subquery)

- Missing indexes on subquery columns

Solution: Rewrite as JOIN, use EXISTS, add indexes, or use CTEs

Non-correlated subqueries execute once (fast)

Correlated subqueries execute per outer row (can be slow)

EXISTS often faster than IN for large datasets

Scalar subqueries in SELECT are efficient if non-correlated

Derived tables can improve performance by filtering early

Index columns used in subquery filters

Consider JOINs when you need columns from both tables