MySQL LEFT JOIN 使用条件运算符(>= 和 <)不返回连接表的空值

Question

我有一个简单的酒店预订表 (booking)。我正在使用 MySQL。

booking_date    last_name   room_no     nights
2016-11-19      McDonnell   207         4
2016-11-20      Jenkins     203         5
2016-11-22      Ross        209         3
2016-11-23      Whitford    207         2
2016-11-27      Berry       207         2   

对于 2016-11-21 到 2016-11-27 期间的每一天，我想知道谁入住了 207 房间，不包括 checkout 该房间的任何人天。例如，我们可以看到 Whitford 在 2016-11-25 退房，因此他不应被列为房客。如果给定日期没有住户，我希望查询返回该日期的 NULL。

我创建了一个简单的日历(hotel_calendar):

StayDate    
2016-11-21  
2016-11-22  
2016-11-23  
2016-11-24  
2016-11-25  
2016-11-26  
2016-11-27

我试图通过匹配找到日历上每一天的占用者:

SELECT        StayDate AS 'Date',
              last_name AS 'Last Name'

FROM          hotel_calendar

LEFT JOIN     booking ON
              (StayDate >= booking_date AND   
              StayDate < booking_date + INTERVAL nights DAY)

WHERE         room_no = 207 AND 
              StayDate BETWEEN '2016-11-21' AND '2016-11-27';

我的结果:

Date        Last Name
2016-11-21  McDonnell
2016-11-22  McDonnell
2016-11-23  Whitford
2016-11-24  Whitford
2016-11-27  Berry

房间在 2016-11-25 和 2016-11-26 没有人住。即使我使用了 LEFT JOIN，查询也没有返回这些日期的 NULL 值。这是由于错误地使用了条件运算符吗？

Answer 1

首先，您应该限定所有列名，以便清楚它们的来源。其次，永远不要在列别名周围使用单引号——这只会导致问题。

但是，您真正的问题是LEFT JOIN 中second 表的条件需要进入ON 子句，而不是WHERE 子句。否则，JOIN 会变成 INNER JOIN:

SELECT c.StayDate AS Date, b.last_name
FROM hotel_calendar c LEFT JOIN
     booking b
     ON c.StayDate >= b.booking_date AND   
        c.StayDate < b.booking_date + INTERVAL nights DAY AND
        b.room_no = 207
WHERE c.StayDate BETWEEN '2016-11-21' AND '2016-11-27';