สำหรับ NULL มี 2 คุณสมบัติใน MySQL:
- IS NULL
- IS NOT NULL
มาสร้างตาราง:
mysql> create table NULL_Demo ( UserId int, UserName varchar(20), UserAddress varchar(20) ); Query OK, 0 rows affected (0.67 sec)
ตัวอย่าง
แทรกข้อมูล (มีค่า NULL):
mysql> insert into NULL_Demo values(12345,'John',NULL); Query OK, 1 row affected (0.16 sec) mysql> insert into NULL_Demo values(2345,'Carol','UK'); Query OK, 1 row affected (0.35 sec) mysql> insert into NULL_Demo values(233444,NULL,NULL); Query OK, 1 row affected (0.60 sec) mysql> insert into NULL_Demo values(NULL,NULL,NULL); Query OK, 1 row affected (0.27 sec)
แสดงข้อมูล:
mysql> select * from NULL_Demo;
ผลลัพธ์
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 12345 | John | NULL | | 2345 | Carol | UK | | 233444 | NULL | NULL | | NULL | NULL | NULL | +--------+----------+-------------+ 4 rows in set (0.00 sec)
กรณี 1 - IS NOT NULL:
mysql> select * from NULL_Demo where UserId = 2345 AND UserName = 'Carol' AND UserAddress IS NOT NULL;
ผลลัพธ์แสดงแถวที่ UserAddress ไม่เป็น NULL:
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 2345 | Carol | UK | +--------+----------+-------------+ 1 row in set (0.00 sec)
กรณี 2 - IS NULL:
mysql> select * from NULL_Demo where UserName = 'John' AND UserAddress IS NULL;
ผลลัพธ์แสดงแถวที่ UserAddress เป็น NULL:
+--------+----------+-------------+ | UserId | UserName | UserAddress | +--------+----------+-------------+ | 12345 | John | NULL | +--------+----------+-------------+ 1 row in set (0.00 sec)
