Explaining self-join in SQL

The aim of this page📝 is to explain self-joins in SQL and how they can be used effectively.

• Definition: A self-join is a regular join but the table is joined with itself.
• Purpose: It is used to compare rows within the same table or to create a hierarchical structure.
• Example Table: A healthcare_providers table with columns name, id, and supervisor_id.
• Basic SQL Query:

 SELECT * FROM healthcare_providers; 

• Data Representation: Example data:

 ┏━━━━━━━━━┳━━━━┳━━━━━━━━━━━━━━━┓ ┃ name ┃ id ┃ supervisor_id ┃ ┡━━━━━━━━━╇━━━━╇━━━━━━━━━━━━━━━┩ │ Alice │ 2 │ 1 │ │ Bob │ 3 │ 1 │ │ Carol │ 4 │ 5 │ │ David │ 5 │ NULL │ │ Eve │ 1 │ 5 │ └─────────┴────┴───────────────┘ 

• Self-Join Query:

 SELECT e.name AS employee, s.name AS reports_to FROM healthcare_providers e LEFT JOIN healthcare_providers s ON e.supervisor_id = s.id ORDER BY s.name NULLS LAST; 

• Explanation:
  • Aliases: e represents employees, s represents supervisors.
  • LEFT JOIN: Ensures all employees are listed, even if they don't have supervisors.
  • ON Clause: Links supervisor_id from e to id in s.
  • SELECT Clause: Retrieves employee names and their respective supervisor names.
  • ORDER BY: Sorts the result by supervisor names, keeping null values at the end.
• Result:

 | employee | reports_to | |----------|------------| | Alice | Eve | | Bob | Eve | | Carol | David | | Eve | David | | David | NULL |