Databases Reference
In-Depth Information
-> ORDER BY played DESC;
+-----------------------+---------------------+
| track_name | played |
+-----------------------+---------------------+
| New Blues | 2006-08-15 14:33:57 |
| Intruder | 2006-08-15 14:26:12 |
| In A Silent Way | 2006-08-15 14:00:03 |
| Bizarre Love Triangle | 2006-08-14 10:54:02 |
| Crystal | 2006-08-14 10:47:21 |
| Regret | 2006-08-14 10:43:37 |
| Ceremony | 2006-08-14 10:41:43 |
| The Perfect Kiss | 2006-08-14 10:36:54 |
| True Faith | 2006-08-14 10:30:25 |
| Temptation | 2006-08-14 10:25:22 |
| Fine Time | 2006-08-14 10:21:03 |
| Do You Love Me? | NULL |
| Nobody's Baby Now | NULL |
| Loverman | NULL |
| Jangling Jack | NULL |
The right join is useful sometimes because it allows you to write a query more naturally,
expressing it in a way that's more intuitive. However, you won't often see it used, and
we'd recommend avoiding it where possible.
Both the
LEFT JOIN
and
RIGHT JOIN
can use either the
USING
or
ON
clauses discussed for
the
INNER JOIN
earlier in this chapter in “The Inner Join.” You should use one or the
other: without them, you'll get the Cartesian product discussed in “The Inner Join.”
There's an extra
OUTER
keyword that you can optionally use in left and right joins, to
make them read as
LEFT OUTER JOIN
and
RIGHT OUTER JOIN
. It's just an alternative syntax
that doesn't do anything different, and you won't often see it used. We stick to the
basic versions in this topic.
The Natural Join
We're not big fans of the natural join that we're about to describe in this section. It's
in here only for completeness and because you'll see it used sometimes in SQL state-
ments you'll encounter. Our advice is to avoid using it where possible.
A natural join is, well, supposed to be magically natural. This means that you tell
MySQL what tables you want to join, and it figures out how to do it and gives you an
INNER JOIN
result set. Here's an example for the
artist
and
album
tables:
mysql>
SELECT artist_name, album_name FROM artist NATURAL JOIN album;
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
