SQL NULL: tricky equality

2015-11-11 by Tomas Tulka

Don't get confused with NULL in SQL!

Especially when creating a SQL quary from the code, to make the life easier we are using constructions like this:

SELECT * FROM test WHERE 1=1

Then we can easily add a new condition just joined with AND (or OR):

SELECT * FROM test WHERE 1=1 AND ...

and it will be working perfectly fine.

Working with constans can't bring any problem, but it's getting tricky when we're working with variables (columns) like this:

SELECT * FROM test WHERE num=num

The problem is the special value NULL.

Why do we actually need num = num instead of 1 = 1? Well it could be handy when we have a condition inside the query:

SELECT * FROM test WHERE val =  IF(num > 2, 3, val)

By this query we say: give me all the data in the test table where the value of the val column equals 3, in case that value of the num column is greater then 2, otherwise we don't care of the value of the val at all.

Comment: the control flow function IF is RDBMS-related (IF is available in MySQL).

In case when the val column contains NULL values, we can get unexpected behaviour (unexpected for us, but logic as we will see next).

Working with the NULL values

Let's consider a simple table:

CREATE TABLE test (num INT, str VARCHAR(100), str2 VARCHAR(100));

with different values containg NULL values:

INSERT INTO test VALUES (1, 'abc', 'abc'), (2, NULL, NULL), (NULL, '3', NULL), (NULL, NULL, NULL);

The table looks like following:

Executing this query:

SELECT * FROM test WHERE num = num;

will return

But it's very suspicious, because we have added four not two rows.

NULL values can't be compared by the = equality operator. The same behaviour we can see in this example:

SELECT * FROM test WHERE str = str2;

Instead of = operator we need to use IS operator:

SELECT * FROM test WHERE num IS num;

will return the whole table.

SELECT * FROM test WHERE str IS str2;

NULL is an unknown value. As NULL != NULL, we can't do num = NULL nor num != NULL to get the expected results.

Always use IS (IS NOT) operators while working with NULL values.