(opens in a new tab)

Self Join in SQL: Syntax, Examples and Use Cases

August 14, 2026 by Chat2DB Team

A self join in SQL is a join in which a table is joined to itself. There is no special SELF JOIN keyword: you simply reference the same table twice in the FROM clause, give each reference a different alias, and write a join condition between the two aliases. The database treats the two aliases as if they were two independent tables that happen to contain identical data.

Self joins solve a specific class of problems: relationships that exist between rows of the same table. The classic cases are hierarchical data (an employee row that points to a manager row in the same table), duplicate detection, and row-to-row comparisons such as "compare each day's value to the previous day's value."

This article walks through the syntax, three complete runnable examples, when a window function is a better choice, and what to watch for regarding performance.

Self Join Syntax

The general shape of a self join looks like this:

SELECT a.column_1, b.column_2
FROM my_table AS a
JOIN my_table AS b
  ON a.some_column = b.other_column;

Two rules matter:

1. Aliases are mandatory in practice. If you write FROM my_table JOIN my_table, the database cannot tell which reference a column such as my_table.id belongs to. Most engines reject the statement outright with an error like Not unique table/alias. Aliases (a and b above) remove the ambiguity.
2. Every column reference must be qualified. Inside the query, always write a.id or b.id, never a bare id, because both aliases expose a column of that name.

Any join type works as a self join: INNER JOIN, LEFT JOIN, or even a comparison-based join such as ON a.id < b.id, which is common for pair generation and duplicate detection.

Example 1: Employee-Manager Hierarchy

The most common real-world self join is a table that stores a hierarchy using a foreign key that points back at its own primary key. Create the sample data:

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    full_name     VARCHAR(100) NOT NULL,
    title         VARCHAR(100) NOT NULL,
    manager_id    INT NULL,
    salary        DECIMAL(10, 2) NOT NULL
);
 
INSERT INTO employees (employee_id, full_name, title, manager_id, salary) VALUES
(1, 'Dana Ito',      'CEO',                NULL, 250000.00),
(2, 'Luis Ortega',   'VP Engineering',     1,    190000.00),
(3, 'Mei Chen',      'VP Sales',           1,    185000.00),
(4, 'Sam Patel',     'Backend Engineer',   2,    140000.00),
(5, 'Ana Kovacs',    'Backend Engineer',   2,    138000.00),
(6, 'Tom Reilly',    'Account Executive',  3,    110000.00),
(7, 'Priya Nair',    'Frontend Engineer',  2,    135000.00);

Each row's manager_id refers to another row's employee_id. To list every employee next to their manager's name, join the table to itself: the alias e plays the role of "employee" and the alias m plays the role of "manager."

SELECT
    e.full_name  AS employee,
    e.title      AS employee_title,
    m.full_name  AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Expected output:

Note the LEFT JOIN. The CEO has manager_id = NULL, so an INNER JOIN would silently drop that row. Whenever the top of a hierarchy must appear in the result, use a left join.

A useful variation is a filter across the two aliases. For example, find employees who earn more than 70% of their manager's salary:

SELECT e.full_name, e.salary, m.full_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 * 0.7;

This returns Sam Patel, Ana Kovacs, and Priya Nair, each of whom earns more than 70% of Luis Ortega's salary.

A single-level self join resolves one step of the hierarchy. If you need the full chain (manager of manager of manager…), use a recursive CTE (WITH RECURSIVE), which is essentially a self join applied repeatedly until no new rows are produced.

Example 2: Finding Duplicate Rows

A self join with an inequality condition is a classic way to find rows that duplicate each other on some columns. Sample data:

CREATE TABLE contacts (
    contact_id INT PRIMARY KEY,
    email      VARCHAR(255) NOT NULL,
    city       VARCHAR(100) NOT NULL
);
 
INSERT INTO contacts (contact_id, email, city) VALUES
(1, 'a@example.com', 'Berlin'),
(2, 'b@example.com', 'Paris'),
(3, 'a@example.com', 'Berlin'),
(4, 'c@example.com', 'Madrid'),
(5, 'a@example.com', 'Munich');

To find every pair of rows that share an email:

SELECT
    c1.contact_id AS kept_id,
    c2.contact_id AS duplicate_id,
    c1.email
FROM contacts AS c1
JOIN contacts AS c2
    ON  c1.email = c2.email
    AND c1.contact_id < c2.contact_id;

Expected output:

