Databases Reference
In-Depth Information
Before we move on, let's consider what purpose the
WHERE
,
ON
, and
USING
clauses serve.
If you omit the
WHERE
clause from the query we showed you, you get a very different
result. Here's the query, and the first few lines of output:
mysql>
SELECT artist_name, album_name FROM artist, album;
+---------------------------+------------------------------------------+
| artist_name | album_name |
+---------------------------+------------------------------------------+
| New Order | Let Love In |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Let Love In |
| The Rolling Stones | Let Love In |
| The Stone Roses | Let Love In |
| Kylie Minogue | Let Love In |
| New Order | Retro - John McCready FAN |
| Nick Cave & The Bad Seeds | Retro - John McCready FAN |
| Miles Davis | Retro - John McCready FAN |
| The Rolling Stones | Retro - John McCready FAN |
| The Stone Roses | Retro - John McCready FAN |
| Kylie Minogue | Retro - John McCready FAN |
...
The output is nonsensical: what's happened is that each row from the
artist
table has
been output alongside each row from the
album
table, for all possible combinations.
Since there are 6 artists and 13 albums, there are 6
×
13 = 78 rows of output, and we
know that only 13 of those combinations actually make sense (there are only 13 al-
bums). This type of query, without a clause that matches rows, is known as a
Cartesian
product
. Incidentally, you also get the Cartesian product if you perform an inner join
without specifying a column with a
USING
or
ON
clause, as in the query:
SELECT artist_name, album_name FROM artist INNER JOIN album;
Later in “The Natural Join,” we'll introduce the natural join, which is an inner join on
identically named columns. While the natural join doesn't use explicitly specified col-
umns, it still produces an inner join, rather than a Cartesian product.
The keyphrase
INNER JOIN
can be replaced with
JOIN
or
STRAIGHT JOIN
; they all do the
same thing. However,
STRAIGHT JOIN
forces MySQL to always read the table on the left
before it reads the table on the right. We'll have a look at how MySQL processes queries
behind the scenes in Chapter 8. The keyphrase
INNER JOIN
is the one you'll see most
commonly used: it's used by many other database systems besides MySQL, and we use
it in all our inner-join examples.
The Union
The
UNION
statement isn't really a join operator. Rather, it allows you to combine the
output of more than one
SELECT
statement to give a consolidated result set. It's useful
in cases where you want to produce a single list from more than one source, or you
want to create lists from a single source that are difficult to express in a single query.
