What is the difference between != NULL and IS NOT NULL in QGIS filter expressions?
Disclaimer: Because the syntax for filtering in QGIS works with SQL, I'm assuming here that SQL rules apply. I'm not completely sure if that's entirely correct, but it seems logical and it does explain the behavior.
The filter works with SQL, that's why you have to look there for an answer.
In short, when using a logical operator in combination with null
, the result is always null
. But in order to test for null
, SQL comes with the IS (NOT)
comparison functionality, which allows to use it for your intended filtering.
Check Bohemian's answer on stackoverflow for a more in-depth discussion.
NULL
is not a value, therefore it cannot equal =
or not equal !=
anything. It is not the same as zero 0
which is a value.
A NULL
indicates that no value has been recorded in the cell you are looking at. To check if a value exists you ask if the cell IS NULL
or if it IS NOT NULL
IS NULL
checks to see if the cell is emptyIS NOT NULL
checks to see if the cell is not empty
If you have some records where a values are One
, Two
, Three
and the rest NULL
and you want to find everything that isn't Two
you would need to use something like
value != 'Two' OR value IS NULL
as NULL values do not get returned in an equals/not equals query. If you used just value != 'Three'
the result would exclude all NULL
records as NULL
isn't a value that can equal or not equal.