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 -

• Full join and Inner join in MS SQL Server
• Left join and Right join in MS SQL Server