MySQL の自己結合

SELECT FIRST_ALIAS.COLUMN_NAME, SECOND_ALIAS.COLUMN_NAME
FROM TABLE_NAME AS FIRST_ALIAS
JOIN TABLE_NAME AS SECOND_ALIAS
  ON FIRST_ALIAS.COLUMN_NAME = SECOND_ALIAS.COLUMN_NAME;

SELECT first_name FROM actor JOIN actor ON actor.last_name = actor.last_name LIMIT 1;

ERROR 1066 (42000): Not unique table/alias: 'actor'

SELECT a1.first_name, a1.last_name, a2.first_name AS also_named
FROM actor AS a1
JOIN actor AS a2 ON a1.last_name = a2.last_name AND a1.actor_id < a2.actor_id
ORDER BY a1.last_name, a1.first_name, a2.first_name;

+-------------+-------------+-------------+
| first_name  | last_name   | also_named  |
+-------------+-------------+-------------+
| CHRISTIAN   | AKROYD      | DEBBIE      |
| CHRISTIAN   | AKROYD      | KIRSTEN     |
| KIRSTEN     | AKROYD      | DEBBIE      |
| CUBA        | ALLEN       | KIM         |
| CUBA        | ALLEN       | MERYL       |
| KIM         | ALLEN       | MERYL       |
...
+-------------+-------------+-------------+
108 rows in set (0.00 sec)

SELECT f1.title, f2.title AS same_length, f1.length
FROM film AS f1
JOIN film AS f2 ON f1.length = f2.length AND f1.film_id < f2.film_id
WHERE f1.length = 185
ORDER BY f1.title, f2.title;

+--------------------+--------------------+--------+
| title              | same_length        | length |
+--------------------+--------------------+--------+
| CHICAGO NORTH      | CONTROL ANTHEM     |    185 |
| CHICAGO NORTH      | DARN FORRESTER     |    185 |
| CHICAGO NORTH      | GANGS PRIDE        |    185 |
...
+--------------------+--------------------+--------+
45 rows in set (0.00 sec)

SELECT a1.first_name, a1.last_name, a2.first_name AS also_named
FROM actor AS a1
JOIN actor AS a2 ON a1.last_name = a2.last_name
ORDER BY a1.last_name, a1.first_name, a2.first_name;

+-------------+--------------+-------------+
| first_name  | last_name    | also_named  |
+-------------+--------------+-------------+
| CHRISTIAN   | AKROYD       | CHRISTIAN   |
| CHRISTIAN   | AKROYD       | DEBBIE      |
| CHRISTIAN   | AKROYD       | KIRSTEN     |
| DEBBIE      | AKROYD       | CHRISTIAN   |
| DEBBIE      | AKROYD       | DEBBIE      |
| DEBBIE      | AKROYD       | KIRSTEN     |
...
+-------------+--------------+-------------+
416 rows in set (0.00 sec)