Description

SQL Self Join is a regular join without a JOIN clause, where the table is joined with itself.

  • A table is joined with itself without using a JOIN clause.
  • It must need different table aliases for the same table name, to represent each instance of the table.

Syntax

Here is the basic syntax of a SELECT statement with a self-join.

  • The table1 and table2 represents the first and second table names.
  • The T1 and T2 represents the table aliases for the same table, that can be used to refer to columns on the respective table instance.
SELECT column_list
FROM table1 T1, table1 T2
WHERE conditions;

Table Data

Consider the below data in the customers tables.

customer_id customer_name address  city country postal_code
1 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
2 Dominique Perrier 25, rue Lauriston Paris France 75016
3 Christina Aguilera Gran Va, 1 Madrid Spain 28001
4 Pedro Afonso Av. dos Lusadas, 23 Sao Paulo Brazil 05432-043
5 Aria Cruz Rua Ors, 92 Sao Paulo Brazil 05442-030
6 Diego Roel C/ Moralzarzal, 86 Madrid Spain 28034

Self Join

The below SQL statement fetches customers from the same city, with all the possible combinations.

  • In case there are more than two customers in the same city, then the result set contains all the possible combinations of such customers.

Run this on IDE

SELECT a.customer_name as customer_name1, b.customer_name as customer_name2, a.city
FROM customers a, customers b
WHERE a.customer_id <> b.customer_id
AND a.city = b.city
ORDER BY a.city;

After successful execution, the output contains all the possible combinations of the customers from the same city.

customer_name1 customer_name2 city
Fran Wilson Christina Aguilera Madrid
Fran Wilson Diego Roel Madrid
Christina Aguilera Fran Wilson Madrid
Christina Aguilera Diego Roel Madrid
Diego Roel Fran Wilson Madrid
Diego Roel Christina Aguilera Madrid
Pedro Afonso Aria Cruz Sao Paulo
Aria Cruz Pedro Afonso Sao Paulo

Overall

We now know how can we use a SELECT Statement with a self join on the same table.

Related Links