2

I have an employee table as below

select * from employee

I need to select employee_id, manager_id, result. result should have true or false depends on below conditions.

  1. If the employee is manager for someone then true
  2. If the employee has a manager then true

I came up with a query, but I need to know if there are any other better way to do it. This is my query

with manager  as
(
select distinct manager_id from employee where manager_id is not null
)
select 
e.employee_id, e.manager_id , m.manager_id, 
case when e.manager_id is not null then true
     when m.manager_id is not null then true 
     else false
end as Result
from employee e left join manager m  on e.employee_id = m.manager_id

and the result should come like this

enter image description here

Kavi
  • 140
  • 1
  • 9

1 Answers1

2

Maybe something like this:

select
  e.employee_id,
  e.manager_id,
  (
    e.manager_id is not null
    or
    exists(select 1 from employee m where m.manager_id=e.employee_id)
  ) as result
from employee e;
Tometzky
  • 22,573
  • 5
  • 59
  • 73
gotqn
  • 42,737
  • 46
  • 157
  • 243
  • 2
    Please note that this would benefit from index on `employee(manager_id)`. – Tometzky Sep 16 '20 at 06:54
  • comparing with my query, will this work efficiently?(fast)? how? – Kavi Sep 16 '20 at 08:47
  • @Kavi it depends on the data volume. You can use `SET STATISTICS IO ON, TIME ON` to detect performance metric. – gotqn Sep 16 '20 at 09:01
  • @Kavi . . . This is faster -- it really doesn't depend on anything. There is no additional overhead for removing duplicates. `select distinct` requires additional processing. – Gordon Linoff Sep 16 '20 at 11:53
  • thanks for your inputs. I need one more help, is there any reference to build a faster query. because every time I managed to find the solution, but my query is not faster. what are the possible ways to build an efficient query? – Kavi Sep 17 '20 at 04:37
  • @Kavi, the best way is always to test it with real data :-) and with the time, you will get more experiences in writing SQL. You can always double check your solutions in places like StackOverflow. – gotqn Sep 17 '20 at 05:05