Summary: in this tutorial, you will learn how to use a special join called SQL self Join that allows you to join a table to itself.
Introduction to SQL Self Join
SQL self join is used to join a table to itself. The SQL self join can be done by using SQL table aliases that treats one table like a different table and join them together.
The following illustrates the typical syntax of SQL self join:
SELECT column1, column2 FROM table_A AS A [INNER|LEFT|RIGHT] JOIN table_A AS B ON join_condition WHERE row_condition
SQL Self Join example
SQL self join is very useful when you want to select related data storing in one table such as organizational structure.
In the sample database, we have employees table that stores not only employee data but also organizational structure. The ReportsTo column specifies the manager of an employee and it refer to EmployeeID column in the same table.
To display who reports to whom, we can use SQL self join with inner join as follows:
SELECT concat(e.firstname,e.lastname) employee, concat(m.firstname,m.lastname) manager FROM employees e INNER JOIN employees m ON m.employeeId = e.reportsTo
In this tutorial, you have learned how to use SQL self join to join a table to itself.