EXISTS vs IN — Subquery Filtering

-- IN with a fixed list
SELECT * FROM orders WHERE status IN ('pending', 'shipped');

-- IN with a subquery
SELECT * FROM users
WHERE  id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- EXISTS — usually faster on large subqueries
SELECT * FROM users u
WHERE  EXISTS (
  SELECT 1 FROM orders o
  WHERE  o.user_id = u.id AND o.amount > 1000
);

-- ⚠️ NOT IN is BROKEN with NULL — if any subquery row is NULL, you get NO rows.
-- Use NOT EXISTS instead:
SELECT * FROM users u
WHERE  NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);   -- users with zero orders, NULL-safe