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

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

🔸 SELF JOIN for Finding Pairs in the Same City

Example table: users

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:

🔸 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

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.