SQL NULL: tricky equality
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;
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;
= operator we need to use
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.
IS NOT) operators while working with NULL values.