Databases Reference
In-Depth Information
-> album_name LIKE "%g";
+---------------+
| album_name |
+---------------+
| Second Coming |
+---------------+
1 row in set (0.00 sec)
Both examples with parentheses are much easier to understand. We recommend that
you use parentheses whenever there's a chance the intention could be misinterpreted;
there's no good reason to rely on MySQL's implicit evaluation order.
The unary
NOT
operator negates a Boolean statement. Suppose you want a list of all
albums except the ones having an
album_id
of 1 or 3. You'd write the query:
mysql>
SELECT * FROM album WHERE NOT (album_id = 1 OR album_id = 3);
+-----------+----------+------------------------------------------+
| artist_id | album_id | album_name |
+-----------+----------+------------------------------------------+
| 1 | 2 | Substance (Disc 2) |
| 1 | 4 | Retro - New Order / Bobby Gillespie LIVE |
| 3 | 2 | In A Silent Way |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 6 | Substance 1987 (Disc 1) |
| 1 | 7 | Brotherhood |
+-----------+----------+------------------------------------------+
6 rows in set (0.00 sec)
The expression in the parentheses says we want:
(album_id = 1 OR album_id = 3)
and the
NOT
operation negates it so we get everything but those that meet the condition
in the parentheses. There are several other ways you can write a
WHERE
clause with the
same function, and it really doesn't matter which you choose. For example the follow-
ing three expressions have the same effect:
WHERE NOT (album_id = 1) AND NOT (album_id = 3)
WHERE album_id != 1 AND album_id != 3
WHERE album_id != 1 AND NOT (album_id = 3)
Consider another example using
NOT
and parentheses. Suppose you want to get a list
of all albums with an
album_id
greater than 2, but not those numbered 4 or 6:
mysql>
SELECT * FROM album WHERE album_id > 2
-> AND NOT (album_id = 4 OR album_id = 6);
+-----------+----------+----------------------------+
| artist_id | album_id | album_name |
+-----------+----------+----------------------------+
| 1 | 3 | Retro - Miranda Sawyer POP |
| 1 | 5 | Power, Corruption & Lies |
| 1 | 7 | Brotherhood |
+-----------+----------+----------------------------+
3 rows in set (0.01 sec)
