SQL IN

-- List of literals
SELECT id, email FROM users
WHERE country IN ('AU', 'NZ', 'US');

-- Equivalent OR form
SELECT id, email FROM users
WHERE country = 'AU' OR country = 'NZ' OR country = 'US';

-- IN with a subquery
SELECT id, email FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE status = 'paid'
);

-- IN gotcha with NULL
SELECT id FROM users WHERE country IN ('AU', NULL);
-- Returns rows where country = 'AU' - the NULL is silently ignored

-- NOT IN gotcha with NULL
SELECT id FROM users WHERE country NOT IN ('AU', NULL);
-- Returns ZERO rows - 'country <> NULL' is unknown
-- Always use NOT EXISTS instead:
SELECT u.id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM banned_countries b WHERE b.code = u.country
);

-- IN with tuples (Postgres)
SELECT id FROM orders
WHERE (user_id, status) IN (
  (1, 'paid'),
  (2, 'shipped'),
  (3, 'paid')
);

-- IN vs EXISTS - usually equivalent
-- Use EXISTS for very large subquery result sets:
SELECT u.id FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Performance
-- IN with a small literal list is fast (compiles to OR)
-- IN with a large subquery may benefit from being rewritten as JOIN:
SELECT DISTINCT u.id, u.email
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

-- ANY and SOME are SQL-standard synonyms for IN with a subquery
SELECT id FROM users WHERE country = ANY (SELECT code FROM allowed_countries);

-- ALL - matches if true for every value in the set
SELECT id FROM products WHERE price > ALL (SELECT price FROM old_products);

SELECT * FROM customers
WHERE country IN ('UK', 'US', 'AU');