SQL SELF JOIN

← Previous Next →

A Self Join is a join where a table is joined with itself to compare rows within the same table. Since the same table is used twice, aliases (temporary table names) are used to differentiate the two instances.

A Self Join is useful when:
      • A table contains related data within itself
      • You need to compare rows in the same table
      • There is a hierarchical relationship (like employee–manager)

Self Join can be implemented using:
      • INNER JOIN
      • LEFT JOIN
It is not a separate join type; it is simply joining one table to itself.

Syntax

SELECT a.column_name, b.column_name
FROM table_name a
JOIN table_name b
ON a.common_column = b.common_column;

Where:
a = first instance of the table
b = second instance of the same table

Example: Query (Employee and their Manager) 

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


Explanation:
The table Employees is joined with itself.
e represents the employee.
m represents the manager.

Example: Finding Duplicate Records Using Self Join in SQL 

SELECT a.student_id, a.name, a.email
FROM Students a
JOIN Students b
ON a.email = b.email
AND a.student_id <> b.student_id;

Explanation:
The Students table is joined with itself.
a and b are aliases of the same table.
a.email = b.email finds rows with the same email.
a.student_id <> b.student_id ensures the same row is not compared with itself.

Simple Summary:
Self Join = Table JOIN itself to compare its own rows

← Previous Next →
Topics