This question already has an answer here:
Imagine the structure of a simple hotel booking app:
hotel_chain(id, name) hotel(id, hotel_chain_id, name) room(id, hotel_id) booking(room_id, date_start, date_end)
There are hotels, that belong to an organization (chain), every hotel has rooms, and you can book a room for a given amount of time (date range).
Now, I want to do a query to find which rooms are available in a specific range, for a specific hotel chain. Which would be the more efficient way of getting this information.
Right now I can come up with this query that has a left anti semi join:
select h.id, h.name, r.id from hotel h inner join room r on r.hotel_id = h.id where h.hotel_chain_id = @hotel_chain_id and not exists ( select b.id from booking b where b.room_id = r.id and @start <= b.date_end and @end >= b.date_start )
Which is the appropriated way of indexing for this query? Or a better query that can be indexed for high performance?