I want to exclude cells with 3 specific values, what's the proper syntax?

I've got "Attribute" != 'Value1', which works fine. But what's the right syntax to add the other values? I thought I could just use OR followed by the next value, but that doesn't work, e.g. "Attribute" != 'Value1' OR 'Value2' but that isn't working.

  • Ahh... I realized the problem. The syntax must be "Attribute" != 'Value1' AND "Attribute" != 'Value2', I can't just stack OR statements. – BogBody Dec 15 '18 at 4:37
  • 1
    Most SQL logical operators are ternary, in the form val1 op val2. The only one I can think of which isn't is val1 BETWEEN val2 AND val3. Those expressions are combined with AND or OR. Parenthesis are optional in default evaluation, but are recommended for clarity in all compound queries. And the there's NOT, which can flip the evaluation of following expressions, and is used as a modifier in the val IN (list1,...) operator as val NOT IN (list1,...). All of this is generic to the SQL language (and common in most languages), and therefore nearly off-topic to GIS SE. – Vince Dec 15 '18 at 13:16

What you've written is interpreted as "Attribute" is not 'Value1' as one condition, and separately to that ... just plain 'Value2' - which makes no sense with the syntax, as it isn't even a condition.

You would need be more explicit, i.e.,"Attribute" != 'Value1' AND "Attribute" != 'Value2'

But this can get tedious over many values so I would suggest another approach, IN (or in your case, NOT IN).

For your example, try "Attribute" NOT IN ('Value1', 'Value2').

  • Thanks! Could you explain the theory behind the NOT IN condition, briefly? Is this saying something like "return all the items with this attribute that do not have this value in it"? Just trying to parse the meaning a little better in my head so I can remember it. – BogBody Dec 15 '18 at 4:45
  • 1
    Yes, that would be it - it's saying "return all the items where the attribute value is NOT IN this list of values". – she_weeds Dec 15 '18 at 7:51

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.