login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL Self-join

SQL Self-join

Summary: in this tutorial, you will learn how to use a special join called self-join which allows you to join a table to itself.

SQL self-join simply is a normal join which is used to join a table to itself. The SQL self-join can be done by using SQL table aliases to treat one table like a different table and then join them together. SQL self-join can be any form of join such as SQL inner join and SQL outer join so you can apply any join to the SQL self-join.

Here is common syntax of SQL self-join:

SELECT column_list FROM table_A AS A
INNER JOIN table_A AS B 
  ON A.column_name1 = B.column_name2, ...
WHERE row_conditions

SQL self-join is very useful when you want to retrieve related data storing in one table such as organizational structure. In our sample database, we have employees table which stores not only employee data but also organizational structure. The column REPORTSTO specifies the manager of  an employee and is referenced to EMPLOYEEID column. 

SQL Self-join

In order to display who reports to whom, we can use SQL self-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

Here is the output of the sql self-join query above:

SQL SELF JOIN Query Output

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

Related Tutorials