SELF JOIN

A self join uses the same table twice with different aliases.

This is common for hierarchical or peer relationships.

Core Concepts

flowchart LR
  A[(employees_self as e)] --> C[Self join on manager_id = employee_id]
  B[(employees_self as m)] --> C
  C --> D[Employee + manager in same row]

Alias	Role
e	Employee row
m	Manager row (same table)

Code Examples

-- Setup table with manager relationship.
CREATE TABLE employees_self (
  employee_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  manager_id  INTEGER
);

INSERT INTO employees_self (employee_id, name, manager_id)
VALUES
  (1, 'Dana', NULL),
  (2, 'Eli', 1),
  (3, 'Fatima', 1);

Expected output
Table created and hierarchy rows inserted.

-- Show employee with manager name using self join.
SELECT
  e.name AS employee,
  m.name AS manager
FROM employees_self AS e
LEFT JOIN employees_self AS m
  ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

employee	manager
Dana	null
Eli	Dana
Fatima	Dana

SQLite-Specific Nuances

SQLite Nuance

Self join behavior in SQLite is standard SQL; aliases are mandatory for readability and unambiguous column references.

Common Pitfalls / Best Practices

Pitfall

Forgetting aliases and ending up with ambiguous column names like name from two table references.

Best Practice

Use meaningful aliases (employee, manager or e, m) and qualify selected columns.

Quick Challenge

Write a query that returns only employees who have a manager.

View Solution

SELECT e.name AS employee, m.name AS manager
FROM employees_self AS e
INNER JOIN employees_self AS m
  ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

employee	manager
Eli	Dana
Fatima	Dana