Home / SQL Tutorial / SQL Self Join

SQL Self Join

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.

SQL self join can be any kind of join such as SQL inner join and SQL outer join therefore you can use any kind of join to join a table to itself.

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.

SQL Self Join - Employees 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

SQL SELF JOIN Query Output

In this tutorial, you have learned how to use SQL self join to join a table to itself.