By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE room
(room_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,room CHAR(1) NOT NULL
);
INSERT INTO room VALUES
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'),
(6,'F');
CREATE TABLE reservation
(reservation_id INT NOT NULL PRIMARY KEY
,user_id INT NOT NULL
,room_id INT NOT NULL
,reservation_start DATE NOT NULL
,reservation_end DATE NULL
);
INSERT INTO reservation VALUES
(1,1,1,'2022-04-15','2022-04-30'),
(2,2,2,'2022-04-17',NULL),
(3,3,3,'2022-04-28','2022-05-05'),
(4,4,4,'2022-05-02','2022-05-07'),
(5,5,4,'2022-05-08','2022-05-09'),
(6,6,5,'2022-05-09','2022-05-11'),
(7,7,1,'2022-05-11','2022-05-13');
Records: 6 Duplicates: 0 Warnings: 0
Records: 7 Duplicates: 0 Warnings: 0
SELECT a.* FROM room a
LEFT
JOIN reservation b
ON b.room_id = a.room_id
AND b.reservation_start <= '2022-05-10'
AND (b.reservation_end >'2022-05-01' OR b.reservation_end IS NULL)
WHERE b.reservation_id IS NULL;
| room_id | room |
|---|---|
| 1 | A |
| 6 | F |