SQL Server - SQL JOINS - INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

 

SQL JOINS

SQL Joins are used to combine data or rows from two or more tables by generating link between a common field/keyfield. There are 4 different types of Joins available in SQL.

 

·         INNER JOIN

·         LEFT JOIN

·         RIGHT JOIN

·         FULL JOIN

 

INNER JOIN:

INNER JOIN is used to fetch all the records from two 2 tables as long as the condition is satisfied or match. It’s result of all the records from both tables which match the condition.   

Employee Table

Emp_Id	Name	City
1	Rajesh	Ahmedabad
2	Nayana	Bangalore
3	Pankaj	Cochin
4	Lokesh	Mumbai

 

Department Table
Dept_Id	Dept_Name	Emp_Id
1	Sales	1
2	Marketing	1
3	HR	2
4	Production	3

 

Example :

SELECT Employee.Emp_Id,

               Employee.Name,

               Department.Dept_Name

FROM Employee

INNER JOIN Department

ON Employee.Emp_Id = Department.Emp_Id;

 

LEFT JOIN:

LEFT JOIN is used to fetch all the records from Table1 and matching records from the Table2 based on the condition. In case there is matching row in Table2, result will be shown as NULL.  It is even called as LEFT OUTER JOIN

Employee Table

Emp_Id	Name	City
1	Rajesh	Ahmedabad
2	Nayana	Bangalore
3	Pankaj	Cochin
4	Lokesh	Mumbai

 

Department Table
Dept_Id	Dept_Name	Emp_Id
1	Sales	1
2	Marketing	1
3	HR	2
4	Production	3

 

Example :

SELECT Employee.Emp_Id,

               Employee.Name,

               Department.Dept_Name

FROM Employee

LEFT JOIN Department

ON Employee.Emp_Id = Department.Emp_Id;

 

RIGHT JOIN:

RIGHT JOIN is used exactly opposite of LEFT JOIN. RIGHT JOIN fetch all the records from Table2 and matching records from the Table1 based on the condition. In case there is matching row in Table1, result will be shown as NULL. It is even called as RIGHT OUTER JOIN

Employee Table

Emp_Id	Name	City
1	Rajesh	Ahmedabad
2	Nayana	Bangalore
3	Pankaj	Cochin
4	Lokesh	Mumbai

 

Department Table
Dept_Id	Dept_Name	Emp_Id
1	Sales	1
2	Marketing	1
3	HR	2
4	Production	3

 

Example :

SELECT Employee.Emp_Id,

               Employee.Name,

                Department.Dept_Name

FROM Employee

RIGHT JOIN Department

ON Employee.Emp_Id = Department.Emp_Id;

 

FULL JOIN:

FULL JOIN fetch all the records from Table1 and Table2 when there is match from both tables. In case there is matching BETWEEN Table1 and Table2, result will be shown as NULL. It is even called as FULL OUTER JOIN

Employee Table

Emp_Id	Name	City
1	Rajesh	Ahmedabad
2	Nayana	Bangalore
3	Pankaj	Cochin
4	Lokesh	Mumbai

 

Department Table
Dept_Id	Dept_Name	Emp_Id
1	Sales	1
2	Marketing	1
3	HR	2
4	Production	3

 

Example :

SELECT Employee.Emp_Id,

               Employee.Name,

                Department.Dept_Name

FROM Employee

FULL JOIN Department

ON Employee.Emp_Id = Department.Emp_Id;