-- ============================================

-- BASIC CORRELATED SUBQUERY PATTERN

-- ============================================

-- Compare each employee to their department average

SELECT

e1.employee_id,

e1.name,

e1.department,

e1.salary,

(SELECT AVG(e2.salary) -- Correlated subquery

FROM employees e2

WHERE e2.department = e1.department) AS dept_avg -- References e1!

FROM employees e1

WHERE e1.salary > (

SELECT AVG(e2.salary)

FROM employees e2

WHERE e2.department = e1.department -- References e1 again!

);

-- Execution for 10,000 employees:

-- 1. Row 1 (Alice, Engineering): Calculate Engineering avg

-- 2. Row 2 (Bob, Engineering): Calculate Engineering avg AGAIN

-- 3. Row 3 (Carol, Sales): Calculate Sales avg

-- ... 10,000 total executions

-- Required index for performance:

CREATE INDEX idx_employees_dept_salary

ON employees(department, salary);

-- ============================================

-- CORRELATED EXISTS PATTERN

-- ============================================

-- Find customers who have placed high-value orders

SELECT

c.customer_id,

c.name,

c.email

FROM customers c

WHERE EXISTS (

SELECT 1

FROM orders o

WHERE o.customer_id = c.customer_id -- Correlated: references c

AND o.total > 1000