Reversing or Negating Query Conditions

3.10.1 Problem

You know how to write a query to answer a given question; now you want to ask the opposite question.

3.10.2 Solution

Reverse the conditions in the WHERE clause by using negation operators.

3.10.3 Discussion

The WHERE conditions in a query can be negated to ask the opposite questions. The following query determines when users sent mail to themselves:

mysql> SELECT * FROM mail WHERE srcuser = dstuser; +---------------------+---------+---------+---------+---------+-------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+-------+ | 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 | | 2001-05-14 14:42:21 | barb | venus | barb | venus | 98151 | | 2001-05-15 07:17:48 | gene | mars | gene | saturn | 3824 | | 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 | | 2001-05-15 17:35:31 | gene | saturn | gene | mars | 3856 | | 2001-05-19 22:21:51 | gene | saturn | gene | venus | 23992 | +---------------------+---------+---------+---------+---------+-------+

To reverse this query, to find records where users sent mail to someone other than themselves, change the comparison operator from = (equal to) to != (not equal to):

mysql> SELECT * FROM mail WHERE srcuser != dstuser; +---------------------+---------+---------+---------+---------+---------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+---------+ | 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 | | 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 | | 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 | | 2001-05-14 09:31:37 | gene | venus | barb | mars | 2291 | ...

A more complex query using two conditions might ask when people sent mail to themselves on the same machine:

mysql> SELECT * FROM mail WHERE srcuser = dstuser AND srchost = dsthost; +---------------------+---------+---------+---------+---------+-------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+-------+ | 2001-05-14 14:42:21 | barb | venus | barb | venus | 98151 | | 2001-05-15 08:50:57 | phil | venus | phil | venus | 978 | +---------------------+---------+---------+---------+---------+-------+

Reversing the conditions for this query involves not only changing the = operators to !=, but changing the AND to OR:

mysql> SELECT * FROM mail WHERE srcuser != dstuser OR srchost != dsthost; +---------------------+---------+---------+---------+---------+---------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+---------+ | 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 | | 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 | | 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 | | 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 | ...

You may find it easier just to put the entire original expression in parentheses and negate the whole thing with NOT:

mysql> SELECT * FROM mail WHERE NOT (srcuser = dstuser AND srchost = dsthost); +---------------------+---------+---------+---------+---------+---------+ | t | srcuser | srchost | dstuser | dsthost | size | +---------------------+---------+---------+---------+---------+---------+ | 2001-05-11 10:15:08 | barb | saturn | tricia | mars | 58274 | | 2001-05-12 12:48:13 | tricia | mars | gene | venus | 194925 | | 2001-05-12 15:02:49 | phil | mars | phil | saturn | 1048 | | 2001-05-13 13:59:18 | barb | saturn | tricia | venus | 271 | ...

3.10.4 See Also

If a column involved in a condition may contain NULL values, reversing the condition is a little trickier. See Recipe 3.13 for details.