SQL SELF JOIN

This tutorial shows you how to join table to itself by using the SQL self-join technique.

Introduction to SQL self-join

Sometimes, it is useful to join a table to itself. This type of join is known as the self-join.

We join a table to itself to evaluate the rows with other rows in the same table. To perform the self-join, we use either an inner join or left join clause.

Because the same table appears twice in a single query, we have to use the table aliases. The following statement illustrates how to join a table to itself.

SELECT
    column1,
    column2,
    column3,
        ...
FROM
    table1 A
INNER JOIN table1 B ON B.column1 = A.column2;

In this statement joins the table1 to itself using an INNER JOIN clause. A and B are the table aliases of the table1. The B.column1 = A.column2 is the join condition.

Besides the INNER JOIN clause, you can use the LEFT JOIN clause.

Let’s take few examples of using the self-join technique.

SQL self-join examples

See the following employees table.

The manager_id column specifies the manager of an employee. The following statement joins the employees table to itself to query the information of who reports to whom.

SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM
    employees e
        INNER JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;

The president does not have any manager. In the employees table, the manager_id of the row that contains the president is NULL.

Because the inner join clause only includes the rows that have matching rows in the other table, therefore the president did not show up in the result set of the query above.

To include the president in the result set, we use the LEFT JOIN clause instead of the INNER JOIN clause as the following query.

SELECT 
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM
    employees e
        LEFT JOIN
    employees m ON m.employee_id = e.manager_id
ORDER BY manager;