Subqueries

-- In WHERE
SELECT * FROM products
WHERE category_id = (SELECT id FROM categories WHERE name = 'Electronics');

-- In FROM (derived table / inline view)
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) dept_avgs
WHERE avg_sal > 70000;

-- In SELECT (scalar subquery)
SELECT name,
       (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

-- In SELECT
SELECT name,
       salary,
       salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- In WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- In HAVING
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

-- In ORDER BY
SELECT name, revenue
FROM regions
ORDER BY (
    SELECT COUNT(*) FROM customers WHERE customers.region_id = regions.id
) DESC;

-- IN: matches any value in the subquery result set
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 500);

-- NOT IN: matches no value in the subquery result set
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);

-- DANGER: NOT IN with NULLs
-- If the subquery returns ANY NULL, NOT IN returns no rows at all
-- Use NOT EXISTS for safety:
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

-- EXISTS: rows that have a related record
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100
);

-- NOT EXISTS: rows with no related record
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- EXISTS is preferred over NOT IN when the subquery can contain NULLs
-- EXISTS / NOT EXISTS always returns TRUE or FALSE, never NULL

-- Find employees earning more than their department average
SELECT name, salary, department
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department   -- references outer e
);

-- Latest order per user (correlated)
SELECT u.name, (
    SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id
) AS last_order_date
FROM users u;

-- Find rows where a column equals the max in its group
SELECT * FROM products p
WHERE price = (
    SELECT MAX(price) FROM products WHERE category_id = p.category_id
);

-- Basic CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE country = 'US';

-- Multiple CTEs
WITH
revenue AS (
    SELECT user_id, SUM(total) AS total_rev FROM orders GROUP BY user_id
),
top_customers AS (
    SELECT user_id FROM revenue WHERE total_rev > 1000
)
SELECT u.name, r.total_rev
FROM users u
JOIN revenue       r ON r.user_id = u.id
JOIN top_customers t ON t.user_id = u.id;

-- CTE in UPDATE
WITH flagged AS (
    SELECT id FROM users WHERE last_login < NOW() - INTERVAL '1 year'
)
UPDATE users SET status = 'dormant'
WHERE id IN (SELECT id FROM flagged);

-- CTEs are not a performance silver bullet — the optimizer may or may not
-- inline them. PostgreSQL 12+ makes CTEs optimization fences by default only
-- if they are recursive or contain side-effects.

-- Walk an employee hierarchy (manager → reports)
WITH RECURSIVE org AS (
    -- Base case: top-level employees (no manager)
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees whose manager is already in org
    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e
    JOIN org o ON o.id = e.manager_id
)
SELECT * FROM org ORDER BY depth, name;

-- Generate a series of numbers (portable alternative to generate_series)
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n FROM nums;

-- Traverse a category tree
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, ARRAY[id] AS path
    FROM categories WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.path || c.id
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
    WHERE NOT c.id = ANY(ct.path)   -- cycle protection
)
SELECT * FROM category_tree;

-- Every subquery in FROM needs an alias
SELECT t.dept, t.avg_sal
FROM (
    SELECT department AS dept, AVG(salary) AS avg_sal
    FROM employees GROUP BY department
) t
WHERE t.avg_sal > 60000;

-- Multiple derived tables joined
SELECT a.dept, a.cnt, b.avg_sal
FROM (SELECT department AS dept, COUNT(*) AS cnt FROM employees GROUP BY department) a
JOIN (SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department) b
    ON a.dept = b.dept;

-- Use a CTE instead for readability
WITH counts AS (SELECT department, COUNT(*) AS cnt FROM employees GROUP BY department),
     avgs   AS (SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department)
SELECT c.department, c.cnt, a.avg_sal
FROM counts c JOIN avgs a USING (department);

-- ANY: condition holds for at least one value in subquery
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Budget');
-- Equivalent to: price > MIN(SELECT price ...)

-- ALL: condition holds for every value in subquery
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Budget');
-- Equivalent to: price > MAX(SELECT price ...)

-- ANY with = is equivalent to IN
WHERE id = ANY (SELECT user_id FROM orders)
-- same as:
WHERE id IN (SELECT user_id FROM orders)

-- INSERT ... SELECT
INSERT INTO archive_orders
SELECT * FROM orders WHERE created_at < '2023-01-01';

-- UPDATE with subquery
UPDATE products
SET price = price * 1.1
WHERE category_id IN (SELECT id FROM categories WHERE name = 'Premium');

-- UPDATE with correlated subquery
UPDATE employees e
SET salary = (SELECT AVG(salary) FROM employees WHERE department = e.department)
WHERE title = 'Intern';

-- DELETE with subquery
DELETE FROM sessions
WHERE user_id IN (SELECT id FROM users WHERE status = 'banned');

-- DELETE with NOT EXISTS
DELETE FROM cart_items ci
WHERE NOT EXISTS (
    SELECT 1 FROM products p WHERE p.id = ci.product_id AND p.active = TRUE
);

-- PostgreSQL: LATERAL
SELECT u.name, recent.order_id, recent.total
FROM users u
LEFT JOIN LATERAL (
    SELECT id AS order_id, total
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent ON TRUE;

-- SQL Server: CROSS APPLY / OUTER APPLY
SELECT u.name, recent.order_id
FROM users u
OUTER APPLY (
    SELECT TOP 3 id AS order_id, total
    FROM orders
    WHERE user_id = u.id
    ORDER BY created_at DESC
) recent;

-- MySQL 8+: LATERAL
SELECT p.name, top_sales.*
FROM products p,
LATERAL (
    SELECT SUM(qty) AS units_sold
    FROM order_items
    WHERE product_id = p.id
) top_sales;