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.