Using Joins and Subqueries - MySQL - page 476

The simplest join involves two tables and selects all columns from each. The following
join between the artist and painting tables shows this (the ORDER BY clause makes the
result easier to read):
mysql> SELECT * FROM artist INNER JOIN painting ORDER BY artist.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 1 | Da Vinci | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 1 | 2 | Mona Lisa | MI | 87 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 3 | Van Gogh | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 4 | The Potato Eaters | KY | 67 |
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |
| 3 | Van Gogh | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 4 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 4 | Renoir | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 4 | Renoir | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
+------+----------+------+------+-------------------+-------+-------+
An INNER JOIN produces results that combine values in one table with values in another
table. The preceding query specifies no restrictions on row matching, so the join gen‐
erates all row combinations (that is, the Cartesian product). This result illustrates why
such a join generally is not useful: it produces a lot of unmeaningful output. Clearly,
you don't maintain these tables to match every artist with every painting.
To answer questions meaningfully, produce only the relevant matches by including
appropriate join conditions. For example, to produce a list of paintings together with
the artist names, associate rows from the two tables using a simple WHERE clause that
matches values based on the artist ID column that is common to both tables and serves
to link them:
mysql> SELECT * FROM artist INNER JOIN painting
-> WHERE artist.a_id = painting.a_id
-> ORDER BY artist.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 1 | 1 | The Last Supper | IN | 34 |
| 1 | Da Vinci | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 3 | 3 | Starry Night | KY | 48 |