SQL (PostgreSQL, MySQL, SQLite) Module 5 – Joining tables

Self-joins: comparing rows of one table

Join a table to itself with two aliases to list each employee with their manager, compare rows of the same table and build pairs without mirrored duplicates.

Intermediate
20 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

Free: the whole lesson. With a pass: the model answers to its 2 interview questions.

What you will learn

Join a table to itself with two aliases that name their roles
List each employee with their manager, keeping the employee who has none
Compare a row with another row of the same table
Build pairs from one table without self-pairs or mirrored pairs
Find duplicate records with a self-join

Before you start

LEFT, RIGHT and FULL OUTER JOIN

What you’ll learn · Before you start

What you will learn

Join a table to itself with two aliases that name their roles
List each employee with their manager, keeping the employee who has none
Compare a row with another row of the same table
Build pairs from one table without self-pairs or mirrored pairs
Find duplicate records with a self-join

Before you start

LEFT, RIGHT and FULL OUTER JOIN

On this page

A self-join joins a table to itself. You list the table twice in FROM, give each copy its own alias, and then join the two copies exactly as you would join two different tables. You need one whenever a row points to another row of the same table, as employees.manager_id holds the emp_id of the employee’s manager, and whenever rows of one table have to be compared with each other: an employee with their manager, riders who cover the same area, accounts that share an email address. Nothing is copied. The two aliases are two roles that the same rows play in one query.

The examples use SQLite 3.49.1 as sql.js 1.14.2 runs it in a browser. A self-join needs no syntax of its own, so the same queries work in PostgreSQL 18 and MySQL 8.4; only deleting duplicates differs, as the end of the lesson shows.

Each employee with their manager

The employees table refers to itself: manager_id is a foreign key to emp_id, and the person at the top has NULL there. Drawn as a tree, every arrow is one employee’s link to their manager:

The employees table drawn as a tree

Text description of the diagram

The chart shows the seven rows of the employees table as boxes, each with the employee's number, first name and manager_id. Every arrow runs from an employee up to the employee that their manager_id names, in the same table.

Kavya (1) is at the top. Her manager_id is NULL, so no arrow leaves her box.
Arjun (2) and Fatima (3) have manager_id 1 and point to Kavya.
Deepak (4) has manager_id 2 and points to Arjun; Neha (5) has manager_id 4 and points to Deepak.
Suresh (6) has manager_id 3 and points to Fatima; Ritu (7) has manager_id 6 and points to Suresh.

Each of the six arrows is one row of the self-join employees AS e JOIN employees AS m ON m.emp_id = e.manager_id. Kavya has no arrow of her own, so that inner join leaves her out as an employee; a LEFT JOIN keeps her with no manager.

To print names on both ends of an arrow, read the table twice: once as the employee, e, and once as the manager, m. The condition m.emp_id = e.manager_id says which manager row belongs to which employee row:

Every employee with their manager's name SQL · employees_managers.sql

CREATE TABLE employees (
  emp_id     INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (emp_id),   -- NULL for the person at the top
  joined_on  TEXT    NOT NULL
);
INSERT INTO employees (emp_id, name, title, manager_id, joined_on) VALUES
  (1, 'Kavya Iyer',    'Founder and CEO',      NULL, '2019-04-01'),
  (2, 'Arjun Mehta',   'Head of Engineering',  1,    '2019-09-16'),
  (3, 'Fatima Sheikh', 'Head of Operations',   1,    '2020-01-06'),
  (4, 'Deepak Rao',    'Engineering Lead',     2,    '2019-06-03'),
  (5, 'Neha Gupta',    'Frontend Engineer',    4,    '2021-02-15'),
  (6, 'Suresh Babu',   'Warehouse Lead',       3,    '2019-11-25'),
  (7, 'Ritu Sharma',   'Delivery Coordinator', 6,    '2022-07-11');

-- Two copies of one table: e plays the employee, m the manager.
SELECT e.name AS employee, e.title, m.name AS manager
FROM employees AS e
JOIN employees AS m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

Output

┌───────────────┬──────────────────────┬───────────────┐
│   employee    │        title         │    manager    │
├───────────────┼──────────────────────┼───────────────┤
│ Arjun Mehta   │ Head of Engineering  │ Kavya Iyer    │
│ Fatima Sheikh │ Head of Operations   │ Kavya Iyer    │
│ Deepak Rao    │ Engineering Lead     │ Arjun Mehta   │
│ Neha Gupta    │ Frontend Engineer    │ Deepak Rao    │
│ Suresh Babu   │ Warehouse Lead       │ Fatima Sheikh │
│ Ritu Sharma   │ Delivery Coordinator │ Suresh Babu   │
└───────────────┴──────────────────────┴───────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < employees_managers.sql

