Basic SQL - Learning MySQL - page 149

Again, the expression in parentheses lists albums that meet a condition—those that are
numbered 4 or 6—and the NOT operator negates it so that we get everything else.
The NOT operator's precedence can be a little tricky. Formally, if you apply it to any
statement that evaluates to a Boolean FALSE or arithmetic zero, you'll get TRUE (and
TRUE is defined as 1). If you apply it to a statement that is nonzero, you'll get FALSE (and
FALSE is defined as 0). We've so far considered examples with clauses where the NOT is
followed by a expression in parentheses, such as NOT (album_id = 4 OR album_id = 6) .
You should write your NOT expressions in this way, or you'll get unexpected results. For
example, the previous expression isn't the same as this one:
mysql> SELECT * FROM album WHERE album_id > 2
-> AND (NOT album_id) = 4 OR album_id = 6;
+-----------+----------+-------------------------+
| artist_id | album_id | album_name |
+-----------+----------+-------------------------+
| 1 | 6 | Substance 1987 (Disc 1) |
+-----------+----------+-------------------------+
1 row in set (0.00 sec)
This returns unexpected results: just those albums with an album_id of 6. To understand
what happened, try just the part of the statement with the NOT operator:
mysql> SELECT * FROM album WHERE (NOT album_id) = 4;
Empty set (0.00 sec)
What has happened is that MySQL has evaluated the expression NOT album_id , and
then checked if it's equal to 4. Since the album_id is always nonzero, NOT album_id is
always zero and, therefore, never equal to 4, and you get no results! Now, try this:
mysql> SELECT * FROM album WHERE (NOT album_id) != 4;
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 2 | 1 | Let Love In |
| 1 | 1 | Retro - John McCready FAN |
| 1 | 2 | Substance (Disc 2) |
| 1 | 3 | Retro - Miranda Sawyer POP |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 3 | 1 | Live Around The World |
| 3 | 2 | In A Silent Way |
| 1 | 5 | Power, Corruption & Lies |
| 4 | 1 | Exile On Main Street |
| 1 | 6 | Substance 1987 (Disc 1) |
| 5 | 1 | Second Coming |
| 6 | 1 | Light Years |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
13 rows in set (0.00 sec)
Again album_id is always nonzero, and so NOT album_id is 0. Since 0 isn't equal to 4, we
see all albums as answers. So be careful to use those parentheses: if you don't, NOT 's