WHERE Deep Dive ③ — IS NULL dan IN untuk Filter NULL dan Set

Data yang akan kita pakai — tabel customer (dengan NULL)

Artikel deep-dive WHERE ketiga membahas NULL dan filter berbasis set. Kita akan bekerja dengan tabel customer (8 baris) yang dimuat dari CSV, di mana kolom age / email / country masing-masing punya baris dengan NULL (nilai tidak diketahui). Di tabel dunia nyata, normal saja kolom non-required mencampurkan NULL — "email tidak terdaftar," "country dibiarkan kosong," dan seterusnya.

Lima latihan di artikel ini berjalan, secara berurutan: cara yang benar untuk mengambil NULL (IS NULL / IS NOT NULL), pencocokan candidate-list (IN / NOT IN), dan terakhir jebakan yang mudah terlewat saat memakai NOT IN terhadap data yang mengandung NULL.

Sebelum latihan, pastikan definisi kolom tabel customer dan contoh datanya.

① Jalankan PRAGMA table_info(customer); untuk memeriksa nama kolom, tipe, dan primary key.

② Jalankan SELECT * FROM customer LIMIT 5; untuk melihat 5 baris pertama. Perhatikan sel-sel yang NULL.

NULL dan logika tiga-nilai — true, false, unknown

Di kebanyakan bahasa pemrograman, kondisi adalah salah satu dari TRUE atau FALSE — dua pilihan. SQL menambahkan satu state lagi: NULL (unknown). Ini disebut logika tiga-nilai. NULL tidak berarti "nilainya kosong"; artinya "nilainya tidak diketahui." Bahkan NULL = NULL mengevaluasi menjadi NULL (unknown) — membandingkan dua unknown meninggalkan hasilnya unknown.

WHERE kondisi hanya menyimpan baris di mana kondisinya TRUE. Baik baris NULL maupun FALSE dikecualikan. Setelah aturan itu masuk, kamu akan melihat kenapa WHERE email = NULL selalu kembali kosong.

Kenapa `email = NULL` tidak mengembalikan apa pun

Perbandingan email = NULL selalu mengembalikan NULL (unknown) saat salah satu sisinya NULL. 'unknown' = NULL dan NULL = NULL keduanya mengevaluasi menjadi NULL. WHERE hanya menyimpan baris di mana kondisinya TRUE, jadi kondisi NULL dikecualikan, meninggalkan 0 baris.

Untuk mengambil NULL, kamu butuh sintaks khusus kolom IS NULL / kolom IS NOT NULL. Ini tidak melakukan perbandingan kesetaraan — mereka langsung memeriksa apakah nilainya NULL — dan hasilnya selalu TRUE atau FALSE.

"Nol pelanggan belum churn"?

Saat mengambil "jumlah pelanggan yang belum churn (deleted_at IS NULL)," saya pernah menulis SELECT COUNT(*) FROM user WHERE deleted_at = NULL; dan hasilnya kembali sebagai 0.

Kenyataannya ada ribuan pengguna aktif, tapi deleted_at = NULL selalu mengevaluasi menjadi NULL (unknown) dan dikecualikan oleh WHERE, jadi tidak peduli berapa banyak pengguna yang ada hasilnya selalu 0. Ini kesalahan yang mudah saat kamu mulai dengan SQL — biasakan memakai `IS NULL` / `IS NOT NULL` untuk setiap pengecekan NULL.

IS NULL dan IS NOT NULL — mengambil nilai yang tidak diketahui

Untuk mengambil baris di mana nilainya NULL (unknown), pakai `kolom IS NULL`. Untuk mengambil baris yang bukan NULL, pakai kolom IS NOT NULL. Ingat saja jangan pernah pakai `= NULL` atau `<> NULL` dan kamu akan terhindar dari jebakan logika tiga-nilai.

Seperti yang ditunjukkan kode di bawah, sintaksnya cuma IS alih-alih = — tapi hasilnya benar-benar berbeda. = NULL mengembalikan kosong; IS NULL dengan benar mengembalikan baris NULL.

-- 1) NG: = NULL tidak mengambil apa pun (selalu NULL = dikecualikan)
SELECT name, email FROM customer WHERE email = NULL;
-- Hasil: 0 baris

-- 2) OK: IS NULL mengambil baris NULL
SELECT name, email FROM customer WHERE email IS NULL;
-- Hasil: 4 baris (Bob / Dave / Frank / Henry)

