sql - MySQL LEFT JOIN using conditional operators (>= and <) not returning null values for the joined table -

i have simple table hotel bookings (booking). i'm working 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    

for each day in period 2016-11-21 2016-11-27, want know occupied room 207, excluding checked out day. example, can see whitford checked out on 2016-11-25 should not listed occupant. if there no occupants given date, want query return null date.

i created simple calendar (hotel_calendar):

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

i attempted find occupant each day on calendar matching:

select        staydate 'date',               last_name 'last name'           hotel_calendar  left join     booking on               (staydate >= booking_date ,                  staydate < booking_date + interval nights day)          room_no = 207 ,                staydate between '2016-11-21' , '2016-11-27'; 

my results:

date        last name 2016-11-21  mcdonnell 2016-11-22  mcdonnell 2016-11-23  whitford 2016-11-24  whitford 2016-11-27  berry 

the room unoccupied on 2016-11-25 , 2016-11-26. though used left join, query didn’t return null values dates. due incorrect use of conditional operators?

first, should qualify column names, clear come from. second, never use single quotes around column aliases -- leads problems.

your real issue, though, conditions on second table in left join need go on clause, not where clause. otherwise, join turns inner join:

select c.staydate date, b.last_name hotel_calendar c left join      booking b      on c.staydate >= b.booking_date ,            c.staydate < b.booking_date + interval nights day ,         b.room_no = 207 c.staydate between '2016-11-21' , '2016-11-27';