0Pricing
SQL Academy · Lesson

Comparing Rows in the Same Table

Find pairs and relationships.

Comparing Rows in the Same Table is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Self Join?

A self join is when you join a table to itself. This sounds unusual at first, but it is a powerful technique for comparing rows within the same table.

Imagine an employees table where each employee has a manager_id that points to another row in the same table. A self join lets you match each employee with their manager in a single query.

Setting Up the Example Table

Let us create a simple employees table to use throughout this lesson. Each row has an id, a name, a salary, and a manager_id that references another employee in the same table.

CREATE TABLE employees (
  id         INT PRIMARY KEY,
  name       VARCHAR(50),
  salary     INT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice',   90000, NULL),
  (2, 'Bob',     70000, 1),
  (3, 'Carol',   65000, 1),
  (4, 'Dave',    55000, 2),
  (5, 'Eve',     60000, 2),
  (6, 'Frank',   48000, 3);

Basic Self Join Syntax

To self join, reference the same table twice using two different aliases. The aliases act as if you have two separate copies of the table. You then specify the join condition that links the two copies.

Here we alias employees as e (the employee) and m (the manager), then match on e.manager_id = m.id.

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

Including Top-Level Rows with LEFT JOIN

A regular INNER JOIN drops rows where manager_id is NULL, which means the top-level employee (Alice, the CEO) would be excluded from results.

Use a LEFT JOIN so that every employee appears, even those with no manager. The manager column will simply be NULL for them.

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

Finding Pairs That Share a Property

Self joins are also useful for finding pairs of rows that share a value. For example, you can find all pairs of employees who report to the same manager.

The condition a.id < b.id prevents duplicate pairs like (Bob, Carol) and (Carol, Bob) from both appearing in the results.

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

Comparing Salaries Between Rows

You can use a self join to compare numeric values across rows. The query below finds every employee whose salary is higher than their own manager's salary — a classic analytical check.

SELECT
  e.name       AS employee,
  e.salary     AS emp_salary,
  m.name       AS manager,
  m.salary     AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Finding Rows Without a Match

A self join with a LEFT JOIN and a WHERE ... IS NULL check lets you find rows that have no matching counterpart. Here we find employees who have no one reporting to them — leaf nodes in the hierarchy.

SELECT e.name AS employee
FROM employees e
LEFT JOIN employees sub ON sub.manager_id = e.id
WHERE sub.id IS NULL
ORDER BY e.name;

Counting Direct Reports

By joining the table to itself and grouping on the manager side, you can count how many direct reports each manager has. Employees with zero reports are included via LEFT JOIN.

SELECT
  m.name           AS manager,
  COUNT(e.id)      AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

Using CTEs to Clarify Self Joins

When a self join query grows complex, wrapping it in a Common Table Expression (CTE) makes the logic easier to read. Define one CTE for the hierarchy and then query it clearly.

WITH hierarchy AS (
  SELECT
    e.id         AS emp_id,
    e.name       AS employee,
    e.salary     AS emp_salary,
    m.name       AS manager,
    m.salary     AS mgr_salary
  FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
)
SELECT *
FROM hierarchy
WHERE mgr_salary IS NOT NULL
  AND emp_salary > mgr_salary;

Self Join on a Non-Hierarchical Table

Self joins are not limited to manager-employee hierarchies. The example below uses a products table to find all pairs of products that belong to the same category, which is useful for recommendation engines or similarity checks.

CREATE TABLE products (
  id       INT PRIMARY KEY,
  name     VARCHAR(50),
  category VARCHAR(30)
);

INSERT INTO products VALUES
  (1, 'Laptop',  'Electronics'),
  (2, 'Phone',   'Electronics'),
  (3, 'Tablet',  'Electronics'),
  (4, 'Shirt',   'Clothing'),
  (5, 'Jeans',   'Clothing');

SELECT
  a.name AS product_1,
  b.name AS product_2,
  a.category
FROM products a
JOIN products b
  ON a.category = b.category
 AND a.id < b.id;

Pitfalls: Cartesian Products and Duplicates

Two common mistakes with self joins are Cartesian products (forgetting the join condition entirely) and duplicate pairs (using a.id <> b.id instead of a.id < b.id).

Always verify your join condition is correct and use < instead of <> when you only need unordered pairs, not ordered ones.

-- Wrong: returns every ordered pair (A,B) AND (B,A)
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id <> b.id;

-- Correct: returns each unordered pair once
SELECT a.name, b.name
FROM employees a
JOIN employees b ON a.manager_id = b.manager_id
                AND a.id < b.id;

Quick Check

Test your understanding of self joins and comparing rows in the same table.

Lesson Recap

In this lesson you learned how to use self joins to compare rows within the same table.

Key takeaways:

  • Alias the table twice (e.g., e and m) so the database treats them as separate sources.
  • Use INNER JOIN when you only need rows that have a match, and LEFT JOIN when you want to include unmatched rows too.
  • Use a.id < b.id to avoid duplicate unordered pairs.
  • Self joins work for hierarchies (manager/employee), similarity searches (same category), and any comparison where two rows in the same table must be related.
  • CTEs can make complex self join queries much easier to read and maintain.

Frequently asked questions

Is the “Comparing Rows in the Same Table” lesson free?

Yes — the full text of “Comparing Rows in the Same Table” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Comparing Rows in the Same Table”?

Find pairs and relationships. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Comparing Rows in the Same Table” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. What Is a Self Join
  2. Employees and Managers
  3. Comparing Rows in the Same Table
  4. Limits of Self Joins
← Back to SQL Academy