Error getting SQL reservations and availablity

I am trying to find the rooms which are not booked from the yyyy-mm-dd to yyyy-mm-dd. I am getting the rooms which are booked technically. How to invert the result?

My reservations:

This is my rooms table

SELECT distinct r.room_id 
FROM rooms as r 
INNER JOIN  reservations as b ON r.room_id = b.room_id
WHERE ('2021-01-20' NOT BETWEEN date_from AND date_to) 
  AND ('2021-01-07' NOT BETWEEN date_to AND date_from) 

When I run this query, I get rooms which are booked. I want to get all the rooms which are not booked for example in this case I should be able to see 7 which is not booked between mentioned dates.

Answer

You may try the following modification:

A LEFT JOIN was used instead to capture rooms which may not yet have a reservation. I also switched the date_to AND date_from to date_from AND date_to and added an additional clause OR b.reservation_id IS NULL to capture rooms that may not have been booked as yet

SELECT DISTINCT
    r.room_id 
FROM  
    rooms as r 
LEFT JOIN  
    reservations as b ON r.room_id = b.room_id
WHERE (('2021-01-20' NOT BETWEEN date_from AND date_to) 
    AND ('2021-01-07' NOT BETWEEN date_from AND date_to) 
 ) OR b.reservation_id IS NULL