Database Reference
In-Depth Information
mysql>
SELECT * FROM artist ORDER BY a_id;
+------+----------+
| a_id | name |
+------+----------+
| 1 | Da Vinci |
| 2 | Monet |
| 3 | Van Gogh |
| 4 | Renoir |
+------+----------+
mysql>
SELECT * FROM painting ORDER BY a_id, p_id;
+------+------+-------------------+-------+-------+
| a_id | p_id | title | state | price |
+------+------+-------------------+-------+-------+
| 1 | 1 | The Last Supper | IN | 34 |
| 1 | 2 | Mona Lisa | MI | 87 |
| 3 | 3 | Starry Night | KY | 48 |
| 3 | 4 | The Potato Eaters | KY | 67 |
| 4 | 5 | Les Deux Soeurs | NE | 64 |
+------+------+-------------------+-------+-------+
As you acquire more paintings and the tables get larger, it won't be so easy to eyeball
them and answer questions by inspection. Can you answer it using SQL? Sure, although
first attempts at a solution often look something like the following statement, which
uses a not-equal condition to look for mismatches between the two tables:
mysql>
SELECT * FROM artist INNER JOIN painting
->
ON artist.a_id <> painting.a_id
->
ORDER BY artist.a_id;
+------+----------+------+------+-------------------+-------+-------+
| a_id | name | a_id | p_id | title | state | price |
+------+----------+------+------+-------------------+-------+-------+
| 1 | Da Vinci | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 1 | Da Vinci | 3 | 4 | The Potato Eaters | KY | 67 |
| 1 | Da Vinci | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 1 | 1 | The Last Supper | IN | 34 |
| 2 | Monet | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 2 | Monet | 3 | 4 | The Potato Eaters | KY | 67 |
| 2 | Monet | 3 | 3 | Starry Night | KY | 48 |
| 2 | Monet | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 1 | 2 | Mona Lisa | MI | 87 |
| 3 | Van Gogh | 1 | 1 | The Last Supper | IN | 34 |
| 3 | Van Gogh | 4 | 5 | Les Deux Soeurs | NE | 64 |
| 4 | Renoir | 3 | 3 | Starry Night | KY | 48 |
| 4 | Renoir | 1 | 2 | Mona Lisa | MI | 87 |
| 4 | Renoir | 1 | 1 | The Last Supper | IN | 34 |
| 4 | Renoir | 3 | 4 | The Potato Eaters | KY | 67 |
+------+----------+------+------+-------------------+-------+-------+
The query may look plausible but its result obviously is not. For example, it falsely
indicates that each painting was painted by several different artists. The problem is that
the statement lists all combinations of values from the two tables in which the artist ID
values aren't the same. What you really need is a list of values in
artist
that aren't present
