NOT id IN (1, 2, 3) vs NOT (id IN (1, 2, 3))

September 5, 2008

Problem:

You’re probably wondering why the following query doesn’t  work on some mySQL databases

SELECT * FROM table1 WHERE NOT id IN (1, 2, 3)
works on mySQL v5.0.51a
doesn’t work on mySQL v4.1.21

Solution:

The reason is that the query would confuse the older version what to evaluate by NOT.

This query generally works.

SELECT * FROM table1 WHERE NOT (id IN (1, 2, 3))
works on mySQL v5.0.51a
works on mySQL v4.1.21

This is basically just specifying what should value should be evaluated by NOT.

Filed under: MySQL | Comments (0)
Related Links: