Databases Reference
In-Depth Information
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Live Around The World |
| Miles Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+---------------------------+------------------------------------------+
13 rows in set (0.03 sec)
In reality, it's not quite magical: all MySQL does is look for columns with the same
names and, behind the scenes, adds these silently into an inner join with a
USING
clause.
So, the above query is actually translated into:
mysql>
SELECT artist_name, album_name FROM
-> artist INNER JOIN album USING (artist_id);
If identifier columns don't share the same name, natural joins won't work. Also, more
dangerously, if columns that do share the same names aren't identifiers, they'll get
thrown into the behind-the-scenes
USING
clause anyway. For example, if you had
name
columns in the
artist
and
album
tables (instead of
artist_name
and
album_name
),
you'd get
USING (artist_id, name)
and some unpredictable results. The magic and
mystery makes natural joins worth avoiding; spell out queries using an inner join or a
WHERE
clause instead.
You'll sometimes see the natural join mixed with left and right joins. The following are
valid join syntaxes:
NATURAL LEFT JOIN
,
NATURAL LEFT OUTER JOIN
,
NATURAL RIGHT
JOIN
, and
NATURAL RIGHT OUTER JOIN
. The former two are left joins without
ON
or
USING
clauses, and the latter two are right joins. Again, avoid writing them when you
can, but you should understand what they mean if you see them used.
Nested Queries
Nested queries—supported by MySQL since version 4.1—are the most difficult to
learn. However, they provide a powerful, useful, and concise way of expressing difficult
information needs in short SQL statements. This section explains them, beginning with
simple examples and leading to the more complex features of the
EXISTS
and
IN
state-
ments. At the conclusion of this section, you'll have completed everything this topic
contains about querying data, and you should be comfortable understanding almost
any SQL query you encounter.
Nested Query Basics
You know how to find the name of an artist who made a particular album using an
INNER JOIN
:
mysql>
SELECT artist_name FROM
-> artist INNER JOIN album USING (artist_id)
