Cara menampilkan nilai null di mysql

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;

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;

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;

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;

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

Bagaimana Anda menampilkan nilai NULL?

Bagaimana cara menampilkan nilai NULL dalam SQL?

Bagaimana Anda menampilkan nilai NULL dalam kolom?

Apakah NULL () di MySQL?