What Makes a Subquery Correlated?

-- Regular subquery: executes ONCE, result is static
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery: executes for EACH ROW, result changes
SELECT *
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department  -- references outer query
);

The key difference is the e1.department reference inside the subquery — this binds the inner query to each row of the outer query.

Basic Correlated Subquery Syntax

-- For each employee, find employees in the same department
SELECT
    e1.first_name,
    e1.last_name,
    e1.department
FROM employees e1
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.department = e1.department
      AND e2.employee_id != e1.employee_id
);

-- For each product, find the most recent order
SELECT
    p.product_name,
    p.category,
    (
        SELECT MAX(order_date)
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        WHERE oi.product_id = p.product_id
    ) AS last_ordered
FROM products p;

Correlated Subqueries in WHERE

-- Find employees who earn more than their department average
SELECT
    first_name,
    last_name,
    department,
    salary
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Find orders where the amount exceeds the customer's average
SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    o.order_date
FROM orders o
WHERE o.amount > (
    SELECT AVG(amount)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

-- Find products that have never been ordered
SELECT
    p.product_name,
    p.category
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

Filtering Patterns Comparison

Correlated Subqueries in SELECT

-- Show each employee's salary alongside their department average
SELECT
    first_name,
    last_name,
    salary,
    department,
    (
        SELECT ROUND(AVG(salary), 2)
        FROM employees e2
        WHERE e2.department = e1.department
    ) AS dept_avg_salary,
    salary - (
        SELECT AVG(salary)
        FROM employees e2
        WHERE e2.department = e1.department
    ) AS diff_from_avg
FROM employees e1;

-- Show each customer's order count and total spending
SELECT
    c.customer_name,
    c.city,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
    (SELECT COALESCE(SUM(amount), 0) FROM orders o WHERE o.customer_id = c.customer_id) AS total_spent
FROM customers c
ORDER BY total_spent DESC;

Correlated Subqueries in UPDATE

-- Update each employee's bonus based on department performance
UPDATE employees e1
SET bonus = (
    SELECT CASE
        WHEN e1.salary > AVG(e2.salary) * 1.2 THEN 5000
        WHEN e1.salary > AVG(e2.salary) THEN 3000
        ELSE 1000
    END
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Update customer tier based on their order history
UPDATE customers c
SET tier = (
    SELECT CASE
        WHEN COUNT(*) >= 50 THEN 'Platinum'
        WHEN COUNT(*) >= 20 THEN 'Gold'
        WHEN COUNT(*) >= 5 THEN 'Silver'
        ELSE 'Bronze'
    END
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Correlated Subqueries in DELETE

-- Delete employees who earn less than their department average
DELETE FROM employees e1
WHERE salary < (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

-- Delete inactive customers (no orders in the last year)
DELETE FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.order_date >= CURRENT_DATE - INTERVAL '1' YEAR
);

EXISTS vs NOT EXISTS Patterns

-- Find departments that have at least one employee earning over 100k
SELECT
    d.dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department = d.dept_name
      AND e.salary > 100000
);

-- Find customers who have never left a review
SELECT
    c.customer_name,
    c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM reviews r
    WHERE r.customer_id = c.customer_id
);

-- Find products that appear in every order (universal quantification)
-- "Find products where there is no order that does NOT contain this product"
SELECT
    p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE NOT EXISTS (
        SELECT 1
        FROM order_items oi
        WHERE oi.order_id = o.order_id
          AND oi.product_id = p.product_id
    )
);

Performance Considerations

Correlated Subquery vs JOIN vs Window Function

-- Correlated subquery: runs per row
SELECT
    first_name,
    salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS dept_avg
FROM employees e1;

-- JOIN alternative: often faster
SELECT
    e.first_name,
    e.salary,
    d.dept_avg
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department
) d ON e.department = d.department;

-- Window function: usually fastest
SELECT
    first_name,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Performance Comparison Table

Practical Examples

Rank Within Group

-- Assign rank to employees within each department by salary
SELECT
    first_name,
    last_name,
    department,
    salary,
    (SELECT COUNT(*)
     FROM employees e2
     WHERE e2.department = e1.department
       AND e2.salary >= e1.salary
    ) AS salary_rank
FROM employees e1
ORDER BY department, salary_rank;

First and Last Per Group

-- Find each customer's first and most recent order
SELECT
    c.customer_name,
    (SELECT order_date FROM orders o
     WHERE o.customer_id = c.customer_id
     ORDER BY order_date ASC LIMIT 1) AS first_order,
    (SELECT order_date FROM orders o
     WHERE o.customer_id = c.customer_id
     ORDER BY order_date DESC LIMIT 1) AS last_order
FROM customers c;

Conditional Aggregation

-- Show each employee with a flag if they are the top earner in their dept
SELECT
    first_name,
    last_name,
    department,
    salary,
    CASE
        WHEN salary = (
            SELECT MAX(salary)
            FROM employees e2
            WHERE e2.department = e1.department
        ) THEN 'Top Earner'
        ELSE 'Standard'
    END AS status
FROM employees e1;

Summary