-- 3) Ambil baris non-NULL
SELECT name, email FROM customer WHERE email IS NOT NULL;
-- Hasil: 4 baris (Alice / Carol / Eve / Grace)

Bayangkan customer support ingin "daftar pelanggan tanpa email terdaftar supaya kita bisa mengirim mereka permintaan registrasi." (Setelah kamu menjalankannya dengan benar, penjelasannya akan muncul.)

① Dari customer, ambil kolom name dan email.

② Saring ke baris di mana `email` adalah NULL.

③ Pastikan hasilnya 4 baris (Bob / Dave / Frank / Henry, semua dengan email NULL).

Bayangkan pengiriman iklan tertarget ingin "pelanggan dengan age dan email terdaftar," disortir berdasarkan age tertinggi dulu.

① Dari customer, ambil kolom name, age, dan email.

② Saring ke baris di mana baik `age` maupun `email` bukan NULL.

③ Sortir berdasarkan `age` descending.

④ Pastikan hasilnya 3 baris (Eve 42 / Grace 35 / Alice 30).

IN dan NOT IN — mencocokkan terhadap candidate list

Saat kamu ingin menguji apakah nilai sebuah kolom ada di daftar kandidat — "country adalah Japan, US, atau UK" — itulah IN. Menulis kolom IN (nilai1, nilai2, ...) mengembalikan baris di mana kolomnya cocok dengan kandidat mana pun. Artinya sama dengan kolom = nilai1 OR kolom = nilai2 OR kolom = nilai3; orang sering memilih IN cuma untuk keterbacaan.

Menambahkan NOT IN mengembalikan baris yang tidak cocok dengan satu pun kandidat.

-- 1) IN: cocok dengan kandidat mana pun
SELECT name, country FROM customer
WHERE country IN ('Japan', 'US');

-- Arti yang sama, ditulis dengan OR
SELECT name, country FROM customer
WHERE country = 'Japan' OR country = 'US';

-- 2) NOT IN: tidak cocok dengan satu pun kandidat
SELECT name, country FROM customer
WHERE country NOT IN ('Japan');

Bayangkan kebutuhan untuk "mengirim email pengumuman ke pelanggan di Japan atau US."

① Dari customer, ambil kolom name dan country.

② Saring ke baris di mana `country` adalah `'Japan'` atau `'US'` (pakai IN).

③ Pastikan hasilnya 5 baris (Alice Japan / Bob US / Carol Japan / Frank Japan / Grace US).

Jebakan `NOT IN` + NULL

NOT IN ('Japan') terlihat seperti mengambil "pelanggan yang country-nya bukan Japan," tapi ia juga mengecualikan baris di mana `country` adalah NULL. Itu karena country NOT IN ('Japan') secara internal mengevaluasi sebagai country <> 'Japan' (tidak sama), dan NULL <> 'Japan' mengembalikan NULL (unknown). NULL bukan TRUE, jadi WHERE menjatuhkan barisnya.

Lebih jauh, menulis NOT IN ('Japan', NULL) — dengan NULL di dalam candidate list — menyebabkan setiap baris mengevaluasi menjadi NULL dan menghilang. Saat memakai NOT IN terhadap kolom yang mungkin mengandung NULL, selalu buat penanganan NULL eksplisit (kolom NOT IN (...) AND kolom IS NOT NULL) atau tulis ulang query dalam bentuk IN.

Bayangkan kebutuhan untuk "menawarkan keuntungan premium ke 2 pelanggan tertua non-Japan."

① Dari customer, ambil kolom name, age, dan country.

② Saring ke baris di mana `country` bukan `'Japan'` (NOT IN).

③ Sortir berdasarkan `age` descending, dan ambil hanya 2 baris pertama.

④ Pastikan hasilnya 2 baris (Grace 35 US / Henry 29 Italy). Alasan Eve (country NULL) dikecualikan akan dijelaskan di bagian hasil.

Membangun jebakan dari Latihan 4, bayangkan kamu ingin mengambil baik "pelanggan yang dipastikan tidak tinggal di Japan" maupun "pelanggan yang country-nya tidak diketahui."

① Dari customer, ambil kolom name, age, dan country.

② Sertakan baris di mana `country` bukan `'Japan'` plus baris di mana `country` adalah NULL (digabung dengan OR).

③ Sortir berdasarkan `age` descending.

④ Pastikan hasilnya 5 baris (Eve 42 NULL / Grace 35 US / Henry 29 Italy / Dave 28 UK / Bob 25 US).