SELF JOIN in SQL

11/22/2025
All Articles

SQL SELF JOIN tutorial with examples and hierarchy explanation

SELF JOIN in SQL

SELF JOIN in SQL – SQL Tutorial for Beginners

A SELF JOIN is a type of SQL join in which a table is joined with itself. This is useful when the table contains hierarchical, relational, or comparative data.

In this beginner-friendly SQL tutorial, you’ll learn:

  • What SELF JOIN is

  • How it works

  • Syntax

  • Real-world examples

  • When to use SELF JOIN

  • Best practices

πŸ”Ή What Is SELF JOIN?

A SELF JOIN occurs when a table is joined with itself as if it were two separate tables.

To differentiate them, aliases are used.

βœ” Used to compare rows within the same table.

βœ” Useful for parent-child relationships and hierarchy data.

πŸ”Έ SELF JOIN Syntax

SELECT a.column1, b.column2
FROM table_name AS a
JOIN table_name AS b
ON a.common_field = b.common_field;

a and b represent two different references to the same table.

πŸ”Έ Example Table: employees

id name manager_id
1 Amit NULL
2 Neha 1
3 Rahul 1
4 Sameer 2

Here:

  • Amit is the manager of Neha & Rahul

  • Neha is the manager of Sameer

πŸ”Έ SELF JOIN Example: Employees and Their Managers

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

βœ” Output

employee manager
Amit NULL
Neha Amit
Rahul Amit
Sameer Neha

πŸ”Έ SELF JOIN for Finding Pairs in the Same City

Example table: users

id name city
1 Amit Delhi
2 Neha Delhi
3 Ravi Pune

Query:

SELECT u1.name, u2.name, u1.city
FROM users u1
JOIN users u2
ON u1.city = u2.city
AND u1.id <> u2.id;

Output:

name name city
Amit Neha Delhi
Neha Amit Delhi

πŸ”Έ SELF JOIN for Comparing Salaries (More Than Another Employee)

Table: employees

SELECT e1.name AS employee, e2.name AS higher_paid_employee
FROM employees e1
JOIN employees e2
ON e1.salary < e2.salary;

πŸ”Έ SELF JOIN for Hierarchies (Parent-Child)

Table: categories

id name parent_id
1 Electronics NULL
2 Mobile 1
3 Laptop 1

Query:

SELECT c1.name AS category, c2.name AS parent_category
FROM categories c1
LEFT JOIN categories c2
ON c1.parent_id = c2.id;

πŸ”Ή When to Use SELF JOIN

βœ” Working with hierarchical data (employees, categories)
βœ” Finding duplicates or similar rows
βœ” Matching rows based on relationships in the same table
βœ” Comparing data within the same table

πŸ”Ή Best Practices

βœ” Always use aliases (e1, e2) for clarity
βœ” Use proper indexes for performance
βœ” Avoid unnecessary comparisons (use <> when required)
βœ” LEFT JOIN helps show parent-less (NULL) records

Summary

In this SQL SELF JOIN tutorial, you learned:

  • What SELF JOIN is

  • How a table can be joined with itself

  • Real-world use cases like employee-manager and category hierarchies

  • Best practices and examples

SELF JOIN is extremely powerful when working with hierarchical or relational data stored in a single table.

Article