SQL SERVER - Types Of Joins

Getting the data from more than one table is called a Join.

There are five types of Joins.
1.Cross Join
2.Natural Join
3.Equi Join
4.Self Join
5.Outer Join

Cross Join : Cross Join is nothing but a Cartesian Product where getting the data from more than one table with out specifying any condition in the ON Clause. 

Example:

Select  EmpName                                                     From Employee CROSS JOIN Department

The result set contains                                              (No. of Records in Employee) * (No. of Records in Department) of Records

Natural Join : A Join that restricts  the redundant column data from the result set.

Example:

Select Emp.EmpId,Emp.EmpName,Dept.DeptName,Emp.Salary
From Employee Emp  JOIN Department Dept
ON  Emp.DeptId = Dept.DeptId

Here we will specify all the columns from the both tables, that we want to display, in the Select statement. Hence the Redundant Columns can be avoided.

If we did not specified the columns and simply used the ‘*’ in the select statement, then we can see the DeptId column twice, which can be avoided in this Natural Join.

Equi Join : A Join which displays the redundant Column data from the result set.Here we will use the ‘*’ symbol in the Select Statement.

Example:

Select  *
From Employee Emp  JOIN Department Dept
ON  Emp.DeptId = Dept.DeptId

As Specified above, Here we won’t specify the columns names in the Select statement instead we will use ‘*’ symbol. Hence the Redundant Columns will be shown.

In the above example, we can see the DeptId column twice one is from Employee table and another one is from Department table.

Self Join : A table can be joined to itself.That means, A Join in which One record of a table correlates with other records of the same table.

Example:

Select Mgr.LogID Manager,Emp.MgrID,Emp.LogID, Emp.EmpID
From Employee Emp JOIN Employee Mgr
ON  Emp.MgrID = Mgr.EmpID

The above example returns the list of all the managers and their employees that report to them. Here one record of the table  Employee correlates with other records of the same table

Outer Join : The result set contains all the rows from one table and matching rows from another.

Again the Outer Join can be of 3 types:

1. LEFT OUTER JOIN or LEFT JOIN                                        2. RIGHT OUTER JOIN or RIGHT JOIN                                    3. FULL OUTER JOIN or FULL JOIN

Example:

Let us consider two tables Product and Orders. Every Product May or May not be Ordered with in a period of time.

Now we want to get all the Product details, Whether it may be Ordered or not. In this case we will go for LEFT OUTER JOIN.

Select P.ProdId, ProdName, OrderNum
From Product P LEFT OUTER JOIN Orders O
ON  Product.PRODID = Orders.PRODID

In this case, In the Result set, for all the Products those are not Ordered, the OrderNum column will be Null.That means, the result set contains all matched records from the Orders  table and all unmatched records from the Product Table.

Similarly, In case of RIGHT OUTER JOIN, it will return all the matched records from the First table and all the unmatched records from the Second table.Null value will be placed for all the missing values at the

While In case Of FULL OUTER JOIN, the result set contains all the records from both tables. It will return NULLs for any values missing on either side.

Example:

Select CustID, CompName, O.OrderID                          From Customer C FULL OUTER JOIN Orders O                  ON  c.CustomerID = o.CustomerID