MySQL Cookbook

Reversing or Negating Query Conditions

Problem

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

Solution

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

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 ...