KAL
Menu

Semantik NULL

Logika tiga nilai, perilaku NULL pada perbandingan dan agregasi, serta pemakaian IS NULL.

Daftar isi

NULL menandai nilai yang tidak diketahui atau tidak berlaku, dan penandaan ini berperilaku berbeda dari nilai apa pun termasuk string kosong maupun angka nol. Topik ini membahas logika tiga nilai yang melandasinya, pencarian baris dengan IS NULL, perilaku NULL pada fungsi agregat, jebakan NOT IN yang menyaring seluruh baris, dan penggunaan COALESCE untuk mengganti nilai yang kosong pada tampilan. Semua demonstrasi memakai tabel nilai ujian yang sebagian barisnya sengaja tidak bernilai.

Logika tiga nilai#

Perbandingan biasa hanya mengenal dua hasil, yaitu benar dan salah, sedangkan SQL menambahkan satu hasil lagi bernama unknown karena NULL berarti tidak diketahui. Membandingkan nilai yang diketahui dengan NULL menghasilkan unknown, dan membandingkan NULL dengan NULL juga menghasilkan unknown karena dua hal yang sama-sama tidak diketahui belum tentu sama. Demonstrasi berikut menunjukkan ketiga bentuk perbandingan itu tanpa perlu tabel apa pun.

bash 
# membandingkan nilai dengan NULL
mysql -t -u root -e "SELECT NULL = NULL AS null_eq_null, NULL <> NULL AS null_neq_null, 1 = NULL AS satu_eq_null;"
text Keluaran 
+--------------+---------------+--------------+
| null_eq_null | null_neq_null | satu_eq_null |
+--------------+---------------+--------------+
|         NULL |          NULL |         NULL |
+--------------+---------------+--------------+

Ketiga kolom mengembalikan NULL, bukan 0 ataupun 1, karena hasil perbandingannya memang tidak diketahui. Klausa WHERE hanya menyertakan baris yang perbandingannya benar, sehingga baris yang perbandingannya unknown ikut tersaring tanpa pesan apa pun. Konsekuensi praktisnya muncul pada gabungan kondisi. Rantai AND yang memuat satu hasil unknown menghasilkan unknown, sedangkan rantai OR yang memuat satu hasil benar tetap benar karena satu bukti sudah cukup. Tabel kebenaran tiga nilai ini menentukan cara menyusun kondisi pada bagian-bagian berikutnya, terutama saat kondisinya menyentuh kolom yang boleh tanpa nilai. Tabel latihan yang memuat NULL diperlukan mulai bagian berikutnya.

IS NULL dan pencarian baris tanpa nilai#

Basis data latihan berikut membuat tabel nilai ujian dengan empat baris, dan baris Citra sengaja dibiarkan tanpa nilai agar perilaku NULL terlihat pada data asli. Nilai NULL dinyatakan secara eksplisit pada INSERT, dan cara yang sama berlaku pada UPDATE.

sql skema-null.sql 
-- tidak-dijalankan
DROP DATABASE IF EXISTS latihan_tipe;
CREATE DATABASE latihan_tipe;
USE latihan_tipe;

CREATE TABLE nilai_ujian (
  id INT PRIMARY KEY,
  siswa VARCHAR(40) NOT NULL,
  nilai TINYINT UNSIGNED NULL
);

INSERT INTO nilai_ujian VALUES
  (1, 'Ali', 88),
  (2, 'Budi', 74),
  (3, 'Citra', NULL),
  (4, 'Dewi', 91);
bash 
# membuat ulang basis data latihan dan tabelnya
mysql -u root < skema-null.sql

Pencarian pertama memakai perbandingan biasa terhadap nilai 80. Hasilnya menyertakan tiga baris dan melewatkan baris Citra tanpa peringatan apa pun.

bash 
# menyaring baris dengan perbandingan biasa
mysql -t -u root latihan_tipe -e "SELECT id, siswa, nilai FROM nilai_ujian WHERE nilai <> 80;"
text Keluaran 
+----+-------+-------+
| id | siswa | nilai |
+----+-------+-------+
|  1 | Ali   |    88 |
|  2 | Budi  |    74 |
|  4 | Dewi  |    91 |
+----+-------+-------+

Baris Citra tidak lolos karena NULL <> 80 bernilai unknown. Pencarian kedua memakai pembanding IS NULL yang hasilnya hanya benar atau salah tanpa wilayah ketiga.

