Need to see if a range of dates overlaps another range of dates in sql

I have a table which stores bookings of rooms, the schema is:

ID | ROOM_ID | CHECK_IN_DATE | CHECK_OUT_DATE | USER_ID

I need to run a search query for rooms which are available/unavailable between a set range of dates.

Also keep in mind that there exists another table which holds dates when the room is prebooked and its in the format:

ROOM_ID | DATE

SO I need to run a query which looks for rooms available within a set range, How would I formulate the query? I'm using MySQL here.

---edit---

Theres also a Rooms table of the schema:

ID | ROOM DETAILS etc

The unavailability/prebooked dates table basically holds sporadic single dates, each date in the unavailability table refers to a date when the room for some reason cannot be booked eg: maintenance etc

Solution

SELECT
   ROOM_ID
FROM
   Rooms r
   LEFT JOIN Bookings b ON (
      r.ROOM_ID = b.ROOM_ID
      AND b.CHECK_IN_DATE > '$MAX_DATE'
      AND b.CHECK_OUT_DATE < '$MIN_DATE'
   )

I'm not sure how pre-booked rooms factors in as there is no date range. Do pre-booked rooms also get an entry on bookings or not?