SQL query to check if column includes some data

Question

I need to select all free rooms from hotel DB and I think I can do it with two steps:

1. bookings = select * from booking where booking.startDate>=selectedStartDate and booking.endDate=<selectedEndDate.
2. pseudo query: select * from room where room.room_id not includes bookings.room_id.

I wrote my second query like pseudo query because I can't find how I can do what I want to do. How to check that bookings doesn't includes room_id's?

My booking table looks like:

+-----------+------------+------------+---------+---------+
| bookingId | startDate  | endDate    | room_id | guestId |
+-----------+------------+------------+---------+---------+
|         1 | 2016-03-12 | 2016-03-22 |       1 |       1 |
|         2 | 2016-03-12 | 2016-03-22 |       2 |       2 |
+-----------+------------+------------+---------+---------+

Answer 1

You could transform the first query to a subquery of the second query by using the not in operator:

SELECT *
FROM   room
WHERE  room.room_id NOT IN (SELECT room_id
                            FROM   booking
                            WHERE  startDate >= selectedEndDate AND
                                   endDate <= selectedStartDate)

Answer 2

If you want rooms free during a period of time, use not exists. The correct logic is:

select r.*
from room r
where not exists (select 1
                  from booking b
                  where $startdate <= b.enddate and $enddate >= b.startdate
                 );

Two periods overlap when one starts before the second ends and the first ends after the second starts.

Note that the <= and >= might be strict inequalities, depending on whether the first and last dates are included in the period.

Answer 3

You can use a more optimized query

SELECT * FROM room JOIN booking ON room.room_id = booking.room_id
WHERE booking.startDate >= selectedStartDate AND booking.endDate <= selectedEndDate