We have seen SQL SELECT command along with WHERE clause to fetch data from MySQL table. But when we try to give a condition which compare field or column value to NULL it does not work properly.

To handle such situation MySQL provides three operators

• IS NULL: operator returns true of column value is NULL.
• IS NOT NULL: operator returns true of column value is not NULL.
• <=> operator compare values, which (unlike the = operator) is true even for two NULL values

Conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. Such comparisons always fail because it’s impossible to tell whether or not they are true. Even NULL = NULL fails.

To look for columns that are or are not NULL, use IS NULL or IS NOT NULL.

Using NULL values at Command Prompt:

Suppose a table tcount_tbl in TUTORIALS database and it contains two columns tutorial_author and tutorial_count, where a NULL tutorial_count indicates that the value is unknown:

Example:

Try out following examples:

You can see that = and != do not work with NULL values as follows:

To find records where the tutorial_count column is or is not NULL, the queries should be written like this:

Handling NULL Values in PHP Script:

You can use if…else condition to prepare a query based on NULL value.

Example:

Following example take tutorial_count from outside and then compare it with the value available in the table.