SQL Self Join to Join a Table to Itself

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

Introduction to SQL self join

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
Code language: PHP (php)

SQL self join examples

We will use the employees table in the sample database for the demonstration.

employees table

The employees table stores not only employee data but also organizational structure. The reportsto column specifies the manager of an employee and refers to the employeeid column in the same table.

The following illustrates the data in the employees table including employee id, last name, first name and reports to:

SELECT employeeid, lastname, firstname, reportsto FROM employees;
employees data

SQL self join with inner join example

SQL self join is very useful when you want to select related data stored in one table such as organizational structure. For example, to display who reports to whom, you can join the employees table to itself as the following query:

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;
employee manager hierarchy

SQL self join with left join example

The top manager i.e., the CEO, does not report to anyone in the company, therefore, the reportTo column contains the NULL value. To query the whole organization structure including the CEO, you need to use the  LEFT JOIN clause rather than the  INNER JOIN clause as the following query:

SELECT concat(e.firstname, e.lastname) employee, concat(m.firstname, m.lastname) manager FROM employees e LEFT JOIN employees m ON m.employeeid = e.reportsto ORDER BY manager;
SQL self join with LEFT JOIN employee manager hierarchy example

You can compare a row with other rows in the employees table by any column by using self join. For example, the following query finds employees who locate in the same city.

SELECT e1.firstname, e2.firstname, e1.city FROM employees e1 INNER JOIN employees e2 ON e2.city = e1.city WHERE e1.employeeid <> e2.employeeid ORDER BY e1.city , e1.firstname;
Code language: HTML, XML (xml)
employees locate in the same city

To find employees who locate in the same city as Anne, you add a condition to the  WHERE clause  of the above query as follows:

SELECT e1.firstname, e2.firstname, e1.city FROM employees e1 INNER JOIN employees e2 ON e2.city = e1.city WHERE e1.employeeid <> e2.employeeid AND e1.firstname = 'Anne' ORDER BY e1.city , e1.firstname;
Code language: HTML, XML (xml)
employees locate in the same city as Anne

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