Self JOIN in SQL
Table of Content:
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;
-- 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.
SubjectId |
SubjectName |
SubCategory |
SubjectId |
SubjectName |
SubCategory |
1 |
C |
IT |
1 |
C |
IT |
2 |
Java |
IT |
2 |
Java |
IT |
3 |
Python |
CSE |
3 |
Python |
CSE |
4 |
PHP |
CSE |
4 |
PHP |
CSE |