让我们把它分解成两个子问题。
把从来没有订过的房间都订了。
获取所有从不与预定房间时间冲突的房间。
在查询中，我假设 $start_date 以及 $end_date 是存储有用户选择的值的php变量。

(select * 
from rooms where room_id NOT IN (select room_id from reservations))

UNION

(select *
from rooms where room_id IN (
   select room_id 
   from reservations 
   where $start_date not BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   and $end_date not between date_at and date_add(date_at, INTERVAL period day)
   and ($start_date > date_add(date_at, INTERVAL period day) OR $end_date < date_at)
))

你不需要做2 IN 查询。从你第一次 IN 查询您只有 NOT 子句，您可以将其更改为 NOT IN 然后取下 NOT 子查询中的。这也将删除第二个 OR IN .

select * 
from rooms 
where id not in (
   select room_id 
   from reservations 
   where '2018-6-11' BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   or '2018-6-30' between date_at and date_add(date_at, INTERVAL period day)
   or date_at between '2018-6-11' and '2018-6-30' 
   or date_add(date_at ,INTERVAL period day ) between '2018-6-11' and '2018-6-30' ))

基本上，这个查询翻译成：给我所有没有预订的房间，我的开始或结束日期在预订时间之间。
编辑：
多亏了@vivekè23，我意识到不能根据内部where条件简化查询-尽管如此，这个版本将数据库的查询量减少到2。

转换呢 IN 子句转换为等价联接？如果在您的案例中有效，请参见下面的查询：

SELECT *
FROM rooms rm
JOIN 
   (SELECT room_id rmid
  FROM reservations
  WHERE '2018-6-11' NOT BETWEEN date_at AND date_add(date_at, INTERVAL period DAY)
  AND '2018-6-30' NOT BETWEEN date_at AND date_add(date_at, INTERVAL period DAY)
  AND date_at NOT BETWEEN '2018-6-11' AND '2018-6-30'
  AND date_add(date_at ,INTERVAL period DAY ) NOT BETWEEN '2018-6-11' AND '2018-6-30'
  ) tb1  
ON  rm.ID = tb.rmid 
JOIN reservations rv
ON rm.ID <> rv.room_id;