Joins in SqlServer

Author : Sathya Sandeep

Join :Suppose If we want to retrive the Data from two or more tables we are using the "Joins" concept.Create a two tables with the following Data :
      Tbl_LeftTable       
                              

ID	Name
1	One
2	Three
3	Five
4	seven
5	Nine
6	Eleven
7	Thirteen

 Tbl_RightTable

ID	Name
1	Two
2	Four
3	Six
4	Eight
8	Ten
9	Twelve
10	fourteen

 Types Of Join :

Inner Join:  The INNER JOIN keyword return rows when there is at least one match in both tables.This join returns rows when there is at least one match in both the tables.

Syntax :  Select <SelectList> From <table1> [INNER] JOIN <table2> on < table1>.<col1> = < table2>.<col2>

 The Above Query will result following Out put :

Name	Name
One	Two
Three	Four
Five	Six
seven	Eight

LEFT JOIN ( Left Outer Join): The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Syntax :  Select <SelectList> From <table1> [LEFT] JOIN <table2> on < table1>.<col1> = < table2>.<col2>

 The Above Query will result following Out put :

 

Name	Name
One	Two
Three	Four
Five	Six
seven	Eight
Nine	Null
Eleven	Null
Twelve	Null

 

RIGHT JOIN ( RIGHT Outer Join): The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

Syntax :  Select <SelectList> From <table1> [RIGHT] JOIN <table2> on < table1>.<col1> = < table2>.<col2>

 The Above Query will result following Out put :

Name	Name
One	Two
Three	Four
Five	Six
seven	Eight
NULL	Ten
NULL	Twelve
NULL	fourteen

 The LEFT Join are also called as "LEFT OUTER JOIN" and Right Join are also called as "RIGHT OUTER JOIN".

FULL JOIN( Full Outer Join): The FULL JOIN keyword return rows when there is a match in one of the tables.

Syntax :  Select <SelectList> From <table1> [FULL] JOIN <table2> on < table1>.<col1> = < table2>.<col2>

  The Above Query will result following Out put :

Name	Name
One	Two
Three	Four
Five	Six
seven	Eight
Nine	NULL
Eleven	NULL
Twelve	NULL
NULL	Ten
NULL	Twelve
NULL	fourteen

 

Self-Join: A self-join, also known as an inner join, where a queried table is joined to itself. The self-join statement is necessary when two sets of data, within the same table, are compared.

 

Cross-Join: SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

 which is similar to the A X B opertion that we do on the sets in the Mathmetics.

Syntax : Select <SelectList> From <table1> [Cross] JOIN <table2> 

The Above Query will result following Out put of 49 rows in all combinations.