Skip to content
Related Articles

Related Articles

Improve Article

Self Join and Cross Join in MS SQL Server

  • Last Updated : 07 Aug, 2020

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
NameAgeRollno
Aisha19111
Maya18112
Naina18113


Table – Course
NameRollnoCourse
Aisha111CSE
Maya112EEE
Naina113ECE

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

NULLNULL



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

select *
from Student cross join Course

NameAgeRollnoNameRollnoCourse
Aisha19111Aisha111CSE
Maya18112Maya112EEE
Naina18113Naina113ECE

Additional Articles –

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :