Open In App

Self Join and Cross Join in MS SQL Server

Improve
Improve
Like Article
Like
Save
Share
Report

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 –


Last Updated : 07 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads