IN dan NOT IN untuk Himpunan

IN: Mencocokkan dengan Daftar

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

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

IN dengan Subkueri

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

NOT IN: Mengecualikan Daftar

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

Jebakan NULL pada NOT IN

-- 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
);

Mengapa NOT EXISTS Lebih Aman

IN vs OR — Keterbacaan

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

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

IN dengan Tuple (Konstruktor Baris)

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

Penggunaan Indeks

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

Daftar IN Besar

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

IN dengan Daftar Kosong

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

IN vs 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;

Ringkasan

Pemeriksaan Singkat