SQL SELF JOIN: The Complete Guide

The self-join allows you to join the table to itself. It helps query the hierarchical data or compare rows within the same table.

SQL Self Join

Self-join in SQL is used to join the table itself means that each table row is combined with itself and with every other table row.

SQL Self Join is the kind of join in which a table is joined with itself means we are joining a table with that same table (also called Unary relationships), especially when the table has the FOREIGN KEY, which references its PRIMARY KEY.

Key Points

1. Self-join occurs when a table takes a ‘selfie.’
2. Self-join is a regular join, but the table is joined with itself.
3. It can be helpful when modeling hierarchies.
4. They are also useful for comparisons within the table.
5. You use the self join when the table references data in itself.
6. One most used example is where you wanted to get a list of employees and their immediate managers.

SQL Self Join

The self-join uses an inner join or left join clause. Because the query that uses the self join references the same table, the table alias is used to assign different names to the same table within the query. The self-join can be viewed as a join of two copies of the same table.

The table is not copied, but SQL performs a command as though it were. For example, the syntax of the command for joining a table to itself is almost the same as that for joining two different tables.

If we want to distinguish the column names from one another, aliases for an actual table name are used since both tables have the same name. Table name aliases are defined in a FROM clause of the SELECT statement.

Syntax

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

In the above syntax, T1 and T2 are different table aliases for the same table.

See the following table.

Products

Example

The following SQL statement matches Products that have different categories.

SELECT A.ProductID, A.ProductName AS ProductNameA, B.ProductName AS ProductNameB, A.Category
FROM Products A, Products B
WHERE A.`ProductName` <> B.`ProductName`
AND A.`Category` = B.`Category`
ORDER BY B.`ProductName`;

See the following output.

In the above query, we have used a WHERE condition and ORDER BY clause.

That’s it for this tutorial.