Each result row combines two rows of the same table: Neha’s own row as e and Deepak’s row as m. Name the aliases after their roles. e and m, or emp and mgr, keep the condition readable, while a and b hide which side is which. That matters, because the condition written the other way round, e.emp_id = m.manager_id, pairs each employee with their direct reports instead of their manager.

Keeping the person at the top

Six employees came back, not seven. Kavya’s manager_id is NULL, which equals nothing, so the inner join drops her. The previous lesson’s LEFT JOIN keeps her:

A LEFT self-join keeps the employee without a manager SQL · left_self_join.sql

CREATE TABLE employees (
  emp_id     INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (emp_id),   -- NULL for the person at the top
  joined_on  TEXT    NOT NULL
);
INSERT INTO employees (emp_id, name, title, manager_id, joined_on) VALUES
  (1, 'Kavya Iyer',    'Founder and CEO',      NULL, '2019-04-01'),
  (2, 'Arjun Mehta',   'Head of Engineering',  1,    '2019-09-16'),
  (3, 'Fatima Sheikh', 'Head of Operations',   1,    '2020-01-06'),
  (4, 'Deepak Rao',    'Engineering Lead',     2,    '2019-06-03'),
  (5, 'Neha Gupta',    'Frontend Engineer',    4,    '2021-02-15'),
  (6, 'Suresh Babu',   'Warehouse Lead',       3,    '2019-11-25'),
  (7, 'Ritu Sharma',   'Delivery Coordinator', 6,    '2022-07-11');

-- A LEFT JOIN keeps the person at the top, who has no manager.
SELECT e.name AS employee,
       coalesce(m.name, '(no manager)') AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

Output

┌───────────────┬───────────────┐
│   employee    │    manager    │
├───────────────┼───────────────┤
│ Kavya Iyer    │ (no manager)  │
│ Arjun Mehta   │ Kavya Iyer    │
│ Fatima Sheikh │ Kavya Iyer    │
│ Deepak Rao    │ Arjun Mehta   │
│ Neha Gupta    │ Deepak Rao    │
│ Suresh Babu   │ Fatima Sheikh │
│ Ritu Sharma   │ Suresh Babu   │
└───────────────┴───────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < left_self_join.sql

coalesce(m.name, '(no manager)') replaces the NULL with a label for the report.

Two levels up

Each further level up the tree needs one more copy of the table:

Employee, manager and the manager's manager SQL · two_levels.sql

CREATE TABLE employees (
  emp_id     INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (emp_id),   -- NULL for the person at the top
  joined_on  TEXT    NOT NULL
);
INSERT INTO employees (emp_id, name, title, manager_id, joined_on) VALUES
  (1, 'Kavya Iyer',    'Founder and CEO',      NULL, '2019-04-01'),
  (2, 'Arjun Mehta',   'Head of Engineering',  1,    '2019-09-16'),
  (3, 'Fatima Sheikh', 'Head of Operations',   1,    '2020-01-06'),
  (4, 'Deepak Rao',    'Engineering Lead',     2,    '2019-06-03'),
  (5, 'Neha Gupta',    'Frontend Engineer',    4,    '2021-02-15'),
  (6, 'Suresh Babu',   'Warehouse Lead',       3,    '2019-11-25'),
  (7, 'Ritu Sharma',   'Delivery Coordinator', 6,    '2022-07-11');

-- A third copy reaches one level higher: the manager's manager.
SELECT e.name  AS employee,
       m.name  AS manager,
       mm.name AS managers_manager
FROM employees AS e
LEFT JOIN employees AS m  ON m.emp_id  = e.manager_id
LEFT JOIN employees AS mm ON mm.emp_id = m.manager_id
ORDER BY e.emp_id;

Output

