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
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
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;