SQLTeam.com | Weblogs | Forums

Self join with tables vs cte self join


#1

Hi team, I have a curiosity. please suggest me the best practices here.

I have a table test that contains empname and mgr_name columns as the name suggest empname contains empname and mgr_name contains manager name for the particular employee. my requirement is to find those employees who at least report to one manager + employees who are managers.

I write a query and that is returning correct records.

create table test(empname varchar(10),mgr_name varchar(10))

insert into test
values('a','b'),('b',null),('c',null),('d','f'),('f',null),('g',null),('h',null)

query :

select a.empname
from test a , test b
where a.mgr_name =b.empname
union
select b.empname
from test a , test b
where a.mgr_name =b.empname

i tried to optimize this query and use CTE instead of using tables in self join multiple times like

;with cte as (
select *
from test

)
select a.empname
from cte a , cte b
where a.mgr_name =b.empname
union
select b.empname
from cte a , cte b
where a.mgr_name =b.empname

But I found that there is no improvement showing in the execution plan.

I need your help to decide to optimize query in this case, please suggest if any other approach would be optimize in this case.


#2

create an index on mgr_name and empname. see what happens