SQL SELF JOIN

A SELF JOIN is not a special SQL keyword — it’s simply a regular join where a table is joined to itself. You give the same table two different aliases so the database engine treats each alias as an independent copy of the table for the length of the query, letting you compare rows in a table against other rows from that very same table. This is the standard technique for querying hierarchical data (like an employees table whose manager_id column points back to another row’s primary key) and for finding relationships between rows — pairs, duplicates, or comparisons — that a join between two different tables can’t express. By the end of this lesson you’ll be able to write self joins for hierarchies, peer comparisons, and value comparisons, and know how to avoid the two mistakes almost everyone makes the first time.

Overview: How a Self Join Works

Every JOIN pairs rows from one row source with rows from another row source based on a matching condition. Normally those two row sources are two different tables — orders matched to customers, for example. A self join uses the same table as both row sources. Because referring to the table’s name twice in one query would be ambiguous — the engine wouldn’t know which “copy” a bare column name belongs to — you must alias the table twice, for example employees a and employees b. From that point on, a and b behave as two completely separate row sources, even though they’re reading from the same underlying data.

Internally, the query planner doesn’t know or care that both row sources happen to come from the same table file. It plans and executes a self join exactly like it would any other join: it prepares each row source (using an index if one exists on the join column), evaluates the ON condition for candidate row pairs, and applies the join type — INNER JOIN, LEFT JOIN, and so on — to decide which pairs (and which unmatched rows) survive into the result set. If the join column is indexed — a foreign key like manager_id, or the primary key it references — the engine can look up matches directly instead of comparing every row of a against every row of b.

Self joins show up constantly in real schemas:

• Hierarchies / adjacency lists — an employee row pointing to its manager’s row, a category pointing to its parent category, a comment pointing to the comment it replies to.
• Peer comparisons — finding two rows that share an attribute, like coworkers in the same department or products in the same category.
• Value comparisons — comparing a row to another row by some measure, like which product costs more than another in the same category.
• Duplicate detection — finding two different rows (different primary keys) that share the same value in some other column.

Syntax

The general shape is a normal join where both sides name the same table under different aliases:

SELECT a.column1, b.column2
FROM table_name AS a
JOIN table_name AS b
  ON a.linking_column = b.linking_column
WHERE a.id_column <> b.id_column;

Part	Meaning
table_name AS a / table_name AS b	The same table given two different aliases, so the engine — and you — can tell the two “copies” apart
ON a.linking_column = b.linking_column	The condition that relates a row in one copy to a row in the other, e.g. a.manager_id = b.employee_id
Join type	INNER JOIN (the default for JOIN) keeps only matched pairs; LEFT JOIN keeps every row from a even when there’s no match in b
WHERE a.id_column <> b.id_column	Stops a row from being paired with itself; use < instead of <> when you want each unordered pair only once (more on this below)

Here’s that pattern as a small, runnable example. Each person optionally has a friend_id pointing at another row in the same table:

CREATE TABLE people (
  person_id INTEGER PRIMARY KEY,
  person_name TEXT,
  friend_id INTEGER
);

INSERT INTO people (person_id, person_name, friend_id) VALUES
  (1, 'Sam', 2),
  (2, 'Tia', 3),
  (3, 'Ravi', NULL);

SELECT a.person_name AS person, b.person_name AS friend
FROM people a
JOIN people b ON a.friend_id = b.person_id;

Result:

person	friend
Sam	Tia
Tia	Ravi

Ravi never appears in the person column, because his friend_id is NULL — there’s nothing for it to match on the b side, so INNER JOIN drops that row entirely.

Examples

Example 1: A Hierarchy — Employees and Their Managers

The classic self join is an adjacency-list hierarchy, where each row can point to another row in the same table as its “parent.” Here, staff.manager_id refers back to another row’s staff_id:

CREATE TABLE staff (
  staff_id INTEGER PRIMARY KEY,
  staff_name TEXT,
  manager_id INTEGER
);

INSERT INTO staff (staff_id, staff_name, manager_id) VALUES
  (1, 'Alice', NULL),
  (2, 'Bob', 1),
  (3, 'Carol', 1),
  (4, 'Dave', 2),
  (5, 'Eve', 2);

SELECT e.staff_name AS employee, m.staff_name AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.staff_id
ORDER BY e.staff_id;

Result:

employee	manager
Alice	NULL
Bob	Alice
Carol	Alice
Dave	Bob
Eve	Bob

The alias e stands for “employee side,” and m for “manager side” — both read from staff. A LEFT JOIN is used deliberately: Alice has no manager (manager_id is NULL), so with an INNER JOIN her row would disappear entirely. The LEFT JOIN keeps her row and simply shows NULL for manager.

Example 2: Finding Coworkers in the Same Department

This example uses this course’s shared sample employees table (id, name, department, salary, hire_date), which has Alice and Carol in Engineering, Bob in Sales, and Dave in Marketing. To list pairs of employees who share a department — without pairing anyone with themselves, and without listing each pair twice — self join on department and add a.id < b.id:

SELECT a.name AS employee_1, b.name AS employee_2, a.department
FROM employees a
JOIN employees b ON a.department = b.department AND a.id < b.id
ORDER BY a.department, a.id, b.id;

Result:

employee_1	employee_2	department
Alice	Carol	Engineering

Only one row comes back. Alice (id 1) and Carol (id 3) are the only two employees who share a department, so they’re the only pair that satisfies a.department = b.department. Bob (Sales) and Dave (Marketing) are each alone in their department, so they never find a partner and — because this is an INNER JOIN — they’re simply absent from the result, not shown with a blank partner.

Example 3: Comparing Prices Within a Category

