sql self referencing relationship with best example
- User
sql self referencing relationship with best example
- 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:
EmployeeID Name ManagerID 1 Alice CEO NULL 2 Bob CTO 1 3 Carol CFO 1 4 David Dev1 2 5 Eve Dev2 2 6 Frank 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:
Hierarchy EmployeeID ManagerID Alice CEO 1 NULL Bob CTO 2 1 David Dev1 | 4 | 2 Eve Dev2 | 5 | 2Carol 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:
- Define a foreign key that points back to the same table.
- Insert your hierarchical data.
- Use self-joins or recursive CTEs to traverse the hierarchy.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.