bash 
# menemukan baris tanpa nilai dengan IS NULL
mysql -t -u root latihan_tipe -e "SELECT id, siswa, nilai FROM nilai_ujian WHERE nilai IS NULL;"
text Keluaran 
+----+-------+-------+
| id | siswa | nilai |
+----+-------+-------+
|  3 | Citra |  NULL |
+----+-------+-------+

Pembanding kebalikannya IS NOT NULL menemukan baris yang bernilai, sedangkan bentuk nilai = NULL tidak pernah menemukan apa pun karena hasil perbandingannya selalu unknown. MySQL menyediakan operator <=> yang menganggap NULL sama dengan NULL, tetapi bentuk IS NULL tetap menjadi pilihan yang paling jelas dan portabel. Deklarasi NOT NULL pada kolom adalah kebalikan dari semua ini, yaitu larangan menyimpan NULL pada kolom tersebut, dan pelanggarannya menimbulkan error yang didemonstrasikan pada bagian Kesalahan umum.

NULL pada fungsi agregat#

Fungsi agregat mengabaikan NULL, kecuali COUNT(*) yang menghitung baris dan bukan nilai. Dengan demikian COUNT(nilai) menghitung hanya baris yang bernilai, dan AVG(nilai) membagi jumlah nilai dengan banyaknya nilai, bukan dengan banyaknya baris. Demonstrasi berikut menampilkan ketiganya sekaligus pada tabel latihan.

bash 
# membandingkan COUNT(*) dengan COUNT(nilai) dan AVG(nilai)
mysql -t -u root latihan_tipe -e "SELECT COUNT(*) AS semua_baris, COUNT(nilai) AS baris_bernilai, AVG(nilai) AS rata_rata FROM nilai_ujian;"
text Keluaran 
+-------------+----------------+-----------+
| semua_baris | baris_bernilai | rata_rata |
+-------------+----------------+-----------+
|           4 |              3 |   84.3333 |
+-------------+----------------+-----------+

Rata-rata 84.3333 diperoleh dari 253 yang dibagi tiga, yaitu jumlah 88, 74, dan 91, dan bukan 63.25 yang muncul apabila 253 dibagi empat. Perbedaan ini sering menjadi sumber laporan yang menyesatkan apabila pembaca laporan menganggap rata-ratanya mencakup seluruh baris. Pengurutan juga memperlakukan NULL secara khusus, yaitu pada ORDER BY menaik baris tanpa nilai muncul lebih dulu.

bash 
# mengurutkan baris dengan NULL di urutan pertama
mysql -t -u root latihan_tipe -e "SELECT id, siswa, nilai FROM nilai_ujian ORDER BY nilai, id;"
text Keluaran 
+----+-------+-------+
| id | siswa | nilai |
+----+-------+-------+
|  3 | Citra |  NULL |
|  2 | Budi  |    74 |
|  1 | Ali   |    88 |
|  4 | Dewi  |    91 |
+----+-------+-------+

Baris Citra dengan NULL muncul di urutan pertama, disusul baris bernilai menurut nilai dan kunci pemecah seri id. Apabila baris tanpa nilai diinginkan di urutan akhir, kunci sortirnya dapat dinyatakan sebagai ORDER BY nilai IS NULL, nilai, id karena ekspresi nilai IS NULL bernilai 0 untuk baris bernilai dan 1 untuk baris tanpa nilai. Perilaku yang sama berlaku pada GROUP BY, yaitu NULL membentuk kelompoknya sendiri dan bukan kelompok terpisah per baris.

Jebakan NOT IN dan penggantian nilai#

Predikat NOT IN berperilaku mengejutkan apabila daftar pembandingnya memuat NULL. Setiap baris diuji dengan rantai perbandingan <> yang dihubungkan AND, dan satu saja hasil unknown pada rantai tersebut membuat keseluruhan predikat bernilai unknown, sehingga tidak ada baris yang lolos. Demonstrasi berikut menghitung baris yang lolos dari dua bentuk daftar, satu memuat NULL dan satu tidak.

bash 
# menghitung hasil NOT IN dengan dan tanpa NULL pada daftarnya
mysql -t -u root latihan_tipe <<'SQL'
SELECT COUNT(*) AS baris_dengan_null FROM nilai_ujian WHERE id NOT IN (1, NULL, 4);
SELECT COUNT(*) AS baris_tanpa_null FROM nilai_ujian WHERE id NOT IN (1, 4);
SQL
text Keluaran 
+-------------------+
| baris_dengan_null |
+-------------------+
|                 0 |
+-------------------+
+------------------+
| baris_tanpa_null |
+------------------+
|                2 |
+------------------+

