SQLite Self Join: Querying a Table Against Itself

A Self Join Is Just a Join With Aliases

There's nothing special about a self join. It's an ordinary JOIN where both sides happen to be the same table. The trick is that SQLite needs a way to tell the two copies apart, so you give each one an alias.

You reach for one whenever a row in a table refers to another row in the same table. The classic case: an employees table where each row has a manager_id pointing at another employee:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

Ada has no manager. Boris and Cleo report to Ada. Diego and Esme report to Boris. The relationship lives entirely inside one table — and that's exactly where a self join earns its keep.

The Basic Shape

To get each employee paired with their manager's name, join employees to itself. One copy plays the role of "employee," the other plays "manager":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

Read it as two tables that happen to share storage. e is the employee row; m is the manager row. The join condition e.manager_id = m.id lines them up: for each employee, find the row in m whose id matches the employee's manager_id.

Notice Ada didn't show up. Her manager_id is NULL, and INNER JOIN drops rows that don't match.

Keeping the Unmatched Rows: LEFT JOIN

If you want everyone in the result, including the people without a manager, switch to LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

Now Ada appears with NULL in the manager column. Same self-join mechanics, just with the join type doing what LEFT JOIN always does — keep every row from the left side, fill blanks where there's no match.

This is the form you usually want when displaying a list of people. "No manager" is information; dropping the row isn't.

The Aliases Aren't Optional

Try the join without aliases and SQLite has no idea what you mean:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

Every column appears twice — once from each copy of the table — and SQLite can't pick. Aliases solve this by giving each instance its own name. Pick aliases that describe the role the row is playing, not the table:

• e and m for employee/manager.
• parent and child for hierarchies.
• a and b when you're comparing arbitrary pairs.

The alias is the whole reason a self join reads cleanly.

Finding Pairs Within a Table

Self joins aren't only for hierarchies. Any time you want to compare rows in the same table, the pattern fits. Here's a list of products, where we want every pair priced the same:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('Mug',     9.99),
    ('Notebook', 9.99),
    ('Pen',     2.50),
    ('Pencil',  2.50),
    ('Lamp',   29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

Two things to spot. First, a.price = b.price is the actual matching condition. Second, a.id < b.id is what stops the query from returning each pair twice (once as (Mug, Notebook), again as (Notebook, Mug)) and from pairing every row with itself. That < trick is worth remembering — it shows up any time you're listing pairs.

Going Two Levels Up

A self join handles one hop in a hierarchy. Want each employee's grand-manager? Join three times:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

Each new alias represents one more level up the tree. This works fine for two or three hops, but it falls apart fast — you'd need to know the depth of the hierarchy at query-writing time, and add a join for each level. That's the wall that recursive CTEs were invented to break through.

When Not to Reach for a Self Join

A self join is the right tool when you need columns from both sides of the relationship in the result. If you only need to filter — say, find every employee whose manager is Ada — a subquery often reads better:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

No alias gymnastics, and the intent is unmistakable. The rule of thumb: do you want data from both rows in the output? Self join. Do you just need a value to compare against? Subquery.

For arbitrary-depth hierarchies (org charts, file trees, threaded comments), neither pattern scales. That's recursive CTE territory.

Next: Subqueries

Self joins and subqueries solve overlapping problems, and knowing which one fits saves you a lot of squinting at SQL later. The next page goes deep on subqueries — scalar, correlated, and IN forms — and where each one shines.