Using 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_conditionCode language: SQL (Structured Query Language) (sql)

SQL self join examples

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

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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.