상관 하위 쿼리와 비상관 하위 쿼리

SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE status = 'paid'
);

SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.total > 1000
);

-- IN with non-correlated subquery (often the planner's favourite):
SELECT u.* FROM users u
WHERE u.id IN (SELECT user_id FROM orders);

-- EXISTS with correlated subquery (NULL-safe alternative):
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- BUG-prone:
SELECT u.* FROM users u
WHERE u.id NOT IN (SELECT excluded_id FROM blocklist);

-- Safe:
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM blocklist b WHERE b.excluded_id = u.id);

SELECT u.id, u.email,
  (SELECT MAX(created_at) FROM orders o WHERE o.user_id = u.id) AS last_order_at
FROM users u;

-- Often readable, but consider a LEFT JOIN + GROUP BY.

UPDATE products p
SET review_count = (
  SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.id
);

-- For the orders example, this is essential:
CREATE INDEX orders_user_id_idx ON orders(user_id);