SQL - Self Join

In SQL Server, a self join is a technique used to join a table with itself. It allows you to combine rows from the same table based on a specified relationship between them. This is useful when you need to query hierarchical data or compare records within the same table.

The basic syntax for performing a self join in SQL Server is as follows:

SELECT column_list
FROM table1 AS t1
JOIN table1 AS t2 ON join_condition;

In this syntax, column_list represents the columns you want to select from the self-joined table. The table is aliased as t1 and t2 to distinguish between the two instances of the table within the join. The join_condition specifies the relationship between the rows in the self-joined table.

The join condition is typically defined using the columns of the table to establish the relationship. For example, if you have a "Employees" table with columns such as "EmployeeID" and "ManagerID," you can join the table with itself using the join condition t1.ManagerID = t2.EmployeeID.

Here's an example to illustrate the usage of a self join:

Consider a table called "Employees" with the following structure:

To retrieve the names of employees along with the names of their managers, you can use a self join as follows:

SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName
FROM Employees e1
JOIN Employees e2 ON e1.Manager = e2.ID;

In the above query, we select the name of the employee from e1 and the name of the manager from e2. We perform a self join on the "Employees" table using the join condition e1.Manager = e2.ID, which establishes the relationship between an employee and their manager based on the ManagerID and EmployeeID columns.

The output of the query would be:

As you can see, the result set includes the names of employees along with the names of their respective managers. The self join allows us to establish the hierarchical relationship between the employees and their managers within the same table.

It's important to note that self joins can be more complex and involve multiple levels of hierarchy, depending on the structure of your data and the relationships you want to explore.

In summary, a self join in SQL Server allows you to join a table with itself, enabling you to retrieve information from different rows within the same table based on a specified relationship. It's commonly used for hierarchical data or when comparing records within the same table.