Self-Joins and Aliases
Join a table to itself using aliases to compare rows within the same table (employees and managers, dates and previous dates).
Self-Join: Joining a Table to Itself
Sometimes the relationship is within one table:
- Employees ↔ Managers (manager is an employee)
- Threaded comments (each comment has a parent_id)
- Friend graph (user ↔ user)
The Setup
An employees table with a self-referencing FK:
CREATE TABLE employees (
id BIGSERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
manager_id BIGINT REFERENCES employees(id)
);All lessons in this course
- Why Joins (Relational Model Recap)
- INNER JOIN Mechanics
- LEFT/RIGHT JOIN and OUTER Joins
- Self-Joins and Aliases