QGIS – Difference Between != NULL and IS NOT NULL in Filter Expressions

expressionfilternullqgis

I have a shape layer with an attribute containing NULL values to which I would like to apply a filter for values different from NULL.

enter image description here

Using the available GUI for such query construction, one would intuitively try

"obj_art" != NULL

enter image description here

Which means 'give me all the features with attribute "obj_art" different from NULL' (these count definitely more than 0). Testing this query delivers a strange result from my point of view:

enter image description here

So what I've learned so far is that I can achieve this by using

"obj_art" IS NOT NULL

The question is, what is the difference between != NULL and IS NOT NULL?

Best Answer

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.

Related Question