0Pricing
SQL Academy · Lesson

What Is a Self Join

Join a table to itself with aliases.

What Is a Self Join is a free SQL Academy lesson on CoddyKit — lesson 1 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.

Joining a Table to Itself

A self join is when you join a table to itself. This sounds unusual at first, but it is a powerful technique used when rows in a single table are related to other rows in the same table.

The most common example is an employees table where each employee row also stores the ID of their manager — who is also an employee in the same table.

The Employees Table

Consider an employees table where each row has an id, a name, and a manager_id that points to another row in the same table.

This structure is called a self-referencing or recursive relationship. Run the query below to create and populate the table.

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

INSERT INTO employees VALUES
  (1, 'Alice',   NULL),
  (2, 'Bob',     1),
  (3, 'Carol',   1),
  (4, 'Dave',    2),
  (5, 'Eve',     2),
  (6, 'Frank',   3);

Why Normal Joins Do Not Work Here

If you want to display each employee alongside their manager's name, both pieces of data live in the same table. A normal join connects two different tables (or at least two different sources).

To read from the same table twice — once for the employee and once for the manager — you need a self join combined with table aliases.

Introducing Aliases

To perform a self join you write the same table name twice in the FROM / JOIN clause and give each occurrence a different alias. The aliases let SQL treat them as two separate logical tables.

The convention is to use short, descriptive aliases such as e for employee and m for manager.

-- Conceptual structure (not yet a full query)
-- FROM employees AS e
-- JOIN employees AS m ON e.manager_id = m.id

Your First Self Join

Here is a complete self join that lists every employee together with their manager's name. The ON condition links the employee's manager_id to the manager's id.

Alice has no manager, so she is excluded by the INNER JOIN — she has no matching row on the manager side.

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

Including Rows With No Manager

When you use INNER JOIN, rows that have no match are dropped. Alice has manager_id = NULL, so she disappears from the results.

Switch to a LEFT JOIN to keep all employees and simply show NULL (or a label) when there is no manager.

SELECT
  e.name             AS employee,
  COALESCE(m.name, 'No Manager') AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.id;

Filtering the Self Join Results

You can add a WHERE clause to a self join just like any other query. The example below retrieves only the employees whose direct manager is Alice (id = 1).

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

Counting Direct Reports

Self joins combine naturally with aggregate functions. The query below counts how many direct reports each manager has by grouping on the manager alias.

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

Self Joins on Non-Hierarchical Data

Self joins are not limited to hierarchies. Any time rows in a table relate to other rows in the same table, a self join can help.

For example, finding all pairs of employees who share the same manager — joining the table to itself on matching manager_id values while ensuring you do not pair an employee with themselves.

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

The Role of the Alias

Aliases are mandatory in a self join — without them the database cannot tell which copy of the table each column refers to, and you will get an ambiguity error.

You may use any alias names you like. The key rule is that every column reference must be prefixed with the correct alias so SQL knows which instance of the table to look at.

-- This will fail: column 'name' is ambiguous
-- SELECT name FROM employees JOIN employees ON manager_id = id;

-- This works: aliases remove the ambiguity
SELECT e.name, m.name
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

Real-World Use Cases

Self joins appear frequently in real databases. Common use cases include:

  • Org charts — employees and their managers
  • Category trees — parent and child categories
  • Bill of materials — components made of other components
  • Social networks — finding friends-of-friends in a single connections table

Recognising a self-referencing foreign key in a schema is the first clue that a self join may be needed.

Quick Check

Test your understanding of self joins before moving on.

Lesson Recap

In this lesson you learned what a self join is and when to use one:

  • A self join connects a table to itself by referencing it twice with different aliases
  • Use INNER JOIN to exclude rows with no match (e.g. top-level managers) or LEFT JOIN to keep them
  • Add WHERE, GROUP BY, and aggregate functions exactly as you would in any other join
  • Use a.id < b.id in the ON clause to avoid duplicate pairs when comparing rows within the same table
  • Self joins are the go-to tool for hierarchical data, category trees, and any self-referencing relationship

Next up: exploring multi-level hierarchies with recursive CTEs.

Frequently asked questions

Is the “What Is a Self Join” lesson free?

Yes — the full text of “What Is a Self Join” 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 “What Is a Self Join”?

Join a table to itself with aliases. 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 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “What Is a Self Join” 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