Learning Mysql

| 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 inset 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 seethe 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 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 book contains about querying data, and you should be comfortable understanding almost any SQL query you encounter.

Download 4.24 Mb.

Share with your friends: