Joins in SQL Server

By this article I am trying to show you the joins in a different way i.e. in math terms by the sets as well as the query which will give you the return in one single click of Execute in Management Studio.

Joins : Joins basically returns the result set by making a merge between two tables (which is at the row level).

Types Of Joins

1. Inner Join
2. Outer Join

Let's consider the 2 sets that we have in math:

1. Natural Number {Column name = iNATURAL_NUMBER int}
2. Even-Odd Numbers (shuffle i.e. some from Even and some from ODD) {Column name = iEVEN_ODD_NUMBER int}
   
   #1 is our set/Table A={0,3,4,7,8,9,10,11,13}
   #2 is our next set/Table B={0,2,3,6,7,10,11,14}
   
   Let's consider we have the above data in 2 tables, that is:
   
   In table A we have data 0,3,4,7,8,9,10,11,13 and in table B we have data 0,2,3,6,7,10,11,14
   
   
   

1. Inner Join

It is a simple join between two tables which returns the result sets for matching of rows based on the "on" clause of the join.

In math terms The Inner Join will be for A & B which is:

A Inner JOIN B ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,3,7,10,11}



2. Outer Join

It has 3 sub types

2.1. Left Outer Join
2.2. Right Outer Join
2.3. Full Outer Join

2.1. Left Outer Join

It is a join applied on the left table which returns the result sets for rows based on the "on" clause of the join and mis-matches result in NULL for the right table. It gives all the records from the left table and Null from the non-matches in the right table.

A Left Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,3,4,7,8,9,10,11,13}

If we used a Left Outer Join on the query then that results in a NULL for the right table in our example for example (4,8,9,13) gives (NULL,NULL,NULL,NULL,NULL) from the query for records which are not in the table B.



2.2. Right Outer Join

It is a join applied on a right table which returns the result sets for rows based on the "on" clause of the join and non-matches result in NULL from the left table. It gives all the records from the right table and Null from the non-matches in the left table.

A Right Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,2,3,6,7,10,11,14}
if we used a Right Outer Join on the query then it results in NULL for the left table in our example for example (2,6,14) gives (NULL,NULL,NULL) for the records from the query which is not in the table A.



2.3. Full Outer Join

It is a join applied to both tables which returns the result sets for rows based on the "on" clause of the join and non-matches result in NULL from the right as well as left table. It gives all the records from the right as well as the left table and Null for the non-matching records in the right as well as the left table.

A Full Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER = {0,2,3,4,6,7,8,9,10,11,13,14}

If we used a Full Outer Join in the query then that results in NULL for the right as well as left table; in our example for the right (4,8,9,13) gives (NULL,NULL,NULL,NULL,NULL) and for the left (2,6,14) gives (NULL,NULL,NULL) from the query which is not in the table A and B.



2.4. Left Outer Join With NULL

It will use the out put from the #2.1 and returns for only non matching from Right table

A Left Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER WHERE B.iEVEN_ODD_NUMBER is NULL = {4,8,9,13}

If we used Left outer Join with NULL on query gives all the records from the left table having null from right table in our example for (4,8,9,13) gives (NULL,NULL,NULL,NULL,NULL) from query which is not in the table B.



2.5. Right Outer Join With NULL

It will use the output from the #2.2 and returns only non-matching records from the left table.
A Right Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER WHERE A.iNATURAL_NUMBER is NULL = {2,6,14}
if we used a Right Outer Join with NULL in the query then that results in all the records from the left table having null from the left table; in our example (2,6,14) gives (NULL,NULL,NULL) from the query which is not in the table A.



2.6. Full Outer Join With NULL

It will use the output from the #2.3 and returns only non-matching records from the left table.

A Right Outer Join ON A.iNATURAL_NUMBER =B.iEVEN_ODD_NUMBER WHERE A.iNATURAL_NUMBER is NULL OR B.iEVEN_ODD_NUMBER IS NULL = {4,8,9,13,2,6,14}

If we used a Full Outer Join with NULL in the query then that gives all the records from the left & right tables having null from the left table; in our example (2,6,14) results in (NULL,NULL,NULL) and the right table in our example (4,8,9,13) gives (NULL,NULL,NULL,NULL,NULL) from the query which is not in the table A and B.