Daftar yang memuat NULL menghasilkan nol baris, sedangkan daftar tanpa NULL menghasilkan dua baris yang memang bukan 1 atau 4. Pola ini paling sering muncul ketika daftar NOT IN berasal dari subquery, sehingga kehadiran satu saja NULL pada hasil subquery menyaring seluruh laporan tanpa pesan apa pun. Perbaikannya adalah menambahkan WHERE kolom IS NOT NULL pada subquery tersebut atau memakai NOT EXISTS.

Fungsi COALESCE mengembalikan argumen pertama yang bukan NULL, sehingga berguna untuk mengganti nilai kosong dengan pengganti yang aman saat menampilkan data.

bash 
# mengganti NULL dengan 0 pada tampilan
mysql -t -u root latihan_tipe -e "SELECT id, siswa, COALESCE(nilai, 0) AS nilai_subs FROM nilai_ujian;"
text Keluaran 
+----+-------+------------+
| id | siswa | nilai_subs |
+----+-------+------------+
|  1 | Ali   |         88 |
|  2 | Budi  |         74 |
|  3 | Citra |          0 |
|  4 | Dewi  |         91 |
+----+-------+------------+

Penggantian pada demonstrasi ini hanya memengaruhi tampilan, sedangkan data di tabel tetap NULL. Nilai penggantinya harus dipilih dengan hati-hati karena angka 0 dan ketiadaan nilai tidak bermakna sama pada kolom nilai ujian, dan laporan yang menjumlahkan hasil substitusi dapat berbeda dari laporan yang mengabaikan NULL. Alternatifnya adalah IFNULL(nilai, 0) yang khusus untuk dua argumen.

Kesalahan umum#

  1. Menulis = NULL pada WHERE. Kesalahan ini tidak menimbulkan error, sehingga baru terasa dari hasil yang selalu kosong. Perbaikannya adalah IS NULL untuk mencari baris tanpa nilai dan IS NOT NULL untuk kebalikannya.
  2. Menyamakan string kosong dengan NULL. String kosong adalah nilai yang diketahui, sehingga dapat dibandingkan dan dihitung panjangnya, sedangkan NULL menandakan nilai yang tidak diketahui. Fungsi CHAR_LENGTH('') mengembalikan 0, sementara CHAR_LENGTH(NULL) mengembalikan NULL. Indeks unik memperlakukan keduanya berbeda pula, yaitu string kosong hanya boleh muncul sekali sedangkan beberapa baris NULL tetap diperbolehkan. Perbaikannya adalah menetapkan konvensi sejak perancangan kolom, misalnya catatan yang kosong dinyatakan NULL, lalu konsisten mengikutinya.
  3. Memasukkan NULL ke kolom yang dinyatakan NOT NULL. Kolom siswa pada tabel latihan dinyatakan NOT NULL, sehingga sisipan berikut ditolak server.
bash 
# mencoba memasukkan NULL ke kolom NOT NULL
mysql -u root latihan_tipe -e "INSERT INTO nilai_ujian (id, siswa) VALUES (5, NULL);"
text Keluaran 
ERROR 1048 (23000) at line 1: Column 'siswa' cannot be null

Error 1048 menunjuk kolom yang melanggarnya. Perbaikannya adalah mengisi nilai yang sah atau mengubah deklarasi kolom agar menerima NULL apabila ketiadaan nilai memang diperbolehkan. Keputusan ini sebaiknya diambil saat perancangan karena mengubah kolom besar agar menerima NULL setelahnya adalah operasi skema yang lebih berat.

Ringkasan#

  • NULL berarti tidak diketahui, dan setiap perbandingan yang menyentuh NULL menghasilkan unknown yang tersaring oleh WHERE.
  • IS NULL dan IS NOT NULL adalah pembanding yang tepat untuk mencari baris tanpa nilai dan baris bernilai.
  • Fungsi agregat mengabaikan NULL, sehingga COUNT(nilai) dan AVG(nilai) hanya menghitung baris bernilai, sedangkan COUNT(*) menghitung semua baris.
  • NOT IN yang daftarnya memuat NULL menghasilkan nol baris, dan COALESCE mengganti nilai kosong hanya pada tampilan.

Referensi#