Self join concept in SQL

Introduction

A self join helps you compare rows in the same table by joining the table to itself.

Finding pairs of employees where one is the manager of the other in the same employee table.

Comparing products in a list to find those with similar prices.

Finding students who share the same class in a student table.

Checking for duplicate records based on some criteria within one table.

Syntax

SQL

SELECT A.column1, B.column2
FROM table_name A
JOIN table_name B ON A.common_field = B.common_field
WHERE condition;

Use table aliases (like A and B) to distinguish the same table used twice.

The ON clause defines how rows from the table relate to each other.

Examples

Finds each employee and their manager by joining the employees table to itself.

SQL

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

Finds pairs of different products with the same price.

SQL

SELECT p1.product_name, p2.product_name
FROM products p1
JOIN products p2 ON p1.price = p2.price
WHERE p1.id <> p2.id;

Sample Program

This query lists each employee with their manager's name by joining the employees table to itself.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  manager_id INT
);

INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2);

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id
ORDER BY e1.id;

Important Notes

Always use aliases to avoid confusion when joining a table to itself.

Self joins can be INNER JOIN, LEFT JOIN, or others depending on what you want to find.

Remember to handle NULLs if some rows don't have matching pairs.

Summary

Self join lets you compare rows within the same table.

Use table aliases to treat the same table as two different tables.

It is useful for hierarchical or paired data inside one table.