Self Join and Cross Join in MS SQL Server

Prerequisite – Introduction of MS SQL Server

1. Self Join :
Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table.

Syntax –

select 
select_list
from T t1 [Inner|Left] Join on T t2 
on join_predicate.

Here T refers to the table we use for comparison and it is referred twice. To avoid errors and confusion, t1 and t2 are used along T for comparing two rows in the same table. Inner join or Left join is used for self join to avoid errors.


2. Cross Join :
Cross join allows us to join each and every row of both the tables. It is similar to the cartesian product that joins all the rows.

Syntax –



select 
select_list 
from T1 cross join T2



Example –
Student and Course tables are picked from the university database.

Table – Student

Name Age Rollno
Aisha 19 111
Maya 18 112
Naina 18 113


Table – Course

Name Rollno Course
Aisha 111 CSE
Maya 112 EEE
Naina 113 ECE

1. Self Join :
A self-join is applied and the result set is the table below.

select n1.name, n2.name 
from Student n1 inner join Student n2 
on rollno n1 = rollno n2

NULL NULL



2. Cross Join :
Cross join is applied and the result set is the fourth table.

select *
from Student cross join Course

Name Age Rollno Name Rollno Course
Aisha 19 111 Aisha 111 CSE
Maya 18 112 Maya 112 EEE
Naina 18 113 Naina 113 ECE

Additional Articles –

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up


If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.