┌───────────────┬───────────────┬──────────────────┐
│   employee    │    manager    │ managers_manager │
├───────────────┼───────────────┼──────────────────┤
│ Kavya Iyer    │               │                  │
│ Arjun Mehta   │ Kavya Iyer    │                  │
│ Fatima Sheikh │ Kavya Iyer    │                  │
│ Deepak Rao    │ Arjun Mehta   │ Kavya Iyer       │
│ Neha Gupta    │ Deepak Rao    │ Arjun Mehta      │
│ Suresh Babu   │ Fatima Sheikh │ Kavya Iyer       │
│ Ritu Sharma   │ Suresh Babu   │ Fatima Sheikh    │
└───────────────┴───────────────┴──────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < two_levels.sql

The second join starts from m, not from e: mm.emp_id = m.manager_id finds the manager of the manager. Both joins are LEFT JOINs. If the second were an inner join, Kavya, Arjun and Fatima would disappear, because none of them has a manager’s manager; an inner first join would drop Kavya. A fixed number of joins only reaches a fixed number of levels; a chart of any depth calls for a recursive query, which the module on common table expressions builds.

Comparing a row with another row

Once the two copies are joined, WHERE can compare any column of one with any column of the other. Which employees joined the company before the manager they now report to?

Employees who joined before their manager SQL · joined_before_manager.sql

CREATE TABLE employees (
  emp_id     INTEGER PRIMARY KEY,
  name       TEXT    NOT NULL,
  title      TEXT    NOT NULL,
  manager_id INTEGER REFERENCES employees (emp_id),   -- NULL for the person at the top
  joined_on  TEXT    NOT NULL
);
INSERT INTO employees (emp_id, name, title, manager_id, joined_on) VALUES
  (1, 'Kavya Iyer',    'Founder and CEO',      NULL, '2019-04-01'),
  (2, 'Arjun Mehta',   'Head of Engineering',  1,    '2019-09-16'),
  (3, 'Fatima Sheikh', 'Head of Operations',   1,    '2020-01-06'),
  (4, 'Deepak Rao',    'Engineering Lead',     2,    '2019-06-03'),
  (5, 'Neha Gupta',    'Frontend Engineer',    4,    '2021-02-15'),
  (6, 'Suresh Babu',   'Warehouse Lead',       3,    '2019-11-25'),
  (7, 'Ritu Sharma',   'Delivery Coordinator', 6,    '2022-07-11');

-- Compare each employee's row with their manager's row.
SELECT e.name AS employee, e.joined_on,
       m.name AS manager,  m.joined_on AS manager_joined_on
FROM employees AS e
JOIN employees AS m ON m.emp_id = e.manager_id
WHERE e.joined_on < m.joined_on
ORDER BY e.emp_id;

Output

┌─────────────┬────────────┬───────────────┬───────────────────┐
│  employee   │ joined_on  │    manager    │ manager_joined_on │
├─────────────┼────────────┼───────────────┼───────────────────┤
│ Deepak Rao  │ 2019-06-03 │ Arjun Mehta   │ 2019-09-16        │
│ Suresh Babu │ 2019-11-25 │ Fatima Sheikh │ 2020-01-06        │
└─────────────┴────────────┴───────────────┴───────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < joined_before_manager.sql

Deepak joined about three months before Arjun, who became his manager, and Suresh about six weeks before Fatima. The pattern is the same whatever the comparison: join each row to its partner row, then test the two in WHERE.

Pairs without mirrors

A self-join on a shared value, rather than on a key, pairs rows that have something in common. Riders who cover the same area can share deliveries, but the condition needs care:

Three conditions for pairs of riders SQL · pairs.sql

-- Delivery riders and the area each one covers.
CREATE TABLE riders (rider_id INTEGER PRIMARY KEY, name TEXT NOT NULL, area TEXT NOT NULL);
INSERT INTO riders (rider_id, name, area) VALUES
  (1, 'Imran',  'Kothrud'), (2, 'Bala',   'Kothrud'), (3, 'Chetan', 'Kothrud'),
  (4, 'Divya',  'Baner'),   (5, 'Elango', 'Baner');

-- Same area only: every rider is also paired with themselves, and each pair comes twice.
SELECT count(*) AS same_area FROM riders AS a JOIN riders AS b ON b.area = a.area;

-- Different riders: no self-pairs, but (Imran, Bala) and (Bala, Imran) are both there.
SELECT count(*) AS different_riders
FROM riders AS a JOIN riders AS b ON b.area = a.area AND b.rider_id <> a.rider_id;

-- a.rider_id < b.rider_id: each pair exactly once.
SELECT a.area, a.name AS rider, b.name AS partner
FROM riders AS a
JOIN riders AS b ON b.area = a.area AND a.rider_id < b.rider_id
ORDER BY a.area, a.rider_id, b.rider_id;

