SELF JOIN in SQL (Beginner-Friendly Explanation)

A SELF JOIN sounds scary at first, but the idea is actually very simple.

Let’s understand it slowly, using a real-life example and plain language.

What is a SELF JOIN?

👉 SELF JOIN = joining a table with itself

Why would we do that?

Because one row in a table is related to another row in the same table.

Real-Life Example: Employee & Manager

Imagine an employees table.

emp_id emp_name manager_id
1 Alice NULL
2 Bob 1
3 Charlie 1
4 David 2

How to read this:

  • Alice has no manager (CEO)
  • Bob reports to Alice
  • Charlie reports to Alice
  • David reports to Bob

👉 Employee and Manager are in the SAME table

The Problem

👉 Show employee name and manager name

Expected output:

Employee Manager
Bob Alice
Charlie Alice
David Bob

Why Normal JOIN Won’t Work

There is only one table.
So we use the same table twice, with different names.

These names are called aliases.

The SELF JOIN Query

SELECT
  e.emp_name   AS employee,
  m.emp_name   AS manager
FROM employees e
JOIN employees m
  ON e.manager_id = m.emp_id;

Step-by-Step (Very Important)

Step 1: Table Aliases

employees e   -- employee
employees m   -- manager

Same table, two roles.

Step 2: Join Condition

e.manager_id = m.emp_id

Meaning:

“Employee’s manager_id matches manager’s emp_id”

Step 3: Result

  • Bob → manager_id = 1 → Alice
  • David → manager_id = 2 → Bob

✔ Relationship found inside the same table

Visual Way to Think 🧠

Think of the table as:

  • One copy → employees
  • Second copy → managers

But both are the same table

Another Simple Example: Compare Rows

Example Table

id value
1 10
2 20
3 30

👉 Find pairs where one value is greater than another

SELECT a.id, b.id
FROM numbers a
JOIN numbers b
ON a.value > b.value;

✔ Table compared with itself

When Do You Use SELF JOIN?

Use SELF JOIN when:

  • Employee–manager hierarchy
  • Parent–child relationships
  • Comparing rows in the same table
  • Finding duplicates
  • Finding previous / next records (alternative to window functions)

Beginner Rule to Remember 🧠

If a row needs information from another row in the same table,
you need a SELF JOIN.

One-Line Summary

SELF JOIN means using one table twice to compare or relate its own rows.