MySQL queries empty or non-empty fields (is null and not null), mysqlnull

MySQL queries empty or non-empty fields (is null and not null), mysqlnull

Now let's set the birth field of a record in the test table to null.

Mysql> update test set t_birth = null where t_id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

OK. Execution successful!
When a field value is set to NULL, the syntax is set <field Name> = NULL.
Note: There is no case sensitivity. It can be null or NULL.

The result is as follows:

Mysql> select * from test;
+ ------ + -------- + ---------------------------------- + ------------ +
| T_id | t_name | t_password | t_birth |
+ ------ + -------- + ---------------------------------- + ------------ +
| 1 | name1. | 12345678901234567890123456789012 | NULL |
| 2 | name2 | 12345678901234567890123456789012 | 2013-01-01 |
+ ------ + -------- + ---------------------------------- + ------------ +
2 rows in set (0.00 sec)

Next, query the records with the t_birth value being null or not:

Mysql> select * from test where t_birth is null;
+ ------ + -------- + ---------------------------------- + --------- +
| T_id | t_name | t_password | t_birth |
+ ------ + -------- + ---------------------------------- + --------- +
| 1 | name1. | 12345678901234567890123456789012 | NULL |
+ ------ + -------- + ---------------------------------- + --------- +
1 row in set (0.00 sec)

Mysql> select * from test where t_birth is not null;
+ ------ + -------- + ---------------------------------- + ------------ +
| T_id | t_name | t_password | t_birth |
+ ------ + -------- + ---------------------------------- + ------------ +
| 2 | name2 | 12345678901234567890123456789012 | 2013-01-01 |
+ ------ + -------- + ---------------------------------- + ------------ +
1 row in set (0.00 sec)

Note:
1. syntax for querying empty field values: where <field Name> is null
2. syntax for querying field values not empty: where <field Name> is not null

This article describes how to query empty or non-empty fields (is null and not null) in MySQL. I hope this will help you. Thank you!