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
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,
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,
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.