Working with NULL and IS NOT NULL in MySQL

For NULL, there are two properties in MySQL −

• IS NULL
• IS NOT NULL.

To understand the above concept, let us create a table. The query to create a table is as follows −

mysql> create table NULL_Demo
    -> (
    -> UserId int,
    -> UserName varchar(20),
    -> UserAddress varchar(20)
    -> );
Query OK, 0 rows affected (0.67 sec)

Example

Insert some records in the table using insert command. The query is as follows −

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)

Display all records from the table using select statement. The query is as follows −

mysql> select *from NULL_Demo;

Output

+--------+----------+-------------+
| UserId | UserName | UserAddress |
+--------+----------+-------------+
|  12345 |     John |        NULL |
|   2345 |    Carol |          UK |
| 233444 |     NULL |        NULL |
|   NULL |     NULL |        NULL |
+--------+----------+-------------+
4 rows in set (0.00 sec)

Here are the queries that works for IS NULL and IS NOT NULL property.

Case 1 − IS NOT NULL

The query is as follows −

mysql> select *from NULL_Demo where UserId = 2345 AND UserName = 'Carol' AND UserAddress IS NOT NULL;

The following is the output displaying the NOT NULL record according to the condition set inthe above query −

+--------+----------+-------------+
| UserId | UserName | UserAddress |
+--------+----------+-------------+
|   2345 |    Carol |          UK |
+--------+----------+-------------+
1 row in set (0.00 sec)

Case 2 − NOT NULL

The query is as follows −

mysql> select *from NULL_Demo where UserName = 'John' AND UserAddress IS NULL;

The following is the output displaying the NULL record according to the condition set in the above query −

+--------+----------+-------------+
| UserId | UserName | UserAddress |
+--------+----------+-------------+
|  12345 |     John |        NULL |
+--------+----------+-------------+
1 row in set (0.00 sec)