MySQL におけるサブクエリと JOIN の比較

-- Runs once: find all orders over the average amount
SELECT id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Runs once per customer: find customers who have at least one order over $500
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 500
);

-- IN subquery
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 500);

-- EXISTS
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.amount > 500
);

-- JOIN (with DISTINCT to avoid duplicates)
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 500;

-- This returns zero rows if ANY customer_id in orders is NULL
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe: returns customers with no orders, even if some order rows have NULL customer_id
SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_stats
WHERE avg_salary > 80000;