Output

┌───────────┐
│ same_area │
├───────────┤
│ 13        │
└───────────┘
┌──────────────────┐
│ different_riders │
├──────────────────┤
│ 8                │
└──────────────────┘
┌─────────┬───────┬─────────┐
│  area   │ rider │ partner │
├─────────┼───────┼─────────┤
│ Baner   │ Divya │ Elango  │
│ Kothrud │ Imran │ Bala    │
│ Kothrud │ Imran │ Chetan  │
│ Kothrud │ Bala  │ Chetan  │
└─────────┴───────┴─────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < pairs.sql

b.area = a.area alone gives 13 rows: Kothrud’s 3 riders make 3 × 3 = 9 pairs and Baner’s 2 make 2 × 2 = 4, and those include every rider paired with themselves and every pair twice.
Adding b.rider_id <> a.rider_id removes the self-pairs and leaves 8 rows, but Imran with Bala and Bala with Imran are still both there.
Adding a.rider_id < b.rider_id instead keeps only the copy with the smaller id first: each pair exactly once, 4 in all. An area of k riders gives k(k − 1)/2 pairs: 3 for Kothrud and 1 for Baner.

Finding duplicate records

The same idea finds records that were entered twice. Some customers signed up more than once with the same email address; the addresses use example.com, a domain reserved for examples:

Accounts that share an email address SQL · duplicate_accounts.sql

-- Some people signed up more than once with the same email address.
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  email       TEXT    NOT NULL
);
INSERT INTO customers (customer_id, name, email) VALUES
  (1, 'Asha Kulkarni', 'asha.k@example.com'),
  (2, 'Vikram Nair',   'vikram.nair@example.com'),
  (3, 'Asha K',        'asha.k@example.com'),
  (4, 'Meera Rathore', 'meera.r@example.com'),
  (5, 'A. Kulkarni',   'asha.k@example.com'),
  (6, 'Meera R.',      'meera.r@example.com');

-- Every pair of accounts with the same email, each pair once.
SELECT a.customer_id AS account, b.customer_id AS same_email
FROM customers AS a
JOIN customers AS b ON b.email = a.email AND b.customer_id > a.customer_id
ORDER BY account, same_email;

-- Each later account against the first account with that email: a third copy finds earlier ones.
SELECT a.customer_id AS first_account, b.customer_id AS duplicate, b.name
FROM customers AS a
JOIN customers AS b ON b.email = a.email AND b.customer_id > a.customer_id
LEFT JOIN customers AS earlier ON earlier.email = a.email AND earlier.customer_id < a.customer_id
WHERE earlier.customer_id IS NULL
ORDER BY first_account, duplicate;

Output

┌─────────┬────────────┐
│ account │ same_email │
├─────────┼────────────┤
│ 1       │ 3          │
│ 1       │ 5          │
│ 3       │ 5          │
│ 4       │ 6          │
└─────────┴────────────┘
┌───────────────┬───────────┬─────────────┐
│ first_account │ duplicate │    name     │
├───────────────┼───────────┼─────────────┤
│ 1             │ 3         │ Asha K      │
│ 1             │ 5         │ A. Kulkarni │
│ 4             │ 6         │ Meera R.    │
└───────────────┴───────────┴─────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < duplicate_accounts.sql

The first query lists every pair once. Three accounts share Asha’s address, so they form 3 × 2 ÷ 2 = 3 pairs, including the pair of the two later accounts, 3 and 5. To keep the first account and list every later one against it, the second query adds a third copy of the table, earlier, and keeps a pair only when no account with the same address comes before a: the anti-join of the previous lesson, applied to the same table. Real data needs its values normalised first, such as lower(email), so that Asha.K@Example.com and asha.k@example.com count as one address.

Removing the later copies

Once you are sure the accounts belong to one person, the same comparison can delete the later copies. Standard SQL has no join in DELETE, so the engines differ: PostgreSQL and MySQL each added their own syntax for it, and SQLite has none, so there the second copy of the table moves into an EXISTS subquery, which the module on subqueries explains:

