**Performance Analysis:**

1. Scalar Subquery (Non-correlated):

- Execution: 1 time

- Cost: Very low

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

- Best for: Company-wide aggregates

2. Correlated Subquery:

- Execution: N times (once per row)

- Cost: Can be very high

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

- Critical: Needs index on filter columns

- Without index: Full table scan × N rows

- With index: Index seek × N rows

3. Multiple Correlated Subqueries:

- If you have 3 correlated subqueries and 1000 rows

- Total executions: 3 × 1000 = 3000

- Each needs proper index

**Optimization Strategies:**

1. Add indexes on subquery filter columns:

CREATE INDEX idx_orders_customer ON orders(customer_id);

CREATE INDEX idx_employees_dept ON employees(department, salary);

2. Use covering indexes to avoid table lookups:

CREATE INDEX idx_orders_customer_total ON orders(customer_id, total, order_date);

3. Consider JOIN instead of correlated subquery:

-- Slow: Correlated subquery

SELECT c.name,

(SELECT COUNT(*) FROM orders WHERE customer_id = c.id)

FROM customers c;

-- Faster: JOIN with GROUP BY

SELECT c.name, COUNT(o.id)

FROM customers c

LEFT JOIN orders o ON o.customer_id = c.id

GROUP BY c.id, c.name;

4. Use window functions when available:

-- Slow: Correlated subquery

SELECT e.name,

(SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e.dept)

FROM employees e;

-- Fast: Window function

SELECT name,