Self Join In SQL Server

Self join is very useful in the case where there is a need for join in a single table itself.
When all of the data you want to retrieve is contained within a single table, but data needed to extract is related to each other in the table itself.

In our example, 3 columns emp_id, emp_name and emp_mgr. Now 

For example: Table named Emp_details containing information of all employees, Now you want to know the manager for every employee.


Follow as below, to create and insert the data to proceed for the self join example.


create table Emp_details 
(
 emp_id int,
 emp_name varchar(25),
 mgr_no int
)


insert into Emp_details 
select 1, 'Jeff', 4
union all
select 2, 'Mark', 4
union all
select 3, 'Ben', 4
union all
select 4, 'Kenny', NULL


After insertion, You will see Kenny's emp_id is 4 and her mgr_no is NULL which is 4 for others.
This says, Kenny is a manager for other employees and she has no manager defined.
Self join can be an inner or outer join, lets make a self join to retrieve details of employees with their respective manager.


select t1.emp_id, t1.emp_name, t2.emp_name from Emp_details as t1 inner join Emp_details as t2 on t1.mgr_no = t2.emp_id

sql joins

0 comments:

Post a Comment