CODEWITHSUNDEEP
mysqlnullbetween
Heisenberg
Heisenberg

Reputation: 8806

MySQL using BETWEEN comparison with NULL

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)

Upvotes: 8

Views: 8342

Answers (3)

Meloman
Meloman

Reputation: 3712

IF PERFORMANCE MATTERS...

When I saw the @tom-mac answer I said : nice solution, I need to test it, and after I saw the @mw-goodjava answer and I wanted to compare.

According to this test http://blogs.x2line.com/al/archive/2004/03/01/189.aspx, it seems to be better to use IFNULL() instead of COALESCE() function but the best is still to use WHERE ... OR ... IS NULL like this :

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

So the query is more complex and not using BETWEEN but finaly I will choose that solution.

Upvotes: 2

Tom Mac
Tom Mac

Reputation: 9853

IFNULL might help out here:

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

Upvotes: 13

mw_goodjava
mw_goodjava

Reputation: 281

The COALESCE() function will return the first non-null value in the parameter list. Give this a try:

WHERE A.event_date BETWEEN COALESCE(B.start_date,'1900-01-01') and COALESCE(B.end_date,CURRENT_TIMESTAMP)

Upvotes: 11

Related Questions