Null value handling for MySQL

We already know that MySQL uses the SQL SELECT command and the WHERE clause to read data from a data table, but the command may not work correctly when the supplied query condition field is NULL.

To handle this situation, MySQL provides three large operators:

• is null: This operator returns True when the value of the column is null.
• is not null: The operator returns True when the value of the column is not NULL.
• <=>: The comparison operator (unlike the = operator) returns True when the two value of the comparison is null.

The conditional comparison operation on NULL is quite special. You cannot use = NULL or! = NULL to find a null value in the column .

In MySQL, a comparison of null values with any other value (even null) always returns FALSE, that is, NULL = NULL returns FALSE.

Handling NULL in MySQL uses the IS null and is not NULL operator.

Instance

Try the following examples:

[Email protected]# mysql-u root-p password;

Enter password:*******

mysql> use tutorials;

Database changed

Mysql> CREATE TABLE Tcount_tbl

(

-Tutorial_author varchar (+) not NULL,

-Tutorial_count INT

);

Query OK, 0 rows affected (0.05 sec)

Mysql> INSERT into Tcount_tbl

(Tutorial_author, Tutorial_count) VALUES (' Mahran ', 20);

Mysql> INSERT into Tcount_tbl

(Tutorial_author, Tutorial_count) VALUES (' Mahnaz ', NULL);

Mysql> INSERT into Tcount_tbl

(Tutorial_author, Tutorial_count) VALUES (' Jen ', NULL);

Mysql> INSERT into Tcount_tbl

(Tutorial_author, Tutorial_count) VALUES (' Gill ', 20);

Mysql> SELECT * from TCOUNT_TBL;

+-----------------+----------------+

| Tutorial_author | Tutorial_count |

+-----------------+----------------+

|             Mahran | 20 |

|           Mahnaz | NULL |

|           Jen | NULL |

|             Gill | 20 |

+-----------------+----------------+

4 rows in Set (0.00 sec)

Mysql>

You can see in the following instance that the = and! = operators are not working:

Mysql> SELECT * from tcount_tbl WHERE tutorial_count = NULL;

Empty Set (0.00 sec)

Mysql> SELECT * from Tcount_tbl WHERE tutorial_count! = NULL;

Empty Set (0.01 sec)

If the Tutorial_count column in the lookup data table is NULL, you must use is null and is not NULL, as in the following example:

Mysql> SELECT * from TCOUNT_TBL

, WHERE Tutorial_count is NULL;

+-----------------+----------------+

| Tutorial_author | Tutorial_count |

+-----------------+----------------+

|           Mahnaz | NULL |

|           Jen | NULL |

+-----------------+----------------+

2 rows in Set (0.00 sec)

Mysql> SELECT * from TCOUNT_TBL

, WHERE Tutorial_count is not NULL;

+-----------------+----------------+

| Tutorial_author | Tutorial_count |

+-----------------+----------------+

|             Mahran | 20 |

|             Gill | 20 |

+-----------------+----------------+

2 rows in Set (0.00 sec)

Null value handling for MySQL