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-Joins and Aliases is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
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)
);Joining Employees to Their Managers
You give each instance a different alias:
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id;Without Aliases It Wouldn't Compile
Without aliases you can't tell which full_name you mean. Every reference is ambiguous:
-- ERROR: column reference "full_name" is ambiguous
SELECT full_name FROM employees JOIN employees ON ...LEFT JOIN for Top-Level Employees
An INNER join drops employees who have no manager (e.g. the CEO). Use LEFT JOIN to keep them:
SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;Threaded Comments
Show each comment with its parent's text:
SELECT c.id, c.body, p.body AS parent_body
FROM comments c
LEFT JOIN comments p ON p.id = c.parent_id;Pairs and Combinations
Find pairs of products that appeared in the same order (without duplicate (A,B) and (B,A) pairs):
SELECT a.id AS product_a, b.id AS product_b
FROM order_items a
JOIN order_items b
ON a.order_id = b.order_id
AND a.product_id < b.product_id;Comparing Adjacent Rows
Self-join on a row's "previous" version:
SELECT a.id, a.created_at, b.created_at AS prev_at
FROM events a
LEFT JOIN events b ON b.id = a.previous_id;Self-Join vs Window Function
For many "compare each row to the previous one" tasks, a window function (LAG) is simpler and faster than a self-join. Covered in Window Functions Deep Dive.
Recursive Self-Joins
Need to walk N levels deep (org chart, thread tree)? Use a recursive CTE — covered in Subqueries and CTEs.
Indexing the FK Column
Always add an index on the self-referencing FK column. Without it, the join scans the whole table for every parent lookup.
CREATE INDEX employees_manager_idx ON employees(manager_id);Recap
Self-joins handle relationships inside one table.
- Two aliases for two virtual copies
- LEFT JOIN to keep roots / top-level rows
- Index the FK column
- Window functions can simplify "previous row" patterns
Quick Check
You join employees to itself to find each employee's manager. Why are aliases required?
Frequently asked questions
Is the “Self-Joins and Aliases” lesson free?
Yes — the full text of “Self-Joins and Aliases” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “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). You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Self-Joins and Aliases” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Why Joins (Relational Model Recap)
- INNER JOIN Mechanics
- LEFT/RIGHT JOIN and OUTER Joins
- Self-Joins and Aliases