Delete every account that has an earlier one with the same email SQL · remove_duplicates.sql

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  email       TEXT    NOT NULL
);
INSERT INTO customers (customer_id, name, email) VALUES
  (1, 'Asha Kulkarni', 'asha.k@example.com'),
  (2, 'Vikram Nair',   'vikram.nair@example.com'),
  (3, 'Asha K',        'asha.k@example.com'),
  (4, 'Meera Rathore', 'meera.r@example.com'),
  (5, 'A. Kulkarni',   'asha.k@example.com'),
  (6, 'Meera R.',      'meera.r@example.com');

-- SQLite's DELETE has no join: the second copy of the table moves into EXISTS.
-- Delete every account that has an earlier account with the same email.
DELETE FROM customers
WHERE EXISTS (
  SELECT 1
  FROM customers AS earlier
  WHERE earlier.email = customers.email
    AND earlier.customer_id < customers.customer_id
);

SELECT customer_id, name, email FROM customers ORDER BY customer_id;

Output

┌─────────────┬───────────────┬─────────────────────────┐
│ customer_id │     name      │          email          │
├─────────────┼───────────────┼─────────────────────────┤
│ 1           │ Asha Kulkarni │ asha.k@example.com      │
│ 2           │ Vikram Nair   │ vikram.nair@example.com │
│ 4           │ Meera Rathore │ meera.r@example.com     │
└─────────────┴───────────────┴─────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < remove_duplicates.sql

Accounts 3, 5 and 6 each had an earlier account with the same address, and only the first account of each address is left. PostgreSQL writes the same delete with its USING clause, DELETE FROM customers AS b USING customers AS a WHERE b.email = a.email AND b.customer_id > a.customer_id, and MySQL names the copy to delete from before a join, DELETE b FROM customers AS b JOIN customers AS a ON b.email = a.email AND b.customer_id > a.customer_id. In a real database, first move the orders and other rows that point to the later accounts over to the account you keep, or the foreign keys refuse the delete, or worse, nothing checks them. The module on changing data covers DELETE in full.

How the engines differ

Self-joins in PostgreSQL, MySQL and SQLite
Criterion	PostgreSQL 18	MySQL 8.4	SQLite 3.49
A second copy of the table in FROM	Needs an alias	Needs an alias	Needs an alias
Deleting the later duplicates	DELETE … USING	DELETE b FROM … JOIN	DELETE … WHERE EXISTS
A subquery on the same table inside DELETE	Allowed	Error 1093	Allowed
A hierarchy of any depth	WITH RECURSIVE	WITH RECURSIVE	WITH RECURSIVE

SQLite Online (SQL Playground) Load a CSV export into SQLite in your browser and look for repeated email addresses or phone numbers with a self-join.

Interview questions

Fresher · What is a self-join, and why does it need table aliases?

A self-join joins a table to itself: the table appears twice in FROM, and the join condition compares a row of one copy with rows of the other, as if they were two tables. It is needed when a row refers to another row of the same table, such as employees.manager_id pointing at employees.emp_id, or when rows of one table are compared with each other, such as riders in the same area or accounts that share an email address. Aliases are required because both copies have the same name and the same columns: e.name and m.name say which copy each column comes from, and without aliases every column reference would be ambiguous. Name the aliases after their roles, such as e for the employee and m for the manager, and use a LEFT JOIN when rows without a partner, like the person at the top, must stay.

Model answers open with any pass: ₹79 for 1 week ($2.99), one payment that never renews.

Choose a pass

Mid to senior

A customers table of 2 million rows must be checked for accounts that share a phone number. Write the self-join, estimate the work it does with and without an index on the phone column, and say when GROUP BY is the better tool.

Model answer, Pro. Model answers open with any pass; prices are in the notice on this page. First step and follow-ups
Fresher to mid

With self-joins only, list each employee with their manager and their manager's manager, keeping employees who have neither. What changes when the company has seven levels, and what would you use instead?

Model answer, Pro. First step and follow-ups

Key takeaways

A self-join lists one table twice under two aliases and joins the copies like two tables; nothing is copied.
JOIN employees AS m ON m.emp_id = e.manager_id finds each employee’s manager; a LEFT JOIN keeps the person at the top, and each further level needs another copy.
After the join, WHERE can compare any column of a row with its partner row.
For pairs, a.id < b.id gives each pair once; a.id <> b.id still lists every pair twice, and equality alone adds self-pairs.
A value shared by k rows produces k(k − 1)/2 pairs, so compare duplicates with the first record instead of with each other.

Exercise

Exercise · Easy · SQL

Employees who earn more than their manager

The table employees has one row per employee:

