SELF JOIN

So far, we have joined different tables. In this lesson, we will learn how to join a table with itself.

This technique is called a SELF JOIN and is commonly used when a table has relationships within itself.

What is a SELF JOIN?

A SELF JOIN is a regular join where the same table appears twice in the query, using different aliases.

Even though it looks like two tables, it is actually one table playing two roles.

Why Do We Need SELF JOIN?

SELF JOIN is useful when:

• A table references itself
• Hierarchical relationships exist
• Rows are related to other rows in the same table

Common examples:

• Employee → Manager relationships
• Parent → Child categories
• Comparing rows within the same table

Example Table

Consider the employees table:

+----+----------+------------+
| id | name     | manager_id |
+----+----------+------------+
| 1  | Alice    | NULL       |
| 2  | Bob      | 1          |
| 3  | Charlie  | 1          |
| 4  | Diana    | 2          |
+----+----------+------------+
  

Here:

• Alice has no manager
• Bob and Charlie report to Alice
• Diana reports to Bob

SELF JOIN Syntax

SELF JOIN uses aliases to distinguish table roles.

SELECT a.column, b.column
FROM table_name a
JOIN table_name b
ON a.column = b.column;
  

Aliases a and b represent the same table in different roles.

SELF JOIN Example: Employee & Manager

Retrieve each employee along with their manager’s name.

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

Explanation:

• e represents employees
• m represents managers
• LEFT JOIN ensures employees without managers still appear

Understanding the Result

Output will look like:

• Alice → NULL
• Bob → Alice
• Charlie → Alice
• Diana → Bob

This clearly shows reporting relationships.

SELF JOIN for Comparison

SELF JOIN can also compare rows within the same table.

Example: Find employees with the same manager.

SELECT e1.name AS employee1,
       e2.name AS employee2
FROM employees e1
JOIN employees e2
ON e1.manager_id = e2.manager_id
AND e1.id <> e2.id;
  

This pairs employees who share the same manager.

Why Aliases Are Mandatory

Without aliases, SQL cannot distinguish which instance of the table you are referencing.

Aliases make SELF JOINs readable and correct.

SELF JOIN vs Other Joins

Common Beginner Mistakes

• Forgetting to use table aliases
• Joining the same column incorrectly
• Using INNER JOIN instead of LEFT JOIN unintentionally
• Creating infinite or meaningless matches

When to Use SELF JOIN

Use SELF JOIN when:

• A table references itself
• You need hierarchical relationships
• You need row-to-row comparison

What’s Next?

In the next lesson, we will explore Subqueries, which allow queries inside other queries.