Mastering Self JOIN in SQL: A Detailed Guide

The SQL SELF JOIN is used to join a table to itself as if the table were two tables; temporarily renaming at least one table in the SQL statement.

Syntax: Self JOIN

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

Prerequisite code:

-- Self Join
USE SQLExamples

DROP TABLE Subjects
DROP TABLE Chapters

CREATE TABLE Subjects(
	SubjectId INT,	
	SubjectName VARCHAR(30),
	SubCategory VARCHAR(30)
)


INSERT INTO Subjects VALUES 
(1, 'C', 'IT'),
(2, 'Java', 'IT'),
(3, 'Python', 'CSE'),
(4, 'PHP', 'CSE')

SELECT * FROM Subjects
 

Code: Self Join

SELECT *
FROM Subjects AS A, Subjects AS B
WHERE 
 A.SubjectId = B.SubjectId 

This will produce below output.