25

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?

  • As far as I know it is just syntax. When null use is or is not. Else use = or != – nielsgerrits Jul 25 '16 at 18:02
  • "one would intuitively try" Really? Why? NULL is not a value. It is literally the absence of a value. You can't "equal" NULL! The operator != does not mean "is not"; it means "is not equal to". IS NOT means "is not" so I think this is perfectly intuitive :) – Lightness Races in Orbit Jul 26 '16 at 9:05
  • @LightnessRacesinOrbit Without the understanding that NULL is not a value I would say it is intuitive to try to use = or != as that is how you assess any other value. It's not until you know that NULL isn't a value that it's really intuitive to use IS NOT rather than !=. Many are not aware of what a NULL truly is. – Midavalo Jul 26 '16 at 9:23
  • And furthermore, in the GUI NULL is in values list (cp. above), and hence treated as a 'normal' value. And there is no button IS NULL, or did I miss such?! So, when just typing such query and with the knowledge, that NULL must be treated in a special way we could discuss about intuition, but for shure not in the context of THIS GUI. – Jochen Schwarze Jul 26 '16 at 9:30
  • @Midavalo: While that's true, I would humbly suggest not using a language feature without looking up what it is :) Programming by guessing doesn't work. – Lightness Races in Orbit Jul 26 '16 at 9:49
33

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.

19

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 empty
  • IS 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.