What is a self join in SQL?

A SELF JOIN in SQL is a join where a table is joined with itself.

In simple words:

SELF JOIN is used when rows within the same table are related to each other.

Why SELF JOIN is Needed

Sometimes:

Data relationships exist within same table

Examples:

Employee and Manager
Student and Mentor
Category and Parent Category

SELF JOIN helps retrieve these relationships.

Simple Understanding

SELF JOIN means:

Table JOIN Same Table

Real-Time Example

Suppose a company stores employees in a single table.

Employees Table

Employee ID	Employee Name	Manager ID
1	Naresh	NULL
2	Rahul	1
3	Arjun	1
4	David	2

Understanding the Table

Here:

Naresh is manager
Rahul reports to Naresh
Arjun reports to Naresh
David reports to Rahul

Problem

Manager information exists in:

Same employees table

We need SELF JOIN to fetch:

Employee name
Manager name

SELF JOIN Syntax

SELECT columns

FROM table_name alias1

JOIN table_name alias2
ON alias1.column = alias2.column;

SELF JOIN Example

SELECT e.employee_name,
       m.employee_name AS manager_name

FROM employees e

LEFT JOIN employees m
ON e.manager_id = m.employee_id;

Explanation

Alias	Meaning
e	Employee Table Reference
m	Manager Table Reference

Why Aliases are Used

Because:

Same table is used twice

Aliases help distinguish:

Employee records
Manager records

SELF JOIN Result

Employee	Manager
Naresh	NULL
Rahul	Naresh
Arjun	Naresh
David	Rahul

Important Observation

Naresh has:

NULL

because:

Top-level manager has no manager

SELF JOIN Internal Architecture

Employees Table
       |
       v
Create Alias e (Employees)
       |
       v
Create Alias m (Managers)
       |
       v
Match manager_id = employee_id
       |
       v
Generate Employee -> Manager Mapping

Visualization of SELF JOIN

Employees Table
--------------------------------

1  Naresh   NULL
2  Rahul    1
3  Arjun    1
4  David    2

--------------------------------

SELF JOIN Mapping

Rahul  -> Naresh
Arjun  -> Naresh
David  -> Rahul

How SELF JOIN Works Internally

Database:

Creates two virtual copies of same table
Matches rows using JOIN condition

SELF JOIN Query Flow

Table Loaded Twice
        |
        v
Apply Aliases
        |
        v
Apply JOIN Condition
        |
        v
Return Related Rows

Types of SELF JOIN

INNER SELF JOIN
LEFT SELF JOIN

INNER SELF JOIN

Returns:

Only rows having matching relationships

Example

SELECT e.employee_name,
       m.employee_name AS manager_name

FROM employees e

INNER JOIN employees m
ON e.manager_id = m.employee_id;

Result

Naresh will not appear because:

No matching manager exists

LEFT SELF JOIN

Returns:

All employees including top-level managers

Why LEFT JOIN is Commonly Used

Because:

Some rows may not have parent relationships

SELF JOIN vs Normal JOIN

Feature	SELF JOIN	Normal JOIN
Tables Used	Same Table	Different Tables
Purpose	Internal Relationships	External Relationships
Aliases Required	Yes	Optional

Real-Time Banking Example

Banking systems may use SELF JOIN for:

Employee hierarchy
Branch manager relationships

Example

Employee -> Supervisor

Real-Time E-Commerce Example

E-commerce systems use SELF JOIN for:

Product categories
Subcategories

Example Table

Category ID	Category Name	Parent Category ID
1	Electronics	NULL
2	Mobiles	1

SELF JOIN Example

SELECT c.category_name,
       p.category_name AS parent_category

FROM categories c

LEFT JOIN categories p
ON c.parent_category_id = p.category_id;

Result

Mobiles -> Electronics

Real-Time Learning Platform Example

Learning platforms may use SELF JOIN for:

Course prerequisites
Mentor relationships

Example

Advanced Java -> Core Java

SELF JOIN in Organizational Hierarchy

CEO
 |
 +--> Manager
         |
         +--> Team Lead
                  |
                  +--> Developer

Advantages of SELF JOIN

Handles hierarchical data
Supports recursive relationships
Useful for parent-child mapping
Improves relational modeling

Challenges of SELF JOIN

Complex queries
Difficult readability
Performance issues on large hierarchies

Performance Optimization Tips

Index relationship columns
Use proper aliases
Avoid unnecessary nested joins

Common Mistake

Many developers forget:

Aliases are mandatory in SELF JOIN

Incorrect Query

SELECT employee_name

FROM employees

JOIN employees
ON manager_id = employee_id;

Problem

Database cannot identify:

Which table reference belongs to which role

Correct Query

SELECT e.employee_name,
       m.employee_name

FROM employees e

JOIN employees m
ON e.manager_id = m.employee_id;

SELF JOIN Performance Consideration

SELF JOIN performance depends on:

Table size
Indexes
Hierarchy depth

Best Practices

Always use aliases
Index foreign key columns
Use LEFT JOIN for hierarchy reporting
Optimize hierarchical queries carefully

Professional Interview Answer

A SELF JOIN in SQL is a join where a table is joined with itself. It is used when rows within the same table have relationships with other rows in the same table, such as employee-manager hierarchies, category-parent category structures, or mentor-student relationships. SELF JOIN uses table aliases to differentiate multiple references of the same table and is commonly implemented using INNER JOIN or LEFT JOIN. It is widely used in hierarchical and recursive relational database queries.

Why Interviewers Like This Answer

Clearly explains internal table relationships
Includes real-world hierarchy examples
Shows alias usage understanding
Explains practical business applications
Includes SQL query examples

Frequently Asked Questions