SQL Self Join
A self join is a regular join in which a table is joined to itself so you can compare rows within the same table.
What a self join does
A self join is not a separate keyword or join type. It is any join where both sides of the join refer to the same table. Because you cannot use the same table name twice without confusion, you give the table two different aliases and treat them as if they were two independent tables. This lets you compare one row against another row from the very same table.
For example, you might want to find pairs of customers who live in the same country, or, in an orders context, pairs of orders placed by the same customer. The two aliases act as two copies of the table, and the ON clause states how the copies relate.
Pairs of customers in the same country
SELECT A.CustomerName AS Customer1,
B.CustomerName AS Customer2,
A.Country
FROM Customers AS A
INNER JOIN Customers AS B
ON A.Country = B.Country
WHERE A.CustomerID < B.CustomerID;Self joins for hierarchies
Self joins shine when a table references itself. Imagine each order can list a related PreviousOrderID pointing to an earlier order by the same customer. A self join can line up each order with the order it follows, pulling both sets of details into one row.
Linking each order to the one before it
SELECT curr.OrderID AS ThisOrder,
prev.OrderID AS PreviousOrder,
curr.Amount AS ThisAmount
FROM Orders AS curr
INNER JOIN Orders AS prev
ON curr.PreviousOrderID = prev.OrderID;The same table appears twice under the aliases curr and prev. The join condition connects each current order to the earlier order it references, so a single result row can display information from both.
- A self join always requires table aliases to tell the two copies apart.
- Any join type can be used; INNER and LEFT self joins are the most common.
- Add a condition such as A.id < B.id to avoid duplicate or self-matched pairs.
- Self joins are ideal for comparing rows within one table or walking simple hierarchies.
