# SQL Self Join: Syntax, Examples & How It Works

> In a SQL self join, the important decision is which rows belong together: an employee with their manager, two colleagues in the same team, or successive levels in a reporting chain. That decision determines whether a query returns the relationships you intended or a misleading set of matches.

- Canonical URL: https://www.puppygraph.com/blog/self-join-sql
- Published: 2026-11-13
- Author: Hao Wu

In a SQL self join, the important decision is which rows belong together: an employee with their manager, two colleagues in the same team, or successive levels in a reporting chain. That decision determines whether a query returns the relationships you intended or a misleading set of matches.

This guide explains SQL self join syntax through one employee dataset, including INNER JOIN and LEFT JOIN examples, multiple conditions, hierarchical queries, and practical optimization. The examples use PostgreSQL; recursive-query syntax and plan-inspection commands vary across databases.

## What is a self join in SQL?

A self join joins a table to itself so that a query can compare or combine values from different rows in that table. Each reference receives a distinct alias, giving the same underlying data two roles. PostgreSQL's [join tutorial](https://www.postgresql.org/docs/current/tutorial-join.html) illustrates this pattern using two references to one table.

There is no separate `SELF JOIN` keyword. You use ordinary join syntax, such as `INNER JOIN` or `LEFT JOIN`, and name the same table on both sides. “Self” identifies where the inputs come from; the join type determines what happens to unmatched rows.

Common applications include attaching manager names to employees, comparing products within a category, identifying pairs of records with matching attributes, and finding a category's parent. A self join does not require a hierarchy or even a foreign key. Its condition can compare any suitable columns, including numeric ranges or dates.

The useful starting point is to describe each role in plain language. Employee and manager imply a different relationship from employee and colleague, even though both queries read the employee table twice.

## How does a self join work?

Consider this fictional directory. Salaries are annual amounts in the same currency.

| **employee\_id** | **employee\_name** | **manager\_id** | **department** | **salary** |
| --- | --- | --- | --- | --- |
| 1   | Maya | NULL | Executive | 180000 |
| 2   | Luis | 1   | Engineering | 150000 |
| 3   | Priya | 1   | Operations | 145000 |
| 4   | Noah | 2   | Engineering | 120000 |
| 5   | Amina | 2   | Engineering | 160000 |
| 6   | Chen | 3   | Operations | 110000 |
| 7   | Elena | 4   | Engineering | 100000 |

For a manager lookup, call the employee reference `e` and the manager reference `m`. The condition `e.manager_id = m.employee_id` matches Noah's `manager_id` of `2` to Luis's `employee_id` of `2`. The result can then contain Noah's name from `e` and Luis's name from `m`.

The aliases do not create stored copies of the table. They identify independent references within the query. Thinking through candidate row pairs explains the result, but the database chooses its physical execution plan; it need not compare every possible pair.

![How does a self join work?](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/blog-learn-27ca307c8078e4fd985e0e5f.png)

Figure: Aliases give one table two roles; the join condition matches the rows. LEFT JOIN also preserves employees such as Maya who have no matching manager.

Maya has no manager. With an inner join she contributes no employee-side result. With a left join she remains, with `NULL` in the manager fields. Decide whether such unmatched rows belong in the answer before choosing the join type.

## SQL self join syntax

Create the example table and load its rows:

```sql
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    manager_id INTEGER REFERENCES employees(employee_id),
    department VARCHAR(50) NOT NULL,
    salary INTEGER NOT NULL
);

INSERT INTO employees VALUES
    (1, 'Maya', NULL, 'Executive', 180000),
    (2, 'Luis',    1, 'Engineering', 150000),
    (3, 'Priya',   1, 'Operations', 145000),
    (4, 'Noah',    2, 'Engineering', 120000),
    (5, 'Amina',   2, 'Engineering', 160000),
    (6, 'Chen',    3, 'Operations', 110000),
    (7, 'Elena',   4, 'Engineering', 100000);
```

The [self-referential foreign key](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK) ensures that each non-null `manager_id` identifies an existing employee. It protects the data independently of how you query it.

The self join has this form:

```sql
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees AS e
JOIN employees AS m
  ON e.manager_id = m.employee_id;
```

`FROM` introduces the employee role; `JOIN` introduces the manager role. `ON` defines the relationship between them. The table aliases qualify column references, while `AS manager_name` labels an output column. Plain `JOIN` means `INNER JOIN` here.

Use meaningful aliases and qualify columns consistently. A bare `employee_name` is ambiguous because both references expose that column. Explicit output columns also make the intended relationship easier to review than `SELECT *`, which returns both sets of fields.

## SQL self join examples

**Find employees and their managers with INNER JOIN.** This query returns employees whose manager exists:

```sql
SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees AS e
INNER JOIN employees AS m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;
```

