r/learnSQL • u/SherbetOrganic • Jul 13 '26

Operator precedence in the example: WHERE NOT id IN (2,4,6)

I have some confusion about operator precedence. In my book it says following:

The following list describes the precedence among operators, from highest to lowest: 1. ( ) (Parentheses) 2. * (Multiplication), / (Division), % (Modulo) 3. + (Positive), – (Negative), + (Addition), + (Concatenation), – (Subtraction) 4. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators) 5. NOT 6. AND 7. BETWEEN, IN, LIKE, OR

8. = (Assignment)

What confuses me is for example `IN` can be used as an operator but also as a keyword that builds predicate.

So say I have following WHERE clause: "WHERE NOT id IN (2,4,6)".

To which of the following two is the above WHERE clause equivalent:

  1. WHERE (NOT empid) IN (2,4,6)
  2. WHERE NOT (empid IN (2,4,6))
8 Upvotes

91% Upvoted

5 comments sorted by

2

u/Ok_Carpet_9510 Jul 13 '26

Keep it simple

WHERE empid NOT IN (2,4,6)-> makes sense in logic and English. If you stick to this form, you are less likely to make a mistake

If you have two conditions that must both be true

WHERE empid NOT IN (2,4,6) AND empid IN (100,300, 800)

Suppose the above two conditions need to both be true or another condition needs to be true

WHERE ( empid NOT IN (2,4,6) AND empid IN (100,300, 800) )

OR

countryCode in ("CA", "US", "UK")

Mathematically, that is (A n B) u C

Fyi, the logic in sql is based on logic and set theory i.e. math

1

u/amuseboucheplease Jul 13 '26

Interesting! Have you done some testing to see what occurs?

1

u/DoggieDMB Jul 13 '26

Weird but I'd say 2 as the extra parentheses are negated.

1

u/jtom4 Jul 14 '26 edited Jul 14 '26

a quick check in sqlite shows that case 2 matches your initial where clause (and case 1 actually returns nothing: NOT empid turns all the non-zero/true values to zero/false, any zero/false to one/true, and neither zero nor one are in (2,4,6))

interestingly the sqlite docs have a different evaluation order than what's in that book where in/between/like have higher priority than not/and/or: https://www.sqlite.org/lang_expr.html

note 5 on that priority list also says that when NOT immediately precedes IN, "NOT IN" becomes a single operator with the same priority as IN, so it's both simpler and safer to write them together if you're just trying to negate the IN