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.
Name | Age | Rollno |
---|---|---|
Aisha | 19 | 111 |
Maya | 18 | 112 |
Naina | 18 | 113 |
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 –