Learning Mysql

Before we move on, let’s consider what purpose the WHEREON, 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 albums. 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 columns, 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.

Download 4.24 Mb.

Share with your friends: