Anda dapat menampilkan data pada sebuah table apakah mempunyai nilai Null atau tidak dengan menggunakan operator IS NULL dan IS NOT NULL.
Sintak Dasar
Di bawah ini adalah sintak dasar penggunaan nilai Null di MySQL pada Select statement:
SELECT column_name1, column_name2,... ,column_name3 FROM table_name WHERE column_name IS [NOT] N
Sedangkan di bawah ini adalah sintak dasar penggunaan nilai Null pada Insert, Update, dan Delete statement.
Insert statement
INSERT INTO table_name(column_name1, column_name2, ..., column_nameN) VALUES (null, null, ..., null);
Update statement
UPDATE table_name SET column_name = null WHERE [condition];
Delete statement
DELETE FROM table_name WHERE column_name IS NULL;
Contoh
Asumsikan Anda telah memiliki tabel "tbl_pegawai" dengan data-data sebagai berikut:
mysql> SELECT * FROM tbl_pegawai; +------------+------------------+---------------+----------+------------+------------+ | id_pegawai | nama_pegawai | jenis_kelamin | gaji | alamat | departemen | +------------+------------------+---------------+----------+------------+------------+ | 1 | Nursalim | L | 1000000 | Brebes | IT | | 2 | Naura Krasiva | P | 1500000 | Tegal | FIN | | 3 | Nurul Anawariyah | P | 2000000 | Pemalang | HRD | | 4 | Achmad Fathoni | L | 3000000 | Batang | IT | | 5 | Nurul Hikmah | P | 9000000 | Pekalongan | DIR | | 6 | Iskiyati | P | 3000000 | Semarang | ACC | | 7 | Dodi Al Farizi | L | 3000000 | Kendal | FIN | | 8 | Susanto | L | 4500000 | Cilacap | MKT | | 9 | Susanti | P | 43500000 | Banyumas | MKT | | 10 | Doni Nugroho | L | 3750000 | Pemalang | HRD | +------------+------------------+---------------+----------+------------+------------+ 10 rows in set (0.00 sec)
- Update data pegawai alamatnya menjadi null yaitu pegawai yang mempunyai id_pegawai 1, 3, 5, dan 7 dan tampilkan kembali data pegawai tersebut.
mysql> UPDATE tbl_pegawai -> SET alamat = NULL -> WHERE id_pegawai IN (1,3,5,7); Query OK, 4 rows affected (0.14 sec) Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM tbl_pegawai; +------------+------------------+---------------+----------+----------+------------+ | id_pegawai | nama_pegawai | jenis_kelamin | gaji | alamat | departemen | +------------+------------------+---------------+----------+----------+------------+ | 1 | Nursalim | L | 1000000 | NULL | IT | | 2 | Naura Krasiva | P | 1500000 | Tegal | FIN | | 3 | Nurul Anawariyah | P | 2000000 | NULL | HRD | | 4 | Achmad Fathoni | L | 3000000 | Batang | IT | | 5 | Nurul Hikmah | P | 9000000 | NULL | DIR | | 6 | Iskiyati | P | 3000000 | Semarang | ACC | | 7 | Dodi Al Farizi | L | 3000000 | NULL | FIN | | 8 | Susanto | L | 4500000 | Cilacap | MKT | | 9 | Susanti | P | 43500000 | Banyumas | MKT | | 10 | Doni Nugroho | L | 3750000 | Pemalang | HRD | +------------+------------------+---------------+----------+----------+------------+ 10 rows in set (0.00 sec)
- Tampilkan data pegawai yang mempunyai alamat
mysql> SELECT * FROM tbl_pegawai -> WHERE alamat IS NOT NULL; +------------+----------------+---------------+----------+----------+------------+ | id_pegawai | nama_pegawai | jenis_kelamin | gaji | alamat | departemen | +------------+----------------+---------------+----------+----------+------------+ | 2 | Naura Krasiva | P | 1500000 | Tegal | FIN | | 4 | Achmad Fathoni | L | 3000000 | Batang | IT | | 6 | Iskiyati | P | 3000000 | Semarang | ACC | | 8 | Susanto | L | 4500000 | Cilacap | MKT | | 9 | Susanti | P | 43500000 | Banyumas | MKT | | 10 | Doni Nugroho | L | 3750000 | Pemalang | HRD | +------------+----------------+---------------+----------+----------+------------+ 6 rows in set (0.00 sec)
- Tampilkan data pegawai yang tidak mempunyai alamat
mysql> SELECT * FROM tbl_pegawai -> WHERE alamat IS NULL; +------------+------------------+---------------+---------+--------+------------+ | id_pegawai | nama_pegawai | jenis_kelamin | gaji | alamat | departemen | +------------+------------------+---------------+---------+--------+------------+ | 1 | Nursalim | L | 1000000 | NULL | IT | | 3 | Nurul Anawariyah | P | 2000000 | NULL | HRD | | 5 | Nurul Hikmah | P | 9000000 | NULL | DIR | | 7 | Dodi Al Farizi | L | 3000000 | NULL | FIN | +------------+------------------+---------------+---------+--------+------------+ 4 rows in set (0.00 sec)
- Hapus data pegawai yang tidak mempunyai alamat (alamat = null), dan tampilkan kembali semua data pegawai.
mysql> DELETE FROM tbl_pegawai -> WHERE alamat IS NULL; Query OK, 4 rows affected (0.30 sec)
mysql> SELECT * FROM tbl_pegawai; +------------+----------------+---------------+----------+----------+------------+ | id_pegawai | nama_pegawai | jenis_kelamin | gaji | alamat | departemen | +------------+----------------+---------------+----------+----------+------------+ | 2 | Naura Krasiva | P | 1500000 | Tegal | FIN | | 4 | Achmad Fathoni | L | 3000000 | Batang | IT | | 6 | Iskiyati | P | 3000000 | Semarang | ACC | | 8 | Susanto | L | 4500000 | Cilacap | MKT | | 9 | Susanti | P | 43500000 | Banyumas | MKT | | 10 | Doni Nugroho | L | 3750000 | Pemalang | HRD | +------------+----------------+---------------+----------+----------+------------+ 6 rows in set (0.00 sec)
Sekian tutorial singkat tentang Mengenal Dan Menggunakan Nilai Null Di MySQL. Semoga bermanfaat & Happy Learning MySQL.
Salam,
Nursalim
No comments:
Post a Comment