SQL Self Join – Summary

• A self join in SQL is a regular join where a table is joined with itself. This is useful when you want to compare rows within the same table or look for relationships among rows in a single table.
• Syntax Example:

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

  Here, T1 and T2 are aliases referring to the same table.
• Example Use Case:
  The example provided matches customers from the same city in the "Customers" table (Northwind database) and lists them side by side, without matching the same customer (i.e., ensuring A.CustomerID <> B.CustomerID and A.City = B.City).
• Purpose:
  A self join is used to join and compare rows within the same table (such as finding pairs of customers in the same location).

The page also provides an exercise to verify understanding:
"What is the purpose of a self join in SQL?"
The correct answer is: To join a table with itself.[1]

1

SQL Self Join

SQL Self Join

A self join is a regular join, but the table is joined with itself.

Self Join Syntax

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

T1 and T2 are different table aliases for the same table.

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the "Customers" table:

SQL Self Join Example

The following SQL statement matches customers that are from the same city:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City  
FROM Customers A, Customers B  
WHERE A.CustomerID <> B.CustomerID  
AND A.City = B.City  
ORDER BY A.City;