SQL Server Self Join

In this tutorial, you will learn how to use a special kind of join called SQL self Join to join a table to itself.

SQL self join is used to join or compare a table to itself. SQL self joins are used to compare values of a column with values of another column in the same table.
To construct a self join, you select from the same table twice by using the SELECT statement with an inner join or outer join clause. Because you refer to the same table twice in the same statement, you have to use table aliases. The following illustrates the syntax of a self join:

SELECT 
    column1, column2,...
FROM
    table AS A
(LEFT | INNER) JOIN
    table AS B ON join_condition

he  staffs table stores the staff information such as id, first name, last name, and email. It also has a column named manager_id that specifies the direct manager. For example, Mireya reports to Fabiola because the value in the manager_id of  Mireya is Fabiola.

Fabiola has no manager so the manager id column has a NULL.

To get who reports to whom, you use the self join as shown in the following query:

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;