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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.