In a normalized database, data related to and entity may be stored in multiple tables. when you need to view data from related tables together, you can join the table with the help of common attributes.

Types Of Joins

In SQL Server we have Three types of Joins. Using these join we fetch the data from multiple tables based on conditions.

We have two table for demo  (Employee) and (Country).

Employee Table

ID Name Email Salary Country
1 Sumit Sumit123@.com 25000 1
2 Sandeep Sandeep321@.com 20000 2
3 Umesh Umesh456@.com 20000 2
4 Amit Amit456@.com 40000 3
5 John John@.com 25000 NULL

Country Table

ID Country
1 USA
2 INDIA
3 UK
4 GERMANY

 

Inner Join

An Inner Join is a default join, therefore you can also apply an inner join by using the Join keyword. In additional, you can use the inner join keyword.

Syntex :

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e JOIN Country c on e.Country=c.ID;

OR

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e INNER JOIN Country c on e.Country=c.ID;

Inner Join returns only the matching rows between both tables.

ID Name Email Salary Country
1 Sumit Sumit123@.com 25000 USA
2 Sandeep Sandeep321@.com 20000 INDIA
3 Umesh Umesh456@.com 20000 INDIA
4 Amit Amit456@.com 40000 UK

Left Join Or Left Outer Join

Left join returns all rows from the left table , even if there are no matches in the right table. this means that a left join returns all the values from the left table, and matched values from the right table or NULL in case of not matching join rows.

 

Syntex :

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e LEFT JOIN Country c on e.Country=c.ID;

OR

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e LEFT OUTER JOIN Country c on e.Country=c.ID;

Left Join returns all matching rows plus non matching rows from the left table.

ID Name Email Salary Country
1 Sumit Sumit123@.com 25000 USA
2 Sandeep Sandeep321@.com 20000 INDIA
3 Umesh Umesh456@.com 20000 INDIA
4 Amit Amit456@.com 40000 UK
5 John John@.com 25000 NULL

 

Right Join or Right Outer Join

The Right Join Keyword returns all records from the right table, and the matched records from the left table.The result is NULL from the left side, when there is no match.

Syntex :

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e RIGHT JOIN Country c on e.Country=c.ID;

OR

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e RIGHT OUTER JOIN Country c on e.Country=c.ID;

Right Join returns all records from the right table, and the matched records from the left table. The result is NULL from left side, when there is no match.

ID Name Email Salary Country
1 Sumit Sumit123@.com 25000 USA
2 Sandeep Sandeep321@.com 20000 INDIA
3 Umesh Umesh456@.com 20000 INDIA
4 Amit Amit456@.com 40000 UK
NULL NULL NULL NULL GERMANY

 

Full Join 

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (Matched or Unmatched) rows from the table on both sides of the join clause. It puts NULL on the place of matches not found.

Syntax:

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e FULL JOIN Country c on e.Country=c.ID;

Left Join returns all matching rows plus non matching rows from the left table.

ID Name Email Salary Country
1 Sumit Sumit123@.com 25000 USA
2 Sandeep Sandeep321@.com 20000 INDIA
3 Umesh Umesh456@.com 20000 INDIA
4 Amit Amit456@.com 40000 UK
5 John John@.com 25000 NULL
NULL NULL NULL NULL GERMANY

Use Where Clause In Join Statement :

SELECT e.ID,e.Name,e.Email,e.Salary,c.Country FROM Employee e  JOIN Country c on e.Country=c.ID Where e.ID=2;

Thanks For Reading …….