Query with multiple EXIST

Question

I've got a database of rooms and equipments. I want to query the database and return a list of rooms with e.g. tv, radio, sat and fridge (eq1, eq2, eq3, ...., eqN).

I have the following SELECT statement:

select * from rooms r where 
exists (select id from equipments where eq_id='eq1' and room_id=r.id)
and
exists (select id from equipments where eq_id='eq2' and room_id=r.id)
and
exists (select id from equipments where eq_id='eq3' and room_id=r.id)
.......
and
exists (select id from equipments where eq_id='eqN' and room_id=r.id)

Is there any way to optimize or making this shorter?

Answer 1

To shorten you could

select * 
from rooms r 
where @N = (select count(distinct eq_id) 
            from equipments 
            where eq_id IN ('eq1','eq2',...,'eqN') and room_id=r.id)

EDIT but not sure if it will actually make it faster... quite the opposite, the version with EXISTS AND EXISTS has a chance to prune execution branch on the first false, the above must actually count the distinct values (go through all records) and see what that value is.

So you should think what is faster:

• going once through all records related to a room (one correlated subquery) or
• running N (worst case) correlated (but highly selective subqueries) for each room

It depends on the statistics of your data (I would think that if most rooms don't have all the sought equipment in them then your initial version should be faster, if most rooms have all equipment in them then the proposed version might perform better; also if the EXISTS version is faster make an effort to first the queries that are most likely to fail i.e. first check for rarest equipment)

You can also try a version with GROUP BY

select r.* 
from rooms r join
     equipments e on r.id = e.room_id
group by r.id
where eg_id in ('eq1','eq2',...,'eqN')
having count(distinct e.eq_id) = @N

(above SQL not tested)

Answer 2

try this (I don't have any DB available to test it, also consider performance )

select * from
    rooms r,
    (
        select count(distinct id) as cnt, id from equipments  where eq_id in ('eq1','eq2') group by id
    ) as sub
where sub.id = r.id 
and sub.cnt >= 2 'Options count

Note: 2 - it is the number of options that you need. In example they are: 'eq1','eq2'