Skip to content
The Chat Archive
o4-mini-2025-04-16· OpenAIHugging Face LMArena Human Preference 140K

sql self referencing relationship with best example

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Hunyuanhunyuan-turbos-20250416’s answer.
  1. User

    sql self referencing relationship with best example

  2. Assistant

    A self-referencing relationship (or self-join) happens when a table’s foreign key points back to its own primary key. This is most often used to model hierarchies (e.g. employees & managers, categories & sub-categories, org chart, bill of materials, etc.).

    Below is one of the most classic examples—an Employees table where each employee can have a “manager” who is also an employee.


    1. DDL: Create the table

    CREATE TABLE Employees (
      EmployeeID   INT          PRIMARY KEY,
      Name         VARCHAR(100) NOT NULL,
      ManagerID    INT          NULL,
      HireDate     DATE         NOT NULL,
      -- Self-reference: ManagerID points to EmployeeID in the same table
      FOREIGN KEY (ManagerID)
        REFERENCES Employees(EmployeeID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
    );
    
    • EmployeeID is the PK.
    • ManagerID is the FK referencing EmployeeID in the same table.
    • We allow it to be NULL (e.g. the CEO has no manager).

    2. Sample data

    INSERT INTO Employees (EmployeeID, Name, ManagerID,   HireDate) VALUES
     (1, 'Alice CEO',     NULL,        '2015-01-01'),
     (2, 'Bob CTO',       1,           '2016-03-15'),
     (3, 'Carol CFO',     1,           '2016-03-20'),
     (4, 'David Dev1',    2,           '2017-07-01'),
     (5, 'Eve Dev2',      2,           '2018-02-12'),
     (6, 'Frank Finance', 3,           '2019-11-05');
    

    Rendered as a tree:

    EmployeeIDNameManagerID
    1Alice CEONULL
    2Bob CTO1
    3Carol CFO1
    4David Dev12
    5Eve Dev22
    6Frank Fin.3

    3. Querying the hierarchy

    3.1. Simple self-join (one level deep)

    Get each employee with their manager’s name:

    SELECT 
      e.EmployeeID,
      e.Name           AS Employee,
      m.Name           AS Manager
    FROM Employees e
    LEFT JOIN Employees m
      ON e.ManagerID = m.EmployeeID
    ORDER BY e.EmployeeID;
    

    3.2. Recursive CTE (all levels)

    List everyone under Alice (ID = 1), showing hierarchy depth:

    WITH RECURSIVE OrgChart AS (
      -- Anchor member: start with CEO
      SELECT
        EmployeeID,
        Name,
        ManagerID,
        0  AS Level
      FROM Employees
      WHERE EmployeeID = 1
    
      UNION ALL
    
      -- Recursive member: find direct reports of the current level
      SELECT
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        oc.Level + 1
      FROM Employees e
      INNER JOIN OrgChart oc
        ON e.ManagerID = oc.EmployeeID
    )
    SELECT
      REPEAT('  ', Level) + Name AS Hierarchy,
      EmployeeID,
      ManagerID
    FROM OrgChart
    ORDER BY Level, EmployeeID;
    

    Result:

    HierarchyEmployeeIDManagerID
    Alice CEO1NULL
    Bob CTO21
    David Dev1   | 4          | 2  
    Eve Dev2     | 5          | 2  
    

    Carol CFO | 3 | 1
    Frank Finance| 6 | 3


    4. Another common example: Categories

    CREATE TABLE Categories (
      CategoryID       INT PRIMARY KEY,
      CategoryName     VARCHAR(100) NOT NULL,
      ParentCategoryID INT NULL,
      FOREIGN KEY (ParentCategoryID)
        REFERENCES Categories(CategoryID)
        ON DELETE CASCADE
    );
    
    INSERT INTO Categories VALUES
     (1, 'Electronics', NULL),
     (2, 'Computers',   1),
     (3, 'Laptops',     2),
     (4, 'Desktops',    2),
     (5, 'Cameras',     1);
    

    Again you can use a recursive CTE to build the full category tree.


    👍 That’s the essence of a self-referencing relationship in SQL:

    1. Define a foreign key that points back to the same table.
    2. Insert your hierarchical data.
    3. Use self-joins or recursive CTEs to traverse the hierarchy.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.