SELF JOIN - Joining a Table with Itself

Introduction

Sometimes, the data you need to compare exists within the same table. For example, you want to find students from the same class, or match a student with another student. That’s where SELF JOIN comes in — it allows a table to be joined to itself.

What is SELF JOIN?

A SELF JOIN is a regular join, but the table is joined with itself. We use table aliases to differentiate between the "left" and "right" references.

Syntax

SELECT a.column1, b.column2
FROM table_name a
JOIN table_name b
ON a.common_column = b.common_column
AND a.column1 <> b.column1;

Sample Table – students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A', 'Delhi'),
(2, 'Diya Iyer', '9B', 'Chennai'),
(3, 'Sneha Patil', '10A', 'Pune'),
(4, 'Mehul Agarwal', '8C', 'Hyderabad'),
(5, 'Saira Bano', '10A', 'Delhi');

1. Find Students from the Same Class

Let’s list pairs of students who are in the same class but are not the same person.

SELECT a.name AS student_1, b.name AS student_2, a.class
FROM students a
JOIN students b
ON a.class = b.class
AND a.roll_no <> b.roll_no
ORDER BY a.name, b.name;

student_1      | student_2     | class
----------------+----------------+-------
Aarav Sharma   | Sneha Patil   | 10A
Aarav Sharma   | Saira Bano    | 10A
Sneha Patil    | Aarav Sharma  | 10A
Sneha Patil    | Saira Bano    | 10A
Saira Bano     | Aarav Sharma  | 10A
Saira Bano     | Sneha Patil   | 10A

Notice the duplicate pairs (e.g., Aarav–Sneha and Sneha–Aarav). We can fix that in the next step.

2. Avoiding Duplicate Pairs

To avoid repeating the same pair in reverse, use < on roll numbers:

SELECT a.name AS student_1, b.name AS student_2, a.class
FROM students a
JOIN students b
ON a.class = b.class
AND a.roll_no < b.roll_no;

student_1      | student_2     | class
----------------+----------------+-------
Aarav Sharma   | Sneha Patil   | 10A
Aarav Sharma   | Saira Bano    | 10A
Sneha Patil    | Saira Bano    | 10A

3. Use Case – Students from the Same City

Want to find students living in the same city (excluding self-pairs)?

SELECT a.name AS student_1, b.name AS student_2, a.city
FROM students a
JOIN students b
ON a.city = b.city
AND a.roll_no < b.roll_no;

student_1      | student_2     | city
----------------+----------------+--------
Aarav Sharma   | Saira Bano     | Delhi

Best Practices

• Always use aliases (like a and b) in SELF JOINs to distinguish the table references.
• Use < or > on IDs to avoid symmetric duplicates.
• Use meaningful filters in the ON clause to prevent large result sets.

Summary

SELF JOIN is perfect for comparing rows within the same table — identifying matches, relationships, or patterns among entries. Whether it’s finding classmates, neighbours, or similar scores — it’s your go-to for internal comparisons.

What’s Next?

Up next, we’ll explore CROSS JOIN — a more mathematical join that creates all possible combinations between two tables.