SQL - Self Join

Self Join allows you to join a table to itself. It is useful for querying hierarchical data or comparing rows within the same table.

Self Join uses the inner join or left join clause. Because the query that uses self join references the same table, the table alias is used to assign different names to the same table within the query.

Example 1

--SQL self join with inner join example

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;

--SQL self join with left join example

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;

--Compare a row with other rows in the employees table by any column by using self join

SELECT

    e1.FIRSTNAME, e2.FIRSTNAME, e1.CITY

FROM

    EMPLOYEES e1

INNER JOIN

    --Condition matches the city of the two customers

    EMPLOYEES e2 ON e2.CITY = e1.CITY

WHERE

    e1.EMPLOYEEID <> e2.EMPLOYEEID

ORDER BY

    e1.CITY , e1.FIRSTNAME;

--To find employees who locate in the same city as Anne, you add a condition to the  WHERE clause

SELECT

    e1.FIRSTNAME, e2.FIRSTNAME, e1.CITY

FROM

    EMPLOYEES e1

INNER JOIN

    --Condition matches the city of the two customers

    EMPLOYEES e2 ON e2.CITY = e1.CITY

WHERE

    e1.EMPLOYEEID <> e2.EMPLOYEEID AND

    e1.FIRSTNAME = 'Anne'  

ORDER BY

    e1.CITY , e1.FIRSTNAME;

  

--Finds all employees who have the same hire dates

SELECT

   e1.Hire_Date,

  (e1.First_Name || ' ' || e1.Last_Name) Employee1,

  (e2.First_Name || ' ' || e2.Last_Name) Employee2 

FROM

    Employees e1

INNER JOIN employees e2 ON
    --Makes sure that the statement doesn’t compare the same customer

    e1.Employee_Id > e2.Employee_Id 

    AND e1.Hire_Date = e2.Hire_Date

ORDER BY 

   e1.Hire_Date DESC,

   Employee1,

   Employee2;