MainMenu

Home Core Java Overview Maven Tutorials

Sunday, 15 January 2017

Self Join and full outer join with example

Query for Self Join Table will be joined by itself, first we will give two different name of a table after that i will join these two table by inner join.
Syntax :

Select Column_Name1,Column_Name2
from Table1 as A
Inner join Table2 as B
On A.column_name = B.column_name


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

Query of self join:

select *
from salary as a
inner join salary as b
on a.Emp_id = b.Emp_id

Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_salaryEmp_desig
0125000Software Eng0125000Software Eng
0230000Senior Software Eng0230000Senior Software Eng
0325000Software Analyst0325000Software Analyst
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Query for Full Outer Join It is the combination of left join and right join means all the reocrds from both the table will appear.
Syntax :

Select Column_Name1,Column_Name2
from Table1
Full outer join Table2
On Table1.column_name = Table2.column_name


consider the following table : Salary:
Emp_idEmp_salaryEmp_desig
0125000Software Eng
0230000Senior Software Eng
0325000Software Analyst
0535000Software lead

Employee_detail:
Emp_idEmp_cityEmp_gender
01PuneMale
02DelhiFemale
03NoidaMale
04GurgaonFemale

Query of self join:

select *
from salary as a
inner join salary as b
on a.Emp_id = b.Emp_id

Result :
Emp_idEmp_salaryEmp_desigEmp_idEmp_cityEmp_gender
0125000Software Eng01PuneMale
0230000Senior Software Eng02DelhiFemale
0325000Software Analyst03NoidaMale
NullNullNull04GurgaonFemale
0535000Software leadNullNullNull

Tag:Outer Join with example, Self join with example

1 comment: