MySQL self join

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A self join reads 1 table twice in the same query, so you can compare its rows against each other. It is how you answer questions about pairs: which rows share a value, which row is bigger than which. Open the client with mysql -u root -p sakila before you start.

How a self join works

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;

Replace TABLE_NAME with the table, FIRST_ALIAS and SECOND_ALIAS with 2 different names for it, and each COLUMN_NAME with the column being compared. Nothing else about the join changes. The server has no idea the 2 sides are one table, and treats them as it would any other pair.

The aliases are not optional

Without them the query names 2 things actor and cannot tell them apart.

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

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

ERROR 1066 is the server saying it needs a name for each side.

Examples

Some Sakila actors share a surname. This pairs each of them with the others.

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)

3 actors are called AKROYD, which makes 3 pairs among them.

2) Compare rows on a number

The same shape answers “which films run exactly as long as which”. This narrows it to the longest films in the catalog.

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)

10 films run 185 minutes, and 45 is the number of pairs you can make from 10 things.

What the second condition is doing

a1.actor_id < a2.actor_id is what keeps each pair once and keeps an actor from pairing with themselves. Take it out and the same query answers something else.

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)

416 rows instead of 108, and 2 kinds of row have crept in. CHRISTIAN AKROYD is paired with himself, because his own row satisfies a1.last_name = a2.last_name. And CHRISTIAN with DEBBIE appears, then DEBBIE with CHRISTIAN, which is the same pair written backwards. Comparing the ids with < fixes both at once. It rules out the row matching itself, because no id is less than itself, and it keeps only 1 arrangement of each genuine pair. Use < whenever a self join is looking for pairs.

Summary

Use a self join to compare the rows of 1 table against each other.
Give the table 2 aliases, because ERROR 1066 is what you get without them.
Compare the 2 aliases’ primary keys with < when you want each pair once.
Expect a row to match itself if you do not rule it out.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How a self join works

The aliases are not optional

Examples

2) Compare rows on a number

What the second condition is doing

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How a self join works

​The aliases are not optional

​Examples

​1) Rows that share a value

​2) Compare rows on a number

​What the second condition is doing

​Summary

​See also

How a self join works

The aliases are not optional

Examples

1) Rows that share a value

2) Compare rows on a number

What the second condition is doing

Summary

See also