Self joins are also useful for comparing values row-to-row. Using the shared products table (product_id, product_name, price, category), which holds Widget in Hardware, and Gadget and Gizmo both in Electronics, this query finds, for every product, which other products in the same category cost less:

SELECT a.product_name AS pricier_item, b.product_name AS cheaper_item,
       a.category, ROUND(a.price - b.price, 2) AS price_difference
FROM products a
JOIN products b ON a.category = b.category AND a.price > b.price
ORDER BY a.category, price_difference;

Result:

pricier_item	cheaper_item	category	price_difference
Gadget	Gizmo	Electronics	5.49

Widget is alone in Hardware, so it never appears — there’s no other Hardware row to compare it to. Gadget (19.99) is more expensive than Gizmo (14.50) in Electronics, so that pair satisfies a.price > b.price and shows up with the price difference computed directly in the SELECT list.

How It Works Step by Step (Under the Hood)

Take Example 2’s query and walk through SQL’s logical processing order:

1. FROM — the engine sets up two row sources, both reading from employees, labeled a and b. Conceptually this is a cross product: every row of a paired with every row of b (4 × 4 = 16 candidate pairs for a 4-row table).
2. ON — the join condition a.department = b.department AND a.id < b.id is evaluated for each candidate pair, keeping only the ones where both parts are true. Out of 16 candidate pairs, only (Alice, Carol) survives.
3. JOIN type — since this is an INNER JOIN, rows from a that matched nothing (Bob, Dave) are simply dropped, not padded with NULLs the way a LEFT JOIN would pad them.
4. SELECT — the surviving pair’s columns are projected into employee_1, employee_2, and department.
5. ORDER BY — the result row is sorted last, after everything above has already determined which rows exist.

In practice, SQLite doesn’t literally build all 16 pairs and then filter — if department or id is indexed, it can jump straight to matching rows. But logically, thinking of a self join as “cross product, then filter by the ON condition” is exactly the right mental model, and it’s why the ON condition is what determines whether you get a hierarchy, a peer list, or an accidental explosion of rows.

Common Mistakes

Mistake 1: Forgetting to Alias the Table

If you join a table to itself but reference a column without qualifying which copy it comes from, the engine can’t tell which instance you mean:

SELECT name, department
FROM employees
JOIN employees ON department = department;

This fails with an ambiguous column name error — there are now two unaliased row sources both called employees, and name and department could each refer to either one’s column. The fix is always to alias both sides and qualify every column reference:

SELECT a.name AS employee_1, b.name AS employee_2, a.department
FROM employees a
JOIN employees b ON a.department = b.department AND a.id < b.id;

Mistake 2: Using <> Instead of < When Finding Pairs

It’s tempting to prevent an employee from being paired with themselves using a.id <> b.id. It works, but not the way most people expect:

SELECT a.name AS employee_1, b.name AS employee_2, a.department
FROM employees a
JOIN employees b ON a.department = b.department AND a.id <> b.id
ORDER BY a.department, a.id, b.id;

Result:

employee_1	employee_2	department
Alice	Carol	Engineering
Carol	Alice	Engineering

The same pair now appears twice, once in each direction, because <> is satisfied by both (1, 3) and (3, 1). This is technically correct SQL — it just isn’t what you usually want when the pair is unordered (e.g. “who are coworkers” doesn’t care which name is listed first). Swapping in a.id < b.id, as in Example 2, keeps only one direction of each pair.

Best Practices

• Always alias both instances of the table so every column reference is unambiguous — many databases will refuse to run an unaliased self join at all.
• Use INNER JOIN when you only want matched pairs, and LEFT JOIN when you need to keep rows that have no match, such as a top-level row with no parent.
• Use a.id < b.id (not a.id <> b.id) whenever you’re looking for unordered pairs, to avoid double-counting each relationship in both directions.
• Once the join’s purpose is clear from context, give the aliases meaningful names (employee/manager) instead of bare a/b — it makes the query much easier to read months later.
• Index the linking column (a foreign key like manager_id, or a column you frequently self join on, like department) so large tables don’t force a full scan-and-compare for every row pair.
• For hierarchies deeper than one or two levels, prefer a recursive CTE (WITH RECURSIVE) over chaining multiple self joins — it scales to any depth without adding a new join for every level.

Practice Exercises

Exercise 1: Using the staff table from Example 1, write a query that returns each manager’s name along with how many direct reports they have. Hint: self join staff to itself on manager_id = staff_id, then group by the manager’s name. You should get Alice with 2 direct reports and Bob with 2 direct reports.

Exercise 2: Using the shared products table, write a self join that lists every pair of products that are in different categories, along with the absolute price difference between them. Hint: change the ON condition to a.category <> b.category instead of matching categories, and remember you’ll need something like a.product_id < b.product_id to avoid duplicate mirrored pairs.

Exercise 3: Using the shared employees table, write a self join query that returns each employee’s name next to the number of other employees in their same department (not counting themselves). Hint: self join on department with a.id <> b.id, then GROUP BY the employee in the a role. Employees who are alone in their department should show a count of 0 — think about whether an inner join can produce that row at all, or whether you need something else.

Summary

• A SELF JOIN is a normal join where a table is joined to itself, using two aliases so every column reference stays unambiguous.
• It’s the standard way to query hierarchies (employee/manager, category/parent), peer relationships (coworkers, similar products), and row-to-row comparisons.
• LEFT JOIN keeps rows with no match, such as a top-of-hierarchy row; INNER JOIN keeps only rows that found a partner.
• Use a.id < b.id instead of a.id <> b.id to avoid getting each unordered pair twice, once in each direction.
• The database engine plans and runs a self join exactly like any other join — indexing the linking column keeps it fast on large tables.
• For deep, multi-level hierarchies, a recursive CTE is usually clearer and more scalable than chaining several self joins together.