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

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

๐Ÿ‘‹ Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay