SQL Subqueries

-- Scalar subquery: returns exactly one value
SELECT
    employee_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- Subquery in WHERE with IN
SELECT product_name
FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE type = 'Electronics'
);

-- Subquery in FROM (derived table / inline view)
SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_averages
WHERE avg_salary > 60000;

-- Find employees earning more than their department's average
-- Correlated: inner query references e.department from outer query
SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department  -- references outer query!
);

-- This runs the subquery ONCE PER ROW of the outer employees table
-- On 100,000 employees: 100,000 AVG() computations — slow!

-- Better: use a JOIN with a derived table (computes AVG once per department)
SELECT e.name, e.salary, e.department
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department
) dept ON e.department = dept.department
WHERE e.salary > dept.dept_avg;

-- IN: collects all values from subquery, then checks membership
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- EXISTS: short-circuits — stops as soon as it finds one match
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders WHERE customer_id = c.id
);

-- Performance:
-- IN: subquery runs once, returns a set. Fast when subquery returns few rows.
-- EXISTS: correlated, but stops at first match. Fast when many matches expected.
-- For NOT: NOT EXISTS is almost always faster than NOT IN
-- NOT IN fails silently if subquery contains NULL — NOT EXISTS handles it correctly

-- Dangerous NULL behaviour with NOT IN:
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);
-- Without IS NOT NULL: returns empty set if ANY order has NULL customer_id