MySQL Self Join

A self join is a type of join where a table is joined with itself. In this case, the table is treated as two different tables, with different aliases for each instance of the table. It allows us to query hierarchical relationships or compare rows within the same table.

Self Join Syntax

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

• T1 and T2 are aliases representing two instances of the same table.
• The condition typically compares the rows within the same table (using T1.column_name and T2.column_name).

Self Join Example

Suppose you have a Customers table, and you want to find customers who are from the same city. You would use a self join to compare rows within the same table.

Here’s an example SQL statement that matches customers 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;

Explanation:

• A and B are aliases for the Customers table. The table is joined with itself.
• The A.CustomerID <> B.CustomerID condition ensures that a customer is not matched with themselves.
• The A.City = B.City condition matches customers who are from the same city.
• The ORDER BY A.City sorts the result by city name.

Result:

This query shows pairs of customers who are from the same city.

When to Use a Self Join

• Hierarchical data: When you need to compare rows within the same table, such as employees reporting to other employees.
• Finding relationships between rows: For example, identifying products that are related, customers who have the same address, etc.