| **employee** | **manager** |
| --- | --- |
| Luis | Maya |
| Priya | Maya |
| Noah | Luis |
| Amina | Luis |
| Chen | Priya |
| Elena | Noah |

Maya appears in the manager column but has no row in the employee column. The join treats her differently in each role. Luis likewise appears as an employee reporting to Maya and as the manager of Noah and Amina.

**Keep every employee with LEFT JOIN.** Change the join type when the report must include people without managers:

```sql
SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;
```

The result contains the same six matches plus `Maya | NULL` at the beginning. All seven employees remain. In this schema, the manager's primary key permits at most one match per employee, so this query returns exactly one row per employee. A left join on a non-unique column could return several matches instead.

**Compare an employee's salary with their manager's.** Once the rows are paired, both salaries are available to a filter:

```sql
SELECT e.employee_name AS employee,
       e.salary AS employee_salary,
       m.employee_name AS manager,
       m.salary AS manager_salary
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary
ORDER BY e.employee_id;
```

The single result pairs Amina, earning `160000`, with her manager Luis, earning `150000`. The join establishes the reporting relationship; the filter compares the two salaries. Comparing every employee with every higher-paid employee would answer a different question.

These examples share one relationship condition. Changing the join type changes which employees survive; changing the filter changes which matched relationships qualify.

## Self join with multiple conditions

A join condition can combine several requirements. To find pairs of employees who share both a department and a manager, use:

```sql
SELECT a.employee_name AS employee_1,
       b.employee_name AS employee_2
FROM employees AS a
JOIN employees AS b
  ON a.department = b.department
 AND a.manager_id = b.manager_id
 AND a.employee_id < b.employee_id
ORDER BY a.employee_id, b.employee_id;
```

The result is `Noah | Amina`. Luis and Priya share a manager but have different departments, so they fail the first condition. Elena belongs to Engineering but reports to Noah, so she does not pair with Amina.

The ID comparison does two jobs: it excludes pairing a row with itself and keeps only one ordering of each pair. Using `<>` would exclude self-pairs but return both `Noah | Amina` and `Amina | Noah`. Use `<` when the pair is unordered; use a directional condition when each row has a distinct role.

Nulls need deliberate treatment. Under SQL's [comparison rules](https://www.postgresql.org/docs/current/functions-comparison.html), `NULL = NULL` is not true. Two employees with no manager therefore do not qualify as sharing a manager through this equality condition. That is often appropriate: two missing relationships do not establish a shared supervisor.

When combining `AND` and `OR`, parenthesize alternatives so that requirements such as excluding self-pairs apply to the whole condition. Each condition should correspond to a relationship rule you can explain from the data.

## How to use self joins for hierarchical data

A parent ID stored on each row is an adjacency-list representation of a hierarchy. Each additional self join can follow another level. To find Elena, her manager, and her manager's manager:

```sql
SELECT e.employee_name AS employee,
       m.employee_name AS manager,
       gm.employee_name AS managers_manager
FROM employees AS e
LEFT JOIN employees AS m ON e.manager_id = m.employee_id
LEFT JOIN employees AS gm ON m.manager_id = gm.employee_id
WHERE e.employee_id = 7;
```

This returns `Elena | Noah | Luis`. The query stops at two reporting links because it contains two joins. Another alias would extend it by one level. Left joins retain the starting employee even if the chain ends earlier.

When the required depth depends on the data, use a [recursive CTE](https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE). This PostgreSQL query finds Luis's direct and indirect reports:

```sql
WITH RECURSIVE team AS (
    SELECT employee_id, employee_name, 0 AS depth
    FROM employees
    WHERE employee_id = 2

    UNION ALL

    SELECT e.employee_id, e.employee_name, t.depth + 1
    FROM employees AS e
    JOIN team AS t ON e.manager_id = t.employee_id
)
CYCLE employee_id SET is_cycle USING path
SELECT employee_name, depth
FROM team
WHERE depth > 0 AND NOT is_cycle
ORDER BY depth, employee_id;
```

The anchor selects Luis at depth zero. The recursive term finds employees reporting to the previous iteration's employees. It returns Noah and Amina at depth one, then Elena at depth two; the outer filter excludes Luis.

`CYCLE` detects a repeated employee on a path, stops expanding that cyclic branch, and marks the repeated row. The final filter removes marked rows. A depth limit can bound a business question, but it can also omit deeper descendants; it is not equivalent to finding the complete hierarchy. Other databases use different cycle-handling syntax.

