sql - I can’t figure out how to improve this query

Question

I have this really huge table with several million records in it. And I would like to create a query with multiple where conditions on the same field.

I need to know how many employees have access to building A, but not to building B. Supposedly, any employee with access to building A should have access granted to building B, so I’m looking for anomalies.

So far, the best I’ve come up with is this:

select employee_id from personnel where building = A
minus
select employee_id from personnel where building = B;

But I’m pretty sure there must be a quicker and more efficent way to do this. Maybe some sort of subquery?

Thanks in advance.

question from:https://stackoverflow.com/questions/66068004/i-can-t-figure-out-how-to-improve-this-query

Answer 1

You can try one of the following:

select employee
from personnel
where building in (A, B)
having max(building) = min(building) and min(building) = A;

or:

select employee
from personnel
where building = A and
      not exists (select 1
                  from personnel p2
                  where p2.employee = p.employee and p2.building = B
                 );

In particular, this can use indexes on personnel(building, employee) and personnel(employee, building).