SQL Self-Join

Summary: in this tutorial, you’ll how to compare rows within the same table using the SQL self-join technique.

Introduction to SQL self-join #

Typically, you use a join such as inner join, left join, and right join to merge rows from two tables based on a condition.

However, a join doesn’t have to involve multiple tables. You can use a join to compare rows within the same table. In this case, you join a table to itself that forms a self-join.

A self-join is a join that compares the rows within the same table. A self-join uses an inner join, left join, or right join that joins a table to itself. It uses table aliases to treat the same table as separate tables within the same query.

Here’s the basic syntax of a self-join:

SELECT
  select_list
FROM
  table1 t1
  INNER JOIN table1 AS t2 ON t1.column1 = t2.column2;Code language: SQL (Structured Query Language) (sql)

In this syntax, you can use the LEFT JOIN, RIGHT JOIN, and FULL JOIN instead of the INNER JOIN.

SQL self-join example #

We’ll use the employees table from the HR sample database:

In the employees table:

The employee_id serves as a unique identifier for each employee.
The manager_id represents the employee_id of the manager to whom the current employee reports. If the manager_id is NULL, it means the employee is the CEO, without a manager.

The following query uses an inner join to join the employees table to itself to get the information of who reports to whom:

SELECT
  e.first_name employee,
  m.first_name manager
FROM
  employees e
  LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY
  manager NULLS FIRST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

  employee   |  manager
-------------+-----------
 Valli       | Alexander
 Diana       | Alexander
 Bruce       | Alexander
 David       | Alexander
 Guy         | Den
 Karen       | Den
 Alexander   | Den
 Shelli      | Den
 Sigal       | Den
 Alexander   | Lex
 Irene       | Matthew
...Code language: plaintext (plaintext)

Since the inner join clause only includes the rows with matching rows in the other table, the query does not include the CEO in the result set of the query.

To include the CEO in the result set, you can use a LEFT JOIN clause instead of the INNER JOIN clause.

Performing a self-join using a LEFT JOIN clause #

The following statement performs a self-join using a LEFT JOIN clause:

SELECT
  e.first_name employee,
  m.first_name manager
FROM
  employees e
  LEFT JOIN employees m ON m.employee_id = e.manager_id
ORDER BY
  manager NULLS FIRST;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

  employee   |  manager
-------------+-----------
 Steven      | NULL
 Bruce       | Alexander
 David       | Alexander
 Valli       | Alexander
 Diana       | Alexander
 Sigal       | Den
 Guy         | Den
 Alexander   | Den
 Shelli      | Den
 Karen       | Den
 Alexander   | Lex
 Irene       | Matthew
...Code language: plaintext (plaintext)

SQL SELF JOIN

Introduction to SQL self-join #

SQL self-join example #

Performing a self-join using a LEFT JOIN clause #

Summary #

Quiz #

Databases #