SQL Self Join

Videos Discussion

At first glance, JOINing a table with itself sounds confusing.

A common beginner reaction is:

"Why do we need two tables if the data is already in one table ?"

The answer is simple:

Sometimes relationships exist within the same table.

For example:

An employee reports to another employee

A product belongs to another product (parent–child)

A category has sub-categories

In such cases, Self JOIN becomes necessary.

What Is a Self JOIN?

A Self JOIN is a JOIN where:

A table is joined with itself

SQL treats it as two separate tables using aliases

Important: Self JOIN is not a new JOIN type. It uses existing JOINs like INNER JOIN or LEFT JOIN.

Why Self JOIN Is Needed

Imagine this scenario:

One table stores employees

Each employee has a manager

Managers are also employees

Instead of creating a separate managers table, we store everything in one employees table.

Now the question is:

"How do we fetch employee name along with manager name ?"

This is where Self JOIN comes into picture.

Table Structure

employees

emp_id	emp_name	manager_id
1	Amit	NULL
2	Neha	1
3	Rahul	1
4	Priya	2
5	Karan	2

Explanation:

Amit has no manager (CEO)

Neha & Rahul report to Amit

Priya & Karan report to Neha

Problem Statement

We want to display

Employee	Manager
Neha	Amit
Rahul	Amit
Priya	Neha
Karan	Neha

How Self JOIN Solves This

We treat the same table as two roles:

One as employee

One as manager

This is done using table aliases.

Example

SELECT 
    e.emp_name AS Employee,
    m.emp_name AS Manager
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.emp_id;

How This Query Works

employees e - represents employees

employees m - represents managers

e.manager_id = m.emp_id - links employee to manager

Aliases (e, m) differentiate same table

Only employees having managers are shown, CEO is excluded (no manager)

Output

Employee	Manager
Neha	Amit
Rahul	Amit
Priya	Neha
Karan	Neha

Including Employees Without Managers

If you want to include top-level employees (CEO)

SELECT 
    e.emp_name AS Employee,
    m.emp_name AS Manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

Output

Employee	Manager
Amit	NULL
Neha	Amit
Rahul	Amit
Priya	Neha
Karan	Neha

Common Use Cases of Self JOIN

Self JOIN is commonly used for:

Employee - Manager hierarchy

Category - Sub-category mapping

Parent - Child relationships

Referral systems

Organizational charts

Different Between Self JOIN and Normal JOIN

Aspect	Normal JOIN	Self JOIN
Tables involved	Two different tables	Same table
Use case	Related entities	Hierarchical data
Aliases required	Optional	Mandatory

Two Minute Drill

Self JOIN joins a table with itself
Used for hierarchical data
Aliases are mandatory
Employee Manager is the most common example
Works with INNER / LEFT JOIN
Same table, different logical roles