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. This is 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, and your progress syncs across the web and the CoddyKit app. The SQL Academy course includes 4 lessons in total.
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. 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. The SQL Academy course includes 4 lessons in total.
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, so you can start here or from the beginning and move at your own pace. This is lesson 4 of 4.
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