Kueri berjalan tanpa kesalahan dan mengembalikan angka yang tampak masuk akal, namun barisnya hilang dan rata-ratanya salah. Nilai SQL NULL sangat sering menjadi penyebabnya. NULL tidak berperilaku seperti nilai normal lainnya, dan hal ini menarik perhatian para pengembang pemula dan berpengalaman.

Panduan ini menunjukkan dengan tepat bagaimana perilaku NULL. Kami menjalankan setiap kueri di SQLite 3.50, dan hasil yang ditampilkan berasal dari proses tersebut. Sebagian besar perilaku ini adalah SQL standar, dan kami mencatat perbedaan database, seperti urutan NULL, dan perlakuan Oracle terhadap string kosong.

Apa itu NULL?

NULL adalah penanda yang berarti suatu nilai hilang atau tidak diketahui. Ini bukan nilai dalam pengertian biasa, jadi ini semua adalah hal yang berbeda:

Perbedaannya penting terutama untuk jumlah. Bonus 0 berarti orang tersebut tidak menerima apa pun. Bonus NULL berarti belum ada yang tahu.

Tabel sampel

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Perhatikan bahwa Sara memiliki string kosong untuk telepon, sedangkan Arjun dan Ravi memiliki NULL. Keduanya diperlakukan sangat berbeda.

Menguji NULL

Inilah kesalahan yang dilakukan hampir semua orang pada awalnya:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

Kueri pertama adalah SQL yang valid dan tidak memberikan kesalahan. Itu tidak menghasilkan apa pun, karena membandingkan apa pun dengan NULL tidak pernah benar. Sara muncul di bawah IS NOT NULL, karena string kosong adalah nilai nyata. Namun, di Oracle, string kosong diperlakukan sebagai NULL, yang merupakan pengecualian.

Logika tiga nilai

Di sebagian besar bahasa pemrograman, suatu kondisi bernilai benar atau salah. SQL menambahkan hasil ketiga: tidak diketahui. Jika Anda tidak mengetahui suatu nilai, Anda tidak dapat mengatakan apakah nilainya sama dengan 5. Dan dua nilai yang tidak diketahui tidak diketahui sama, itulah sebabnya NULL = NULL tidak benar. Dalam pengujian kami, semua ini menghasilkan NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Menggabungkan kondisi mengikuti aturan yang cermat. Berikut adalah hasil dari proses kita, dimana 1 adalah benar dan 0 adalah salah:

Baris yang hilang dari WHERE

WHERE menyimpan baris hanya jika kondisinya benar. Baris yang hasilnya tidak diketahui akan dihilangkan, begitu juga dengan baris yang salah. Hal ini membawa hasil yang mengejutkan:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Anda mungkin mengharapkan Arjun dan Ravi muncul, karena telepon mereka jelas bukan nomor tersebut. Namun ponsel mereka tidak diketahui, sehingga hasilnya tidak diketahui, dan mereka menghilang. Untuk menyertakannya, ucapkan secara eksplisit: WHERE phone <> '555-0101' OR phone IS NULL. Negasi juga tidak membantu:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara dan Ravi tidak memiliki bonus, jadi mereka tidak muncul di bonus > 400 maupun kebalikannya. Hal penting yang perlu diingat: suatu kondisi dan negasinya tidak selalu mencakup setiap baris.

COALESCE: menyediakan cadangan

COALESCE mengembalikan nilai non-NULL pertama dalam daftarnya, dan berfungsi di semua database utama:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Sara masih tampak kosong, karena string kosong bukan NULL. Ada fungsi serupa, seperti IFNULL di MySQL dan SQLite, ISNULL di SQL Server dan NVL di Oracle. COALESCE adalah pilihan portabel. Poin kami sebelumnya tentang penghitungan juga berlaku di sini. bonus + 100 memberikan NULL untuk siapa pun tanpa bonus, jadi tulis COALESCE(bonus, 0) + 100 jika memperlakukan bonus yang hilang sebagai nol sudah benar untuk bisnis Anda.

NULLIF: mengubah nilai menjadi NULL

NULLIF(a, b) mengembalikan NULL jika kedua nilainya sama, dan sebaliknya mengembalikan a. Dua kegunaan sangat membantu. Itu dapat membersihkan nilai placeholder:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Kueri kedua adalah cara praktis untuk menemukan nilai yang “hilang atau kosong”. Hal ini juga dapat mencegah pembagian dengan nol, yang menimbulkan kesalahan di banyak database:

SELECT total / NULLIF(quantity, 0) FROM sales;

