SELF JOIN IN SQL
Introduction
You can link a table to itself using a self join. It makes it easier to compare rows inside the same database or query hierarchical data.
The inner join or left join clause is used in a self join. The table is referenced by the self join query, which employs a table alias to give different aliases to the same table within the query.
It should be noted that using table aliases to reference the same table more than once may cause an error.
Syntax
The syntax for linking table T to itself is demonstrated by the following
SELECT select_list FROM T t1 [INNER | LEFT] JOIN T t2 ON join_predicate; |
The query makes two references to table T. The T table is given several names in the query via the usage of the table aliases t1 and t2.
Examples of SQL Server self join
To further understand how the self join functions, let's look into some instances.
Self join can be used to query hierarchical data
Take a look at the staff's table below from the sample database:
Information on the staff, including ID, first and last names, and email addresses, is kept in the staff's table. Additionally, a field with the name manager id lists the direct manager. For instance, Mireya reports to Fabiola because Fabiola is the value for Mireya's manager id.
Since Fabiola doesn't have a manager, the manager id column is NULL.
The self join is used to determine who reports to whom, as demonstrated by the following example:
SELECT e.first_name + ' ' + e.last_name employee, m.first_name + ' ' + m.last_name manager FROM sales.staffs e INNER JOIN sales.staffs m ON m.staff_id = e.manager_id ORDER BY manager; |
In this example, the staffs table was mentioned twice: once as e for employees and once as m for managers. The values in the e.manager id and m.staff id columns are used in the join predicate to match employee and manager relationships.
Due to the INNER JOIN effect, Fabiola Jackson is not present in the employee column. The following query will get a result set that contains Fabiola Jackson in the employee column if the INNER JOIN clause is swapped out for the LEFT JOIN clause:
SELECT e.first_name + ' ' + e.last_name employee, m.first_name + ' ' + m.last_name manager FROM sales.staffs e LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id ORDER BY manager; |
Conclusion
You have learned how to query hierarchical data and compare rows in the same table using a SQL Server self join in this article.