Operator dan NULL SQLite: IS NULL, COALESCE, IFNULL

NULL Berarti "Tidak Diketahui"

Setiap nilai lain di SQLite mewakili sesuatu yang pasti: angka, string, blob. NULL berbeda. Nilai ini adalah penanda untuk nilai yang tidak ada atau tidak diketahui. Satu gagasan itu menjelaskan setiap keanehan yang dilakukan NULL di dalam query.

Siapkan tabel kecil untuk dicoba-coba:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

Dua kolom mengizinkan null. Boris tidak punya email. Cleo tidak punya umur. Dan tidak punya keduanya. Sisa halaman ini membahas cara meng-query baris-baris seperti ini tanpa tertipu.

= dan <> Tidak Bekerja dengan NULL

Naluri pertamamu adalah menulis WHERE email = NULL. Kelihatannya masuk akal. Hasilnya kosong:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

Nol baris, padahal Boris dan Dan jelas punya email yang null. Alasannya: membandingkan apa pun dengan NULL menghasilkan NULL, bukan benar atau salah. Klausa WHERE di SQLite hanya menyimpan baris yang kondisinya benar, dan NULL tidak benar. Jadi barisnya tersaring keluar.

Jebakan yang sama terjadi dengan <>:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

Kamu mungkin mengira query ini mengembalikan semua orang kecuali Ada. Ternyata hanya Cleo yang muncul. Boris dan Dan, yang email-nya null, ikut terbuang, karena NULL <> 'ada@example.com' juga bernilai NULL, bukan benar.

Ini jebakan SQL yang paling umum. Setiap kali sebuah query "kehilangan baris" yang tidak kamu duga, curigai kolom yang null.

Gunakan IS NULL dan IS NOT NULL

Cara yang benar untuk mengecek null adalah operator IS. Berbeda dengan =, operator ini mengenali null dan selalu mengembalikan benar atau salah, tidak pernah null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

Query pertama mengembalikan Boris dan Dan. Query kedua mengembalikan Ada dan Cleo. IS NULL dan IS NOT NULL adalah dua operator yang dibuat khusus untuk bertanya "apakah nilai ini tidak ada?" Pakai keduanya di mana pun kamu tergoda menulis = NULL atau <> NULL.

Jika kamu ingin "bukan Ada, termasuk yang tidak diketahui", gabungkan pengecekannya secara eksplisit:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

Sekarang Boris, Cleo, dan Dan semuanya muncul.

NULL Menular Lewat Aritmetika dan Penggabungan String

Aturan "tidak diketahui" berlaku lebih luas dari sekadar perbandingan. Operasi apa pun yang menyentuh null akan menghasilkan null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Hi ' || name AS greeting,
    'Age: ' || age AS labelled_age
FROM users;

next_year dan doubled bernilai null untuk Cleo dan Dan. labelled_age juga null untuk mereka: menggabungkan string dengan NULL menghasilkan NULL, bukan 'Age: '. Jika sebuah kolom mungkin null dan kamu butuh nilai yang bisa dipakai di hasilnya, kamu harus menanganinya. Di situlah dua fungsi berikut berperan.

IFNULL: Cadangan dengan Dua Argumen

IFNULL(a, b) mengembalikan a kecuali nilainya null, dan dalam hal itu mengembalikan b. Ini cara paling sederhana untuk mengganti null dengan nilai default:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(no email)') AS email,
    IFNULL(age, 0)              AS age
FROM users;

Boris dan Dan mendapat (no email). Cleo dan Dan mendapat 0. Data aslinya tidak berubah: IFNULL hanya menulis ulang output-nya.

IFNULL selalu menerima tepat dua argumen. Jika kamu butuh lebih banyak cadangan, pakai COALESCE.

COALESCE: Yang Pertama Tidak NULL Menang

COALESCE(a, b, c, ...) menelusuri argumennya dari awal ke akhir dan mengembalikan argumen pertama yang tidak null. Fungsi ini memperluas IFNULL menjadi berapa pun jumlah cadangan:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', 'anonymous') AS contact
FROM users;

Untuk Ada dan Cleo, email-nya yang dipakai. Untuk Boris dan Dan, email-nya null, jadi SQLite mencoba argumen kedua, yaitu alamat buatan berdasarkan nama. Jika itu juga null, SQLite akan lanjut ke 'anonymous'.

COALESCE adalah pilihan yang portabel: setiap database SQL besar mendukungnya dengan cara yang sama. IFNULL adalah kemudahan dari SQLite/MySQL untuk kasus dua argumen. Pilih COALESCE secara default; pakai IFNULL hanya jika kamu memang hanya punya dua argumen dan ingin nama yang lebih pendek.

NULL Bukan String Kosong

Kebingungan yang umum: orang menganggap NULL dan '' bisa saling menggantikan. Padahal tidak.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' adalah string sungguhan yang kebetulan tidak punya karakter. NULL adalah ketiadaan nilai. length('') bernilai 0; length(NULL) sendiri bernilai NULL. Dan NULL = NULL bernilai NULL, bukan 1, dan justru karena itulah IS NULL ada.

Jika sebuah kolom bisa berisi '' maupun NULL, tentukan mana yang berarti "tidak ada" lalu konsisten memakainya. Mencampur keduanya memaksa setiap query menangani dua kasus, dan kamu pasti akan lupa salah satunya.

NULL di IN, NOT IN, dan DISTINCT

Ada beberapa tempat lagi di mana null diam-diam menjebakmu.

IN dengan daftar yang berisi null bisa memberi hasil yang mengejutkan, terutama dengan NOT IN:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

Kamu mungkin mengira hasilnya semua orang yang umurnya bukan 25. Ternyata hasilnya kosong. SQLite menjabarkan NOT IN (25, NULL) kira-kira menjadi age <> 25 AND age <> NULL, dan age <> NULL selalu bernilai NULL, sehingga seluruh kondisinya tidak pernah benar. Solusinya adalah menyaring null dari daftar (atau dari kolomnya) sebelum perbandingan.

Sebaliknya, DISTINCT menganggap null sama satu sama lain saat menghapus duplikat:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

Kamu mendapat tiga baris: email Ada, email Cleo, dan satu NULL (gabungan dari Boris dan Dan). Hal yang sama berlaku untuk GROUP BY dan UNION: keduanya memperlakukan null sebagai satu kelompok, kebalikan dari cara = memperlakukannya. SQL tidak selalu konsisten soal ini, jadi ada gunanya tahu di sisi mana setiap operator berada.

Daftar Periksa Singkat

• Cek nilai yang tidak ada dengan IS NULL / IS NOT NULL. Jangan pernah = NULL.
• Aritmetika, penggabungan string, atau perbandingan apa pun yang menyentuh NULL mengembalikan NULL.
• Pakai COALESCE(a, b, c, ...) untuk mengganti null dengan nilai cadangan. Pakai IFNULL(a, b) untuk bentuk singkat dua argumen.
• String kosong '' tidak sama dengan NULL. Pilih salah satu untuk arti "tidak ada" di setiap kolom.
• NOT IN (..., NULL) hampir selalu adalah bug. Buang null dari daftarnya terlebih dahulu.

Selanjutnya: Mengurutkan Hasil

Begitu kamu bisa memfilter baris dengan benar, termasuk yang null, langkah berikutnya adalah menyusunnya dalam urutan yang berguna. ORDER BY ada di halaman berikutnya, dan klausa ini punya aturannya sendiri soal di mana null diletakkan dalam hasil yang diurutkan.