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       |