Skip to content
Related Articles

Related Articles

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 –

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 –

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.

from Student n1 inner join Student n2 
on rollno n1 = rollno n2


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
Recommended Articles
Page :