SELF JOIN — Joining a Table with Itself

A school leader table stores both staff and their supervisors. The report needs a person’s name beside that person’s supervisor.

A self join uses separate aliases for two references to the same table. Each alias represents a different role in the relationship.

The first table reference represents the person and the second represents the supervisor. Match the person’s supervisor key with the supervisor’s own key. A LEFT JOIN preserves a person with no supervisor; an INNER JOIN would omit that person.

Related concepts: Name Result Columns and Recursive Queries.

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.

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

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');

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

• 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.

A self join is not inherently a recursive search. One join follows one relationship level; deeper chains need repeated joins or a recursive query. Use aliases consistently so a comparison does not accidentally compare a row with itself.

Show each practice student beside the student with the preceding roll number, retaining the first student.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT child.name AS student, previous.name AS previous_student FROM practice_students AS child LEFT JOIN practice_students AS previous ON previous.roll_no = child.roll_no - 1 ORDER BY child.roll_no;

Both references read the same stored table. Their aliases assign different roles, and the left join keeps Aarav even though roll number zero is absent.

A self join uses separate aliases for two references to the same table. Each alias represents a different role in the relationship.

Both references read the same stored table. Their aliases assign different roles, and the left join keeps Aarav even though roll number zero is absent.

Continue your understanding with Name Result Columns and Recursive Queries.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.