-> AND NOT (album_id = 4 OR album_id = 6)

Again, the expression in parentheses lists albums that meet a condition—those that are numbered 4 or 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 inset 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 inset 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

Download 4.24 Mb.

Share with your friends: