Nilai NULL dalam bahasa pemrograman seringkali merupakan konsep yang membingungkan dan cukup rumit untuk digunakan. Di sini, di tutorial ini, kita akan menjelajahi berbagai kegunaan, tips, dan trik saat menangani nilai NULL dalam kueri MySQL
Sebuah kolom dalam sebuah tabel dapat berisi nilai NULL karena kolom tersebut didefinisikan sebagai allow NULL, hal ini telah dijelaskan secara menyeluruh pada tutorial Nullability Constraint. Nilai NULL bisa menjadi umum karena ada situasi di mana kolom harus berisi NULL di mana menetapkan nilai default tidak praktis. Dalam situasi lain, perancang tabel mungkin tidak cukup bijaksana untuk membuat kolom NOT NULL. Oleh karena itu, berurusan dengan nilai NULL telah menjadi tugas sehari-hari bagi setiap pengembang SQL
Untuk memahami apa itu nilai NULL, pertimbangkan skenario ini - saat memasukkan produk baru ke tabel Produk di mana kolom Deskripsi didefinisikan sebagai NULL, tidak perlu memberikan nilai untuk kolom Deskripsi. Jadi nilai NULL dapat dilihat sebagai nilai yang tidak diketahui yang hilang. Ini bukan string kosong dan juga bukan nol
1. Membandingkan dengan NULL
When making comparison with NULLs by using comparison or arithmetic operators +, -, , =, , the query returns NULL instead of a Boolean TRUE or FALSE. This includes comparing NULL with itself.
One exception to the rule above is operator for "NULL-safe equal to" comparison, it returns TRUE when comparing to a NULL value.
Contoh berikut menunjukkan saat mencari kolom yang mengizinkan NULL, menggunakan ekspresi expr = NULL tidak mengembalikan baris, karena expr = NULL tidak pernah BENAR
Dua kueri berikut membandingkan NULL dengan dirinya sendiri dan mengembalikan NULL
select null = null;
select null <> null;
To look for a NULL value, you must use the IS NULL keyword or NULL-safe operator . The following two queries show how to find the NULL ReportsTo record in Employees table.
Kumpulan hasil kueri - 1 baris dikembalikan
Di MySQL, nilai default untuk kebenaran dari operasi Boolean adalah 1 dan nilai default untuk false dari operasi Boolean adalah 0
Menggunakan perbandingan aman NULL
sama dengan NULL-aman. Dokumentasi MySQL menyatakan bahwa operator melakukan perbandingan kesetaraan seperti operator =, tetapi mengembalikan 1 daripada NULL jika kedua operan adalah NULL, dan 0 daripada NULL jika salah satu operan adalah NULL
Kumpulan hasil kueri. 1 rekaman dikembalikan
Kueri berikut menggunakan perbandingan aman NULL untuk mendapatkan karyawan teratas (Wakil Presiden, Penjualan)
Kumpulan hasil kueri - 1 baris dikembalikan
Ini kueri dengan menggunakan operator yang sama (=). Hasilnya NULL
Kumpulan hasil kueri - 0 baris dikembalikan
2. Memilih data dengan nilai NULL
2. 1 Fungsi COALESCE mengembalikan nilai non-NULL pertama dalam daftar yang dipisahkan koma
Kumpulan hasil kueri - 1 baris dikembalikan
Kumpulan hasil kueri - 0 baris dikembalikan
2. 2 Fungsi ISNULL menguji apakah suatu ekspresi adalah NULL
Kueri ini mengambil karyawan yang tidak melapor kepada siapa pun karena kolom ReportsTo berisi NULL untuk karyawan tersebut
Kumpulan hasil kueri - 1 baris dikembalikan
2. 3 Gunakan fungsi aliran kontrol untuk bekerja dengan nilai NULL
3 kueri berikut menggunakan fungsi alur kontrol untuk mengetahui kepada siapa karyawan melapor. Jika kolom ReportsTo null, karyawan tidak melapor kepada siapa pun
Kumpulan hasil kueri - 9 baris dikembalikan
2. 4 NULLIF() fungsi
MySQL NULLIF() mengembalikan NULL ketika ekspresi pertama sama dengan ekspresi kedua, jika tidak, ia mengembalikan ekspresi pertama
NULLIF(expr1,expr2) mengembalikan NULL jika expr1 = expr2 benar, jika tidak mengembalikan expr1. Ini sama dengan menggunakan pernyataan kasus CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END
Kueri berikut menggabungkan NULLIF dengan pernyataan kasus
SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;_
Kumpulan hasil kueri - 9 baris dikembalikan
3. Nilai NULL dan klausa ORDER BY
Saat menggunakan kolom yang berisi nilai NULL dalam klausa ORDER BY, nilai NULL ditampilkan terlebih dahulu jika Anda melakukan ORDER BY dalam urutan menaik, sedangkan nilai NULL disajikan terakhir jika Anda melakukan ORDER BY dalam urutan menurun
Kueri ini mencantumkan Wakil Presiden Andrew Fuller sebagai catatan pertama karena ReportsTo-nya berisi NULL dan ORDER BY dengan urutan menaik (tidak perlu menentukan kata kunci ASC karena penyortiran default mengasumsikan urutan menaik)
select FirstName, LastName, Title from Employees
order by ReportsTo;
Kumpulan hasil kueri - 9 baris dikembalikan
Kueri ini mencantumkan Wakil Presiden Andrew Fuller sebagai rekaman terakhir karena DESC ditentukan dalam klausa ORDER BY
select FirstName, LastName, Title from Employees
order by ReportsTo DESC;
Kumpulan hasil kueri - 9 baris dikembalikan
4. Gunakan nilai NULL dengan fungsi agregat (ringkasan).
4. 1 Perbedaan antara COUNT(*), COUNT(expr), dan COUNT(distinct expr)
COUNT(*) mengembalikan hitungan jumlah baris yang diambil, apakah mengandung nilai NULL atau tidak
select count(*) from employees;_
Kumpulan hasil kueri - 1 baris dikembalikan
COUNT(expr) hanya mengembalikan hitungan nilai non-NULL karena nilai NULL diabaikan
Kueri di bawah mengembalikan 8 daripada 9 karena Wakil Presiden Andrew Fuller tidak melapor kepada siapa pun sehingga catatannya tidak dihitung
select count(ReportsTo) from employees;
Kumpulan hasil kueri - 1 baris dikembalikan
Saat mengekstrak record yang berbeda, nilai NULL disertakan. Saat menghitung jumlah record yang berbeda, nilai NULL diabaikan
Kueri berikut mengembalikan 3 catatan, termasuk nilai NULL
select distinct ReportsTo from employees;_
Kumpulan hasil kueri - 3 baris dikembalikan
Tetapi jika Anda menghitung catatan yang berbeda, ini mengembalikan 2 karena fungsi COUNT hanya mengembalikan hitungan nilai non-NULL
select count(distinct ReportsTo) from employees;
Kumpulan hasil kueri - 1 baris dikembalikan
4. 2 Fungsi agregat MAX(), MIN(), SUM(), dll. , abaikan nilai NULL
4. 3 Saat menggunakan GROUP BY dengan kolom yang berisi nilai NULL, semua NULL disatukan menjadi satu grup
select ReportsTo, count(*) from employees
group by ReportsTo;_
Kumpulan hasil kueri - 3 baris dikembalikan
4. 4 GULUNG
ROLLUP menghasilkan nilai total untuk kolom yang digunakan dalam klausa GROUP BY. MySQL selalu mengembalikan NULL di kolom rollup untuk menunjukkan baris ini untuk total, jadi kita perlu mengubah nilai NULL menjadi label yang tepat seperti 'Total'
Kueri di bawah ini menampilkan NULL di baris terakhir untuk kolom Judul yang digulung untuk menghitung total kemunculan semua judul
select TitleOfCourtesy as Title,
count(*) as occurrences
from employees
group by TitleOfCourtesy with ROLLUP;_
Kumpulan hasil kueri - 5 baris dikembalikan
Kueri ini menampilkan kata Total untuk baris terakhir dengan mengonversi NULL menjadi Total
SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;_0
Kumpulan hasil kueri - 5 baris dikembalikan
5. Menggunakan nilai NULL di kolom gabungan
INNER JOIN melompati nilai NULL. Kueri berikut adalah gabungan dalam dan mengembalikan 8 catatan di mana catatan Wakil Presiden Andrew Fuller tidak disertakan karena nilai NULL tidak dapat digabungkan dengan apa pun. Solusi untuk memasukkan nilai NULL adalah dengan menggunakan LEFT atau RIGHT JOIN
SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;_1
Kumpulan hasil kueri - 8 baris dikembalikan
LEFT atau RIGHT OUTER JOIN mempertahankan nilai NULL. Kueri berikut mengembalikan 9 catatan tempat catatan Wakil Presiden Andrew Fuller disimpan
SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;_2
Kumpulan hasil kueri - 9 baris dikembalikan
6. Masukkan nilai NULL atau nilai default
Saat menjalankan kueri penyisipan, selalu merupakan praktik yang baik untuk menentukan nilai default untuk kolom nullable. Hal ini membuat pemeliharaan meja jauh lebih mudah
Untuk jenis karakter kolom, nilai default dapat berupa string kosong '', 'Unknown', atau 'NA' (Not Applicable)
Untuk jenis kolom tanggal atau waktu, tentukan tanggal jauh di masa lalu atau jauh di masa depan. Misalnya, dalam tabel SDM, untuk posisi berkelanjutan/permanen, Tanggal Berakhir pekerjaan dapat ditetapkan ke 31/12/2999 untuk menunjukkan kontrak kerja jangka panjang.
Untuk jenis kolom numerik, nilai default bisa 0 atau -1, atau nilai lain yang sesuai dengan situasi Anda. Jika kolom numerik adalah kolom kunci asing dengan integritas referensial yang diberlakukan, Anda perlu menambahkan nilai default 0 atau -1 sebagai kunci primer di tabel induk (juga dikenal sebagai tabel master atau referensi, atau tabel kunci primer). Biasanya 0 mewakili 'Tidak Diketahui' dan -1 mewakili 'NA
Kueri berikut akan gagal dengan Kode Kesalahan 1048 karena kolom CompanyName tidak boleh NULL dan tidak ada batasan DEFAULT yang ditentukan untuk kolom ini
Kueri ini menyisipkan nilai NULL di kolom ReportsTo dan berhasil karena kolom mengizinkan NULL
Karyawan baru yang dimasukkan ditunjukkan di bawah ini
7. Gunakan NULL dengan kolom TIMESTAMP atau kolom AUTO_INCREMENT
Untuk beberapa tipe data, MySQL menangani nilai NULL secara khusus. Jika Anda memasukkan NULL ke dalam kolom TIMESTAMP, tanggal dan waktu saat ini akan dimasukkan. Jika Anda memasukkan NULL ke dalam kolom bilangan bulat yang memiliki atribut AUTO_INCREMENT, angka berikutnya dalam urutan tersebut akan dimasukkan
Dalam kueri di bawah ini, kami sengaja menghilangkan kolom EmployeeID dan kami tidak menentukan nilainya. Setelah dijalankan, secara otomatis memasukkan EmployeeID 11 karena ini adalah urutan nomor otomatis berikutnya yang akan digunakan
SELECT t1.FirstName, t1.LastName,
case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone'
else CONCAT(t2.FirstName, ' ', t2.LastName)
end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;_3
Karyawan baru yang dimasukkan ditunjukkan di bawah ini
8. Mengindeks kolom nullable
Anda dapat menambahkan indeks pada kolom yang memiliki nilai NULL jika Anda menggunakan mesin penyimpanan MyISAM, InnoDB, atau BDB, atau MEMORY. Jika tidak, Anda harus mendeklarasikan kolom terindeks BUKAN NULL, dan Anda tidak dapat memasukkan NULL ke dalam kolom