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.