NULL, dan kenapa filter-mu tidak mengembalikan apa pun

NULL bukan nol dan bukan string kosong. Dia berarti tidak diketahui. Begitu kamu memegang satu kalimat itu, sebagian besar perilaku SQL yang mengejutkan jadi bisa diturunkan, bukan dihafal.

Perbandingan mengembalikan tiga nilai, bukan dua

Setiap perbandingan yang melibatkan NULL menghasilkan NULL — bukan benar, bukan salah:

SELECT 1 = NULL;        -- NULL
SELECT 1 <> NULL;       -- NULL
SELECT NULL = NULL;     -- NULL

Dan WHERE menyimpan sebuah baris hanya ketika kondisinya benar. Bukan “tidak salah” — benar. Jadi WHERE coupon_code = NULL tidak mengembalikan apa pun, tidak pernah, di tabel mana pun. Itulah kenapa IS NULL ada sebagai sintaks terpisah.

Tabel logikanya mengikuti dari “tidak diketahui”:

salah AND NULL adalah salah karena satu yang salah sudah cukup. benar OR NULL adalah benar dengan alasan yang sama. Di semua tempat lain, ketidaktahuan menular.

NOT NULL adalah NULL, dan detail itulah yang jadi isi bagian berikutnya.

Bug NOT IN

Ini kesalahan NULL yang paling mahal, karena hasilnya himpunan kosong, bukan error.

-- Customer yang belum pernah order. Kelihatan benar. Salah.
SELECT * FROM customer
WHERE id NOT IN (SELECT customer_id FROM "order");

Kalau ada satu saja baris subquery itu yang bernilai NULL, seluruh query mengembalikan nol baris. IN adalah rangkaian OR, jadi NOT IN adalah rangkaian AND atas <>:

id <> 1 AND id <> 7 AND id <> NULL
      -- menjadi --
 benar  AND  benar  AND  NULL   =   NULL   -->  baris ditolak

Setiap baris ditolak, dan tidak ada yang memberitahumu. Di skema kita order.customer_id itu NOT NULL, jadi query khusus ini aman hari ini — dan berhenti aman di hari seseorang membuat kolom itu boleh NULL. Jangan bergantung pada itu.

Tiga perbaikan, berurutan sesuai yang paling dianjurkan:

-- 1. NOT EXISTS: benar terlepas dari NULL, dan biasanya paling cepat
SELECT * FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM "order" o WHERE o.customer_id = c.id);

-- 2. LEFT JOIN ... IS NULL (sebuah "anti-join"), lihat materi 4
SELECT c.* FROM customer c
LEFT JOIN "order" o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- 3. NOT IN dengan NULL disingkirkan — jalan, tapi kamu harus ingat alasannya
SELECT * FROM customer
WHERE id NOT IN (SELECT customer_id FROM "order" WHERE customer_id IS NOT NULL);

NOT EXISTS adalah kebiasaan yang pantas dibangun. Dia tidak punya jebakan NULL untuk diingat, dan dia bisa berhenti di baris cocok pertama alih-alih membangun daftar penuh.

Perhatikan ketidaksimetrisannya: IN biasa dengan NULL di dalam daftar tidak rusak. Dia cuma tidak akan pernah cocok dengan NULL-nya, dan itu memang yang kamu harapkan.

Membandingkan dua kolom yang boleh NULL

Kadang kamu benar-benar ingin “keduanya sama, dengan NULL dianggap sebuah nilai”. Kedua mesin menuliskannya berbeda:

-- PostgreSQL: baris yang kuponnya berubah, termasuk menjadi atau dari NULL
WHERE new_coupon IS DISTINCT FROM old_coupon

-- MySQL
WHERE NOT (new_coupon <=> old_coupon)

Ini paling penting pada deteksi perubahan dan pada logika upsert. Menulis WHERE new_coupon <> old_coupon di sana diam-diam mengabaikan setiap baris yang salah satu sisinya NULL — padahal itu justru baris di mana sesuatu baru diisi atau dikosongkan.

COALESCE dan NULLIF

COALESCE mengembalikan argumen pertamanya yang bukan NULL. Kedua mesin punya.