Ketika quantity adalah 0, ini memberikan NULL daripada gagal. Dalam pengujian kami, SELECT 100 / NULLIF(0, 0) mengembalikan NULL.

Agregat lewati NULL

Di sinilah NULL melakukan kerusakan paling besar pada laporan:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Jadi berapa rata-rata bonusnya, 400 atau 200? Itu tergantung pada apa arti NULL dalam data Anda. Jika NULL berarti “tidak ada bonus yang dibayarkan”, maka 200 benar. Jika artinya “belum diputuskan”, maka 400 mungkin benar. Basis data tidak dapat membuat keputusan ini untuk Anda, jadi pertanyaannya harus ditujukan kepada seseorang yang mengetahui bisnis tersebut.

Jebakan terkait: SUM yang tidak memiliki nilai adalah NULL, bukan 0. Ketika kita hanya menjumlahkan baris tanpa bonus, kita mendapatkan NULL, sementara COUNT menghasilkan 0. Kode aplikasi yang mengharapkan suatu angka dapat mogok dalam hal ini, jadi bungkuslah: COALESCE(SUM(bonus), 0).

KELOMPOK BERDASARKAN, sortir dan BERBEDA

Meskipun NULL “tidak sama” dalam perbandingan, operasi ini memperlakukannya sebagai satu kelompok:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Lihat SQL GROUP BY dan HAVING untuk mengetahui lebih lanjut tentang pengelompokan. Urutan pengurutan adalah tempat dimana database benar-benar berbeda. SQLite dan MySQL memperlakukan NULL sebagai nilai terkecil, sedangkan PostgreSQL dan Oracle memperlakukannya sebagai nilai terbesar. Jadi ORDER BY bonus DESC yang sama dapat menempatkan NULL sebagai yang terakhir di satu dan yang pertama di yang lain. PostgreSQL, Oracle, dan SQLite modern memungkinkan Anda menyatakan hal ini secara langsung, menggunakan NULLS FIRST atau NULLS LAST.

Bergabung

Kondisi gabungan yang menggunakan = tidak pernah cocok dengan NULL, jadi baris dengan kunci NULL dikeluarkan dari gabungan dalam:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera tidak memiliki manajer, jadi gabungan dalam secara diam-diam menghapus baris itu. A LEFT JOIN menyimpannya, dan itu juga membuat NULL sendiri untuk sisi yang hilang. Baca selengkapnya di penjelasan gabungan SQL.

Perangkap TIDAK DALAM

Satu NULL dalam daftar NOT IN menghapus setiap hasil:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

Bentuk kedua berarti manager_id <> 1 AND manager_id <> NULL, dan bagian terakhir itu tidak akan pernah benar. Hal ini sangat berbahaya pada subquery, dimana NULL dapat muncul tanpa disadari. Gunakan NOT EXISTS sebagai gantinya, seperti yang dijelaskan dalam tutorial subkueri SQL kami. Meera juga hilang dari hasil pertama, karena baris manager_id itu adalah NULL.

Mendesain dengan NULL

• Gunakan NOT NULL secara default. Izinkan NULL hanya jika suatu nilai benar-benar hilang.
• Tambahkan default yang masuk akal. Misalnya, quantity INT NOT NULL DEFAULT 0.
• Jangan gunakan nilai ajaib. Tanggal 01-01-1900 atau usia -1 yang mewakili “tidak diketahui” akan merusak perhitungan Anda.
• Ketahui perilaku UNIK. Kebanyakan database mengizinkan beberapa NULL dalam kolom UNIQUE, dan pengujian kami menyisipkan dua tanpa kesalahan. SQL Server adalah pengecualian, karena biasanya mengizinkannya.
• Ingat bahwa kunci utama tidak boleh NULL. Lihat kunci utama vs kunci asing.
• Uji dengan NULL. Data pengujian tanpa NULL menyembunyikan bug ini. Sertakan mereka dengan sengaja, seperti yang Anda lakukan pada kasus tepi lainnya.

Penutup

Perlakukan NULL sebagai “tidak diketahui”, dan sebagian besar perilakunya mulai masuk akal: tidak diketahui ditambah satu tidak diketahui, dan tidak diketahui sama dengan tidak diketahui tidak diketahui kebenarannya. Audit salah satu laporan Anda minggu ini. Periksa kolom yang dapat dibatalkan menggunakan COUNT(*) terhadap COUNT(column), cari NOT IN, dan putuskan apa arti NULL di setiap rata-rata.

Pertanyaan yang sering diajukan

Teruslah belajar