CODEWITHSUNDEEP
mysql
Aaron Murray
Aaron Murray

Reputation: 2030

MySQL query IS NOT test against multiple values

Is there a better way to write the following 'where' portion of a mysql query:

WHERE t.status IS NOT 'resolved'
  AND t.status IS NOT 'closed'
  AND t.status IS NOT 'deleted'

can they be combined into a single where statement?

Upvotes: 5

Views: 6351

Answers (2)

HJW
HJW

Reputation: 23453

In most cases, you need to consider cost issue before attempting to combine these into something more readable and smarter. Check your explain plan or equivalent for MySQL if this change results in an increased cost.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562951

WHERE t.status NOT IN ('resolved', 'closed', 'deleted')

Boolean algebra says these two expressions are equivalent:

NOT A AND NOT B AND NOT C

NOT (A OR B OR C)

This is DeMorgan's Law.

Upvotes: 12

Related Questions