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
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
Share your thoughts in the comments
Please Login to comment...