A self join is a regular join, but the table is joined with itself.
There is no special SELF JOIN keyword. You perform a self join by using a regular JOIN (like INNER JOIN or LEFT JOIN) and listing the same table twice with different table aliases.
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
Or using the JOIN syntax:
SELECT A.column_name, B.column_name... FROM table1 A INNER JOIN table1 B ON A.common_field = B.common_field;
T1 and T2 (or A and B) are different table aliases for the same table. Using aliases is mandatory for a self join to work.
A common use case for a self join is to query hierarchical data (like an employee-manager relationship) or to find pairs of records from the same table.
Let's look at our Customers table.
Customers Table:
| CustomerID | CustomerName | City | Country |
|---|---|---|---|
| 1 | Alfreds Futterkiste | Berlin | Germany |
| 2 | Ana Trujillo Emparedados | México D.F. | Mexico |
| 3 | Antonio Moreno Taquería | México D.F. | Mexico |
| 4 | Berglunds snabbköp | Luleå | Sweden |
The following SQL statement matches customers that are from the same country.
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.Country FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID AND A.Country = B.Country ORDER BY A.Country;
In this query:
Customers with itself, aliasing the two instances as A and B.A.CustomerID <> B.CustomerID ensures that we don't match a customer with themselves.A.Country = B.Country is the join condition that finds customers in the same country.The result would show pairs of customers who share a country, like "Ana Trujillo Emparedados" and "Antonio Moreno Taquería" who are both in "Mexico".