> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL self join

> How to join a MySQL table to itself to compare its own rows, why the query needs 2 aliases, and the condition that stops every pair appearing twice.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/docs/mysql-8.4/stable/quickstart">
  All examples on this page work on VillageSQL. Install Now →
</Card>

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

```sql theme={null}
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.

```sql theme={null}
SELECT first_name FROM actor JOIN actor ON actor.last_name = actor.last_name LIMIT 1;
```

```text theme={null}
ERROR 1066 (42000): Not unique table/alias: 'actor'
```

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

## Examples

### 1) Rows that share a value

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

```sql theme={null}
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;
```

```text theme={null}
+-------------+-------------+-------------+
| 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.

```sql theme={null}
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;
```

```text theme={null}
+--------------------+--------------------+--------+
| 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.

```sql theme={null}
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;
```

```text theme={null}
+-------------+--------------+-------------+
| 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.

## See also

* [Table aliases](/docs/tutorial/table-aliases) — the naming this query depends on
* [INNER JOIN](/docs/tutorial/inner-join) — the join a self join is a special case of
* [How joins work](/docs/tutorial/joins) — where this sits among the other kinds
* [MySQL JOIN performance and common mistakes](/docs/guides/joins) — what a self join costs on a large table