DEV Community

Pavol Z. Kutaj
Pavol Z. Kutaj

Posted on

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;
Enter fullscreen mode Exit fullscreen mode
  • Data Representation: Example data:
  โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”ณโ”โ”โ”โ”โ”ณโ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”“
  โ”ƒ name    โ”ƒ id โ”ƒ supervisor_id โ”ƒ
  โ”กโ”โ”โ”โ”โ”โ”โ”โ”โ”โ•‡โ”โ”โ”โ”โ•‡โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”โ”ฉ
  โ”‚ Alice   โ”‚  2 โ”‚          1    โ”‚
  โ”‚ Bob     โ”‚  3 โ”‚          1    โ”‚
  โ”‚ Carol   โ”‚  4 โ”‚          5    โ”‚
  โ”‚ David   โ”‚  5 โ”‚          NULL โ”‚
  โ”‚ Eve     โ”‚  1 โ”‚          5    โ”‚
  โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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       |
Enter fullscreen mode Exit fullscreen mode
๐Ÿ‘‹ Kindness is contagious

Please leave your appreciation by commenting on this post!

It takes one minute and is worth it for your career.

Get started

Thank you! ๐Ÿ™

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

๐Ÿ‘‹ Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay