-> played RIGHT JOIN track USING (artist_id, album_id, track_id)

-> 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 away 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 book.
The Natural Join
We’re not big fans of the natural join that we’re about to describe in this section. It’s inhere only for completeness and because you’ll see it used sometimes in SQL statements 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 |

Download 4.24 Mb.

Share with your friends: