MainMenu

Home Java Overview Maven Tutorials

Sunday 15 January 2017

Left Join, Right Join, Inner Join with example

Inner Join with example

It will return only the matching records of both the table.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Inner join Table2
On table1.column_name = table2.column_name


consider the following two tables : Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale
03NoidaMale
04GurgaonFemale

Query for inner Join Select *
from Salary Inner join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software Analyst03NoidaMale
------------------------------------------------------------------------------------------------------------------

Left Join with example

All the records of first table will appear & only matching record of second table will appear.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Left join Table2 On table1.column_name = table2.column_name


consider the following two tables : 1.Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

2.Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale

Query for inner Join Select *
from Salary
Left join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software AnalystNULLNULLNULL
-----------------------------------------------------------------------------------------------------------------

Right Join with example

All the records of second table will appear & only matching record of first table will appear.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Right join Table2 On table1.column_name = table2.column_name


consider the following two tables : 1.Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst

2.Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale
03NoidaFemale
04GurgaonMale

Query for right Join Select *
from Salary
right join Employee_detail
on salary.Emp_id = Employee_detail.Emp_id
Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software Analyst03NoidaFemale
NullNullNull04GurgaonMale

Tag: Inner Join with example, what is inner join?, Left join with example,What is left join?,Right join with example,what is right join?

No comments:

Post a Comment