emp_id: the employee's number;
name;
manager_id: the emp_id of the employee's manager, or NULL for the person at the top;
salary_paise: the employee's current monthly salary, in paise.

Write a query that returns the employees whose salary is higher than their own manager's salary, with two columns:

employee: the employee's name;
manager: the manager's name.

Compare each employee only with their direct manager. The rows may come in any order.

Starter code · above_manager.sql

-- Each employee with their own salary and the number of their manager. Find the manager's row to compare with.
SELECT e.name, e.salary_paise, e.manager_id
FROM employees AS e;

The sample tests · tests.yaml

# Sample tests: each one builds an employees table, runs your query on it and compares the rows it returns (in any
# order).
tests:
  - name: finds the employee who earns more than their manager
    setup: |
      CREATE TABLE employees (emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER, salary_paise INTEGER NOT NULL);
      INSERT INTO employees VALUES
        (1, 'Kavya Iyer',    NULL, 50000000),
        (2, 'Arjun Mehta',   1,    30000000),
        (3, 'Deepak Rao',    2,    32000000),
        (4, 'Neha Gupta',    2,    18000000);
    expected:
      columns: [employee, manager]
      rows:
        - ['Deepak Rao', 'Arjun Mehta']
  - name: leaves out equal salaries and compares only with the direct manager
    setup: |
      CREATE TABLE employees (emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER, salary_paise INTEGER NOT NULL);
      INSERT INTO employees VALUES
        (10, 'Fatima Sheikh', NULL, 40000000),
        (11, 'Suresh Babu',   10,   9000000),
        (12, 'Ritu Sharma',   11,   9000000),
        (13, 'Imran Khan',    11,   12000000),
        (14, 'Divya Menon',   13,   15000000);
    expected:
      columns: [employee, manager]
      rows:
        - ['Imran Khan', 'Suresh Babu']
        - ['Divya Menon', 'Imran Khan']
  - name: never lists the person at the top, who has no manager
    setup: |
      CREATE TABLE employees (emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER, salary_paise INTEGER NOT NULL);
      INSERT INTO employees VALUES
        (20, 'Lalitha Raman', NULL, 90000000),
        (21, 'Joseph Mathew', 20,   20000000),
        (22, 'Farah Siddiqui', 20,  95000000);
    expected:
      columns: [employee, manager]
      rows:
        - ['Farah Siddiqui', 'Lalitha Raman']

A hint

The manager's salary is in another row of the same table. Give employees two aliases, one for the employee and one for the manager, and join them so that the manager's emp_id equals the employee's manager_id.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

References

PostgreSQL 18 Documentation: Joins Between Tables (self joins) (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: Table Expressions (table and column aliases) (The PostgreSQL Global Development Group)
PostgreSQL 18 Documentation: Constraints (self-referential foreign keys) (The PostgreSQL Global Development Group)
SQLite: The SELECT statement (SQLite)
MySQL 8.4 Reference Manual: JOIN Clause (Oracle Corporation)
IETF RFC 2606: Reserved Top Level DNS Names (example.com) (Internet Engineering Task Force)

Report a problem with this lesson

In this module: Joining tables

Primary keys, foreign keys and relationships Free lesson, Pro answers: 20 minutes
INNER JOIN: combining rows from two tables Free lesson, Pro answers: 25 minutes
LEFT, RIGHT and FULL OUTER JOIN Free lesson, Pro answers: 30 minutes
Self-joins: comparing rows of one table Free lesson, Pro answers: 20 minutes
CROSS JOIN and combinations Free lesson, Pro answers: 20 minutes
Joining many tables without double counting Free lesson, Pro answers: 25 minutes
Range joins, as-of joins and LATERAL Free lesson, Pro answers: 30 minutes

Your country

Self-joins: comparing rows of one table

What you will learn

Before you start

What you will learn

Before you start

Each employee with their manager

Keeping the person at the top

Two levels up

Comparing a row with another row

Pairs without mirrors

Finding duplicate records

Removing the later copies

How the engines differ

Interview questions

A customers table of 2 million rows must be checked for accounts that share a phone number. Write the self-join, estimate the work it does with and without an index on the phone column, and say when GROUP BY is the better tool.

With self-joins only, list each employee with their manager and their manager's manager, keeping employees who have neither. What changes when the company has seven levels, and what would you use instead?

Key takeaways

Exercise

Employees who earn more than their manager

Results of the sample tests

Check yourself

References

Related tools