A Self Join in SQL is a type of join where a table is joined with itself. It allows you to combine rows from the same table by using different aliases for the same table. Self joins are particularly useful when you need to relate rows within the same table.
The basic syntax for SQL Self Join is:
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Here, T1 and T2 are two aliases for the same table. The WHERE clause specifies the condition to match the rows based on the table’s relationship with itself.
Let’s consider a sample database like the Northwind database. Below is an example from the “Customers” table, where we will match customers who 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;
In this query, we are selecting two customers, CustomerName1 and CustomerName2, who belong to the same city. The WHERE clause ensures that we don’t match a customer with themselves (A.CustomerID <> B.CustomerID) and filters customers by the same City.
Consider a scenario where we want to find employees who work in the same department. If we have an “Employees” table with columns like EmployeeID, EmployeeName, Department, we can use a Self Join to compare employees within the same department.
SELECT A.EmployeeName AS Employee1, B.EmployeeName AS Employee2, A.Department
FROM Employees A, Employees B
WHERE A.EmployeeID <> B.EmployeeID
AND A.Department = B.Department
ORDER BY A.Department;
This query matches two employees who belong to the same department. By using aliases (A and B), we can refer to the same table multiple times and create complex relationships between rows in the same table.
SQL Self Join is used when you need to:
By using a self join, you can avoid redundant tables and perform complex operations within a single table, making your queries more efficient and easier to manage.