In SQL, null is not != 1 | Johan Driessen

In SQL, null is not != 1

Had an interesting error today. An database query that should have returned thousands of lines returned nothing. Turned out it was due to how the inequality operator works in SQL. In most programming languages, like C# or java the inequality operator handles null, so for example the following code (although extremely stupid) will work.

1
2
3
4
5
6
bool? nullValue = null;
bool? trueValue = true;
bool? falseValue = false;
Console.WriteLine((true != nullValue) );
Console.WriteLine((true != trueValue) );
Console.WriteLine((true != falseValue) );

This will produce the result True, False, True, since true really isn’t equal to null.
In SQL, however, this is not the case. Consider the following SQL query:

1
2
3
4
5
6
7
8
-- TestTable data
-- id    name    isTrue
-- 1     test    1
-- 2     test    0
-- 3     test    NULL

select * from TestTable
where isTrue != 0

If you’re not familiar with SQL, you might think that this would return two rows, the one where isTrue is 1 and the one where isTrue is NULL. This is obviously not so, otherwise I wouldn’t have written this post. You only get the one where isTrue is 1. This is due to the fact that in SQL, the inequality operator (!= or <>) does not work on null values. So in order to get both lines, you would have to modify the query like this:

1
2
select * from TestTable
where isTrue != 0 or isTrue is null

Of course, in this case, this is not the correct solution. A column that is obviously Boolean should not allow null values at all! This was also the case in the non-working query I came across. So the moral of the story is:

Never allow null values in for boolean columns!