The condition c1.contact_id < c2.contact_id is doing two jobs. It prevents a row from matching itself (which c1.contact_id <> c2.contact_id would also do), and it removes mirror-image pairs, so you get (1, 3) but not also (3, 1). In MySQL, the same pattern extends directly to a duplicate-deleting statement:

DELETE c2
FROM contacts AS c1
JOIN contacts AS c2
    ON  c1.email = c2.email
    AND c1.contact_id < c2.contact_id;

This keeps the row with the smallest contact_id per email and deletes the rest. In PostgreSQL the equivalent uses DELETE ... USING with the same join condition.

Example 3: Comparing Consecutive Rows

Self joins can relate a row to its logical neighbor, such as the previous day's reading:

CREATE TABLE daily_revenue (
    report_date DATE PRIMARY KEY,
    revenue     DECIMAL(12, 2) NOT NULL
);
 
INSERT INTO daily_revenue (report_date, revenue) VALUES
('2026-08-10', 1200.00),
('2026-08-11', 1350.00),
('2026-08-12', 1100.00),
('2026-08-13', 1600.00);

Join each day to the day exactly one day earlier:

SELECT
    today.report_date,
    today.revenue,
    yesterday.revenue                 AS prev_revenue,
    today.revenue - yesterday.revenue AS change
FROM daily_revenue AS today
JOIN daily_revenue AS yesterday
    ON yesterday.report_date = today.report_date - INTERVAL '1 day'
ORDER BY today.report_date;

Expected output:

The interval syntax varies by dialect: MySQL accepts today.report_date - INTERVAL 1 DAY, and SQL Server would use DATEADD(day, -1, today.report_date). Also note the limitation: this join matches calendar-adjacent rows. If a date is missing (a holiday with no row), that day and the day after it silently disappear from the result, because the join finds no partner row.

Self Join vs Window Functions

The consecutive-rows problem above is exactly what the LAG() window function was designed for:

SELECT
    report_date,
    revenue,
    LAG(revenue) OVER (ORDER BY report_date)             AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY report_date)   AS change
FROM daily_revenue;

Compared with the self join version, the window function:

• Handles gaps correctly. LAG returns the previous existing row by sort order, regardless of missing dates.
• Scans the table once. The self join reads the table twice and performs a join; LAG requires a single scan plus a sort (or none, if an index provides the order).
• Keeps every row. The first day appears with a NULL previous value instead of being dropped.

As a rule of thumb: use window functions (LAG, LEAD, ROW_NUMBER, RANK) when comparing a row to its neighbors in an ordering, or when de-duplicating with ROW_NUMBER() OVER (PARTITION BY email ORDER BY contact_id). Use a self join when the relationship is structural rather than positional — a foreign key into the same table (hierarchies), arbitrary pair generation (a.id < b.id for match-ups), or joins on inequality ranges that windows cannot express.

Performance Notes

A self join is optimized like any other join, but a few points deserve attention:

• Index the join column on both "sides." In the employee-manager example, employee_id is the primary key (already indexed), but an index on manager_id lets the optimizer run the join in either direction efficiently. Without it, expect a full scan on one side.
• Watch for accidental row explosion. Duplicate-detection joins on low-cardinality columns can produce O(n²) intermediate rows. If 10,000 rows share one email, the pair join yields ~50 million pairs. Aggregate first (GROUP BY email HAVING COUNT(*) > 1) and join only the affected keys.
• Check the plan, not your intuition. Run EXPLAIN (MySQL/PostgreSQL) or view the estimated plan (SQL Server) and confirm that the inner side of the join uses an index seek rather than a repeated full scan. A GUI client such as Chat2DB (opens in a new tab) can display the execution plan visually next to the result grid, which makes it easy to spot the table being scanned twice.
• Prefer the window rewrite for ordered comparisons. As shown above, one sorted scan usually beats a join for previous/next-row logic on large tables.

FAQ

Is there a SELF JOIN keyword in SQL? No. A self join is an ordinary JOIN where both sides reference the same table under different aliases.

Can a self join use LEFT JOIN? Yes, and it often should. In hierarchy queries a LEFT JOIN preserves root rows whose parent reference is NULL.

How do I traverse a hierarchy of unknown depth? A plain self join resolves exactly one level. Use a recursive CTE (WITH RECURSIVE in MySQL 8+/PostgreSQL, WITH in SQL Server) for arbitrary depth.

Do self joins perform badly? Not inherently. With proper indexes on the join columns they behave like any two-table join. The risk is combinatorial growth when many rows share the same join key — measure with EXPLAIN before assuming either way.