Search between two dates in mysql

Question

I have two tables, and this is the name of the table "rooms" and the other is "orders"

Now I joined the two tables, I need the values ​​when I search between book_form = "2016-12-30"and book_to = "2016-12-31", it will be return true, because these two dates do not exist in the "orders" table, and when searching between book_form = "2016-12-30"and book_to = "2017-01-05"or book_form = "2017-01-03"and book_to = "2017-01-15"it will return false, because that this date exists in the order table.

This is my request.

select * from rooms join room_book on rooms.room_id = room_book.room_id where status = 'available' and room_book.book_from NOT BETWEEN '2016-12-30' AND room_book.book_to NOT BETWEEN '2016-12-31'

---

NOTE: Unfortunately, the column date is book_from 2017-01-01in the order table.

Answer 1

SQL- , :

SELECT *
FROM rooms
LEFT JOIN room_book
    ON room_book.room_id = rooms.room_id
    AND 'search_date' BETWEEN room_book.book_from AND room_book.book_to
WHERE rooms.room_id IS NULL
AND rooms.status = 'available'

, search_date .

LEFT JOIN, . IS NULL where , room_book.

Answer 2

select * 
from rooms 
join room_book on rooms.room_id = room_book.room_id 
where status = 'available' 
and room_book.book_from >= '2016-12-30' 
AND room_book.book_to <= '2016-12-31'

.