集合に対するINとNOT IN

IN：リストとの照合

SELECT * FROM users WHERE country IN ('US', 'CA', 'GB');

-- Equivalent to:
SELECT * FROM users
WHERE country = 'US' OR country = 'CA' OR country = 'GB';

サブクエリでのIN

SELECT * FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE country = 'US'
);

NOT IN：リストを除外する

SELECT * FROM users WHERE status NOT IN ('banned', 'deleted');

NOT INとNULLの落とし穴

-- Suppose blocked_names contains NULL:
SELECT name FROM users
WHERE name NOT IN (SELECT excluded_name FROM blocked_names);
-- → 0 rows, silently

-- Safer: filter out NULLs explicitly, or use NOT EXISTS:
SELECT name FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_names b WHERE b.excluded_name = u.name
);

NOT EXISTSがより安全な理由

INとOR：可読性

-- Hard to read:
WHERE country = 'US' OR country = 'CA' OR country = 'GB' OR ...

-- Clear:
WHERE country IN ('US', 'CA', 'GB', ...)

タプル（行コンストラクター）でのIN

SELECT * FROM order_items
WHERE (order_id, product_id) IN (
  (1, 42), (2, 99), (3, 17)
);

インデックスの使用

-- Plan: Bitmap Index Scan + Bitmap Heap Scan
SELECT * FROM products WHERE id IN (1, 5, 99, 121, 4421);

大きなINリスト

SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3,...]);

空のINリスト

-- Use empty array trick in PostgreSQL:
SELECT * FROM products WHERE id = ANY($1::bigint[]);
-- Pass {} to get zero matches without rewriting the SQL.

INとJOIN

-- With IN:
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE pro);

-- With JOIN:
SELECT o.*
FROM orders o
JOIN users  u ON u.id = o.user_id
WHERE u.pro;

まとめ

クイックチェック