MySQL using BETWEEN comparison with NULL

Question

I have a query with a where condition like so:

WHERE A.event_date BETWEEN B.start_date AND B.end_date

The complexity is that if B.start_date is NULL, it means from time immemorial, similarly B.end_date is NULL, it means the present. So I still want to select a row where A.event_date > B.start_date and B.end_date is NULL, for example.

The long-winded way to do this is

WHERE A.event_date BETWEEN B.start_date AND B.end_date
   OR (A.event_date > B.start_date AND B.end_date IS NULL)
   OR (B.start_date IS NULL AND A.event_date < B.end_date)

Is there a more elegant solution (especially since in one query I have multiple between condition like that)

Answer 1

IFNULL might help out here:

WHERE A.event_date BETWEEN IFNULL(B.start_date,"1900-01-01") 
                   AND IFNULL(B.end_date,now());