Database Reference
In-Depth Information
The statement output illustrates something typical of self-joins: when you begin with a
reference value in one table instance (
The Potato Eaters
) to find matching rows in a
second table instance (paintings by the same artist), the output includes the reference
value. That makes sense: after all, the reference matches itself. To find only
other
paint‐
ings by the same artist, explicitly exclude the reference value from the output:
mysql>
SELECT p2.title
->
FROM painting AS p1 INNER JOIN painting AS p2
->
ON p1.a_id = p2.a_id
->
WHERE p1.title = 'The Potato Eaters' AND p2.title <> p1.title
+--------------+
| title |
+--------------+
| Starry Night |
+--------------+
The preceding statements use ID value comparisons to match rows in the two table
instances, but any kind of value can be used. For example, to use the
states
table to
answer the question “Which states joined the Union in the same year as New York?”
perform a temporal pairwise comparison based on the year part of the dates in the table's
statehood
column:
mysql>
SELECT s2.name, s2.statehood
->
FROM states AS s1 INNER JOIN states AS s2
->
ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name <> s2.name
->
WHERE s1.name = 'New York'
->
ORDER BY s2.name;
+----------------+------------+
| name | statehood |
+----------------+------------+
| Connecticut | 1788-01-09 |
| Georgia | 1788-01-02 |
| Maryland | 1788-04-28 |
| Massachusetts | 1788-02-06 |
| New Hampshire | 1788-06-21 |
| South Carolina | 1788-05-23 |
| Virginia | 1788-06-25 |
+----------------+------------+
Now suppose that you want to find
every
pair of states that joined the Union in the same
year. In this case, the output potentially can include any pair of rows from the
states
table. A self-join is perfect for this problem:
mysql>
SELECT YEAR(s1.statehood) AS year,
->
s1.name AS name1, s1.statehood AS statehood1,
->
s2.name AS name2, s2.statehood AS statehood2
->
FROM states AS s1 INNER JOIN states AS s2
->
ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name <> s2.name
->
ORDER BY year, name1, name2;
+------+----------------+------------+----------------+------------+
| year | name1 | statehood1 | name2 | statehood2 |
