Databases Reference
In-Depth Information
You can combine
AND
and
OR
, but you need to make it clear whether you want to first
AND
the conditions or
OR
them. Consider an example where the function isn't obvious
from the query:
mysql>
SELECT album_name FROM album WHERE
-> album_name LIKE "L%" OR
-> album_name LIKE "S%" AND
-> album_name LIKE "%g";
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| Second Coming |
| Light Years |
+-----------------------+
4 rows in set (0.00 sec)
When you inspect the results, it becomes clear what's happened: the answers either
begin with
L
, or they have
S
at the beginning and
g
at the end. An alternative interpre-
tation of the query would be that the answers must begin with
L
or
S
, and all end with
g
; this is clearly not how the MySQL server has handled the query, since one of the
displayed answers, “Let Love In,” doesn't end in a
g
. To make queries containing several
Boolean conditions easier to read, group conditions within parentheses.
Parentheses cluster parts of a statement together and help make expressions readable;
you can use them just as you would in basic math. Our previous example can be re-
written as follows:
mysql>
SELECT album_name FROM album WHERE
-> album_name LIKE "L%" OR
-> (album_name LIKE "S%" AND album_name LIKE "%g");
+-----------------------+
| album_name |
+-----------------------+
| Let Love In |
| Live Around The World |
| Second Coming |
| Light Years |
+-----------------------+
4 rows in set (0.00 sec)
The parentheses make the evaluation order clear: we want albums beginning with
'L'
, or those beginning with
'S'
and ending with
'g'
. We've also typed the query over
three lines instead of four, making the intention even clearer through careful layout;
just as when writing program code, spacing, indentation, and careful layout help make
readable queries.
You can also use parentheses to force a different evaluation order. If you did want
albums having names with
'L'
or
'S'
at the beginning and
'g'
at the end, you'd type:
mysql>
SELECT album_name FROM album WHERE
-> (album_name LIKE "L%" OR album_name LIKE "S%") AND
