How to check for NULL value in SQL WHERE clause in MySQL

If you have a column that gets a NULL default value in your SQL database, then you should be careful how you define your SQL query to match the column value against NULL.

• You should not use equal to (=) operator or not equal to (!=) operator for comparison with a NULL value

• You should also not consider the NULL value as an empty value.

• The NULL value and None are different in SQL database tables.

• The NULL value and the empty value are also different.

A NULL value is not a zero value or an empty string, or a column that contains just spaces. A NULL value means the column was not assigned any value at the time of data INSERT.

Using IS NULL in the WHERE clause

• The easiest way to check for a NULL value in a column in the WHERE clause is by using IS NULL operator.

• You can use it in MySQL and in SQLServer you can use the ISNULL() function.

Let's see a simple SQL query in which we will use IS NULL in the WHERE clause.

SELECT * FROM my_table WHERE my_column IS NULL;

In the query above,

• my_table is the name of the table.

• my_column is the name of the column that can have a NULL value.

You can also use IS NULL operator with multiple columns in the WHERE clause by using AND.

Here is an example,

SELECT * FROM my_table WHERE my_column1 IS NULL AND my_column2 IS NULL;

Using IS NOT NULL in the WHERE clause

• If you want to check if the value in a column is not equal to NULL then you can use IS NOT NULL operator.

• You can use IS NOT NULL operator for checking for non-zero values or non-empty/empty strings, in simple words anything that is not null.

Let's see a simple SQL query in which we will use IS NOT NULL in the WHERE clause.

SELECT * FROM my_table WHERE my_column IS NOT NULL;

In the query above,

• my_table is the name of the table.

• my_column is the name of the column.

Using IS NULL and IS NOT NULL in the same query

Let's see a simple SQL query where we will use both IS NULL and IS NOT NULL operators,

SELECT * FROM my_table WHERE my_column1 IS NULL AND my_column2 IS NOT NULL;

That's it. So next time if you have to compare a column value for NULL, don't use = or != operators with empty quotes ' ' or zero, instead use the IS NULL or IS NOT NULL operators in SQL.