Choose fixed joins for a fixed number of levels and recursion for descendants at arbitrary depth. The [recursive join guide](https://www.puppygraph.com/blog/recursive-join) develops the latter pattern further.

## Self join performance and optimization

A self join's cost depends on its predicate, matching-row counts, indexes, and chosen execution plan. A manager lookup and an all-pairs comparison have very different workloads even though both reference one table twice.

**Estimate the result size.** A join to a unique manager ID produces at most one match per employee. By contrast, pairing every member of a group with every other member produces `n × (n - 1) / 2` unordered pairs. An index cannot remove the cost of returning relationships the query explicitly requests. Restrict the scope to the departments or employees the report actually needs.

**Index the lookup direction.** PostgreSQL creates an index for the `employee_id` primary key. Looking up reports by manager uses the other side of the relationship, so a candidate index is:

```sql
CREATE INDEX employees_manager_id_idx
ON employees (manager_id);
```

PostgreSQL does not automatically index referencing columns merely because they have a [foreign key constraint](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK). The new index can help searches by manager, but the planner may prefer a scan, especially for a small table. For frequent queries combining department and manager equality, evaluate a [multicolumn index](https://www.postgresql.org/docs/current/indexes-multicolumn.html) against the actual workload.

**Inspect the plan with representative data.** PostgreSQL's [`EXPLAIN ANALYZE`](https://www.postgresql.org/docs/current/using-explain.html) executes the query and reports actual row counts alongside estimates:

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT e.employee_name, m.employee_name AS manager
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.employee_id
WHERE e.department = 'Engineering';
```

Look for large estimate errors, repeated work, and expensive intermediate results. A sequential scan of this seven-row fixture is reasonable. It cannot establish how the query will behave against a large directory with a different distribution of departments and managers.

**Ask for only the result you need.** If the question is whether an employee has any direct reports, [`EXISTS`](https://www.postgresql.org/docs/current/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS) expresses that without returning one row per report. Pair generation followed by `DISTINCT` can do unnecessary work when the application only needs a yes-or-no test.

For workloads centered on reporting paths across many levels, a graph model provides another way to express those relationships over the existing data.

[PuppyGraph](https://www.puppygraph.com/) is the first and only zero-ETL graph query engine that lets you query existing relational data as a graph. It is a distributed graph query engine that queries tables in place in SQL databases, data warehouses, and data lakes or lakehouses, including direct reads of Iceberg and Delta Lake, without ETL into a separate graph database. Connect the sources, define a graph schema mapping tables to nodes and edges, and query with openCypher or Gremlin; the same employee rows can represent employees and reporting relationships while remaining queryable with SQL.

![Comparison of the best data lakehouse solutions](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/blog-learn-4b2587a755bbd5c9068284ee.png)

Figure: PuppyGraph queries the existing tables in place, so graph and SQL queries share one copy of the data and no pipeline keeps a graph copy in sync.

![Comparison of the best data lakehouse solutions](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/blog-learn-5820007cae8f617f5bb57a14.png)

Figure: Companies that use PuppyGraph.

![Comparison of the best data lakehouse solutions](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/blog-learn-4492653ad4ba877e25923f1c.png)

Figure: Engineering teams at PuppyGraph customers on what they use it for.

## Mistakes when using SQL self joins

**Reversing the roles.** `e.manager_id = m.employee_id` looks up each employee's manager. `e.employee_id = m.manager_id` finds people reporting to `e`, despite the alias names. Trace one known relationship, such as Noah reporting to Luis, before trusting a larger result.

**Filtering away outer rows.** After a left join, `WHERE m.department = 'Engineering'` removes unmatched employees because their manager fields are null. If the intent is to retain every employee but attach only Engineering managers, put that condition in `ON`. PostgreSQL's [table-expression documentation](https://www.postgresql.org/docs/current/queries-table-expressions.html) demonstrates why those placements produce different outer-join results.

**Hiding a wrong predicate with DISTINCT.** Matching employees on department alone creates all qualifying pairs within each department. Multiple matches may be correct, or they may expose a missing condition. Check the expected relationship and its uniqueness before collapsing rows. For unordered pairs, impose an ordering on unique IDs.

**Assuming valid references guarantee a tree.** A foreign key prevents a manager ID from pointing to a missing employee, but it does not prevent someone from managing themselves or a cycle between several employees. Enforce hierarchy rules when writing data and protect recursive queries against cycles. In an unconstrained imported table, also distinguish a legitimate null manager from a non-null manager ID with no matching row.

## Conclusion

A SQL self join assigns different roles to rows from the same table. Correct results depend on the relationship condition, the treatment of unmatched rows, and the number of matches each row can produce. Use INNER JOIN for matches, LEFT JOIN to preserve the starting rows, and ordered IDs to produce unique unordered pairs. Fixed join chains handle known levels; recursive CTEs handle variable depth.

Try the forever-free [PuppyGraph Developer Edition](https://www.puppygraph.com/download-confirmation) and [book a demo](https://www.puppygraph.com/book-demo) with the team to see how openCypher and Gremlin queries follow relationships across warehouse and lakehouse tables, with no graph-specific ETL, as reporting questions extend beyond direct managers.