Null = 63?

[NancyJ]

This query checks the availability of a property between 2 given dates by determining if there are any other bookings for that period.


SELECT booking.pid
FROM mia_booking
JOIN booking ON booking.bid = mia_booking.id
WHERE booking.pid = $pid AND DATE_FORMAT(arrival_date,'%Y-%m-%d') < '".date('Y-m-d',$dateto)."' AND DATE_FORMAT(departure_date,'%Y-%m-%d') > '".date('Y-m-d',$datefrom)."' and booking.confirm=1

My client wanted to exclude bookings that had a client_id of 63 - ie, if the client_id was 63 the property would still show as available so I added " and client_id !=63" to the query - and that should have worked - or so I thought, however bookings with NULL client_id were also being excluded (which is a lot of them)
so I changed that to and (client_id != 63 or client_id is null) and that works fine but why was it necessary? NULL !=63 so why were NULL results excluded?

[ralph l mayo]

NULL isn't equal to 63, but it isn't not equal to it either. (NULL = 63) and (NULL != 63) are both NULL, and NULL evaluated as a boolean works like false.

[chaosprime]

MySQL also has a null-safe comparison operator, <=>. So you could write your condition as (NOT client_id <=> 63).