This SQL tutorial focuses on the SQL Server Self Join statement, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.
This tutorial is a part of several posts describing how to use the JOIN statement in SQL Server. To read additional posts regarding this subject, please use the following links:
- SQL Server Inner JOIN – Joining data items from tables, based on values common to both tables.
- SQL Server Outer JOIN– Joining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table
- SQL Server Self JOIN– Join a table to itself
SQL Server Self JOIN
The SQL Server Self Join allows joining a table to itself. To fully understand this concept, please consider the following example: the table in the illustration below contains information about employees and their managers. If you’ll take a closer look, you’ll notice this table has a certain hierarchy of employees and managers, where a certain employee may also be a manager (for example employee number 3 – Anne, is also the manager of George).
Since the table references data in itself, you can answer questions such as: what is the name of George’s manager? To find out, check what is the manager ID of George’s manager (3), then check to which employee this number corresponds (Anne).
Employee’s manager ID = “Manager’s” employee ID.
On tables of this type you may sometimes want to display data that relies on this relation. You can use this relation to create a query that retrieves the employee’s name and the name of this employee’s manager.
SELECT emp.employee_id , emp.last_name , emp.manager_id ,
mng.last_name
FROM employees emp JOIN employees mng
ON emp.manager_id = mng.employee_id
Because this operation joins the same table to itself, it actually involves referring the same table twice, each time under a different alias (once as “Employees” table, once as “Managers”).
The SQL Server ON keyword is used for defining the relation of the table to itself. In this case, the number of the employee’s manager (“Emp.Manager_ID”) is compared with the employee number of this manager (“Mng.Employee_ID”).