SELECT id, COALESCE(coupon_code, 'none') AS coupon FROM "order";

MySQL juga punya IFNULL(a, b) untuk kasus dua argumen, dan PostgreSQL tidak. Pakai COALESCE supaya query-nya berpindah mesin tanpa diubah.

NULLIF(a, b) mengembalikan NULL ketika a = b, dan itu alat untuk mengubah nilai sentinel kembali jadi ketiadaan yang sebenarnya — sekaligus untuk menghindari pembagian dengan nol:

SELECT SUM(unit_cents * quantity) / NULLIF(SUM(quantity), 0) AS avg_cents
FROM order_item;

Pembagian dengan NULL menghasilkan NULL. Pembagian dengan nol adalah error di PostgreSQL dan, di MySQL pada mode ketat bawaan, juga error. NULLIF mengubah kegagalan itu jadi NULL yang bisa kamu COALESCE.

CASE

CASE adalah kondisional milik SQL, dan dia berhenti di kecocokan pertama sesuai urutan:

SELECT id,
       CASE
         WHEN shipped_at IS NOT NULL           THEN 'shipped'
         WHEN status = 'paid'                  THEN 'awaiting dispatch'
         WHEN placed_at < now() - interval '7 days' THEN 'stale'
         ELSE 'new'
       END AS state
FROM "order";

Tanpa ELSE, baris yang tidak cocok menghasilkan NULL — sumber umum NULL yang tak terjelaskan di sebuah laporan. Tulis ELSE-nya bahkan ketika kamu yakin dia tidak mungkin tercapai.

Di MySQL, now() - interval '7 days' ditulis NOW() - INTERVAL 7 DAY.

Agregat mengabaikan NULL, dan COUNT tempat kamu menyadarinya

Semua agregat kecuali COUNT(*) melewati input NULL.

SELECT
  COUNT(*)            AS rows_total,       -- menghitung baris
  COUNT(shipped_at)   AS rows_shipped,     -- menghitung nilai bukan-NULL
  AVG(unit_cents)     AS avg_price         -- mengabaikan NULL dalam rata-rata
FROM "order" o JOIN order_item i ON i.order_id = o.id;

COUNT(col) yang lebih kecil dari COUNT(*) bukan bug; itu definisinya. Dan AVG yang melewati NULL berarti pembaginya adalah jumlah nilai yang diketahui — dan itu biasanya benar, tapi salah kalau maksudmu menganggap yang tidak diketahui itu nol. Kalau begitu, katakan: AVG(COALESCE(unit_cents, 0)).

Agregat atas nol baris juga pantas diketahui: SUM dari ketiadaan adalah NULL, bukan 0. COUNT dari ketiadaan adalah 0. Jadi COALESCE(SUM(x), 0) adalah bentuk aman di laporan yang mungkin tidak punya baris.

NULL dan UNIQUE

Constraint UNIQUE mengizinkan banyak NULL, di kedua mesin, karena dua hal yang tidak diketahui tidak diketahui sama.

-- PostgreSQL maupun MySQL menerima semua ini
INSERT INTO "order" (customer_id, status, placed_at, coupon_code)
VALUES (1, 'new', now(), NULL), (1, 'new', now(), NULL);

Itu perilaku standar dan biasanya memang yang kamu mau. Kalau bukan — kalau kamu butuh “paling banyak satu baris tanpa kupon per customer” — jawabannya adalah partial index di PostgreSQL (CREATE UNIQUE INDEX ... WHERE coupon_code IS NULL) dan, di MySQL, sebuah generated column, karena MySQL tidak punya partial index. Materi 8 membahasnya.

Yang perlu dibawa pulang

• NULL berarti tidak diketahui; WHERE hanya menyimpan yang benar.
• NOT IN atas kolom yang boleh NULL tidak mengembalikan apa pun. Pakai NOT EXISTS.
• Kesamaan aman-NULL: IS NOT DISTINCT FROM di PostgreSQL, <=> di MySQL.
• Utamakan COALESCE daripada IFNULL; pakai NULLIF supaya pembagi aman.
• Selalu tulis ELSE di dalam CASE.
• SUM atas nol baris adalah NULL, bukan nol.