Comparing Multiple Date with between Mysql

I have this query

SELECT * 
FROM   `reservations` 
WHERE  '2014-09-04' BETWEEN check_in AND check_out 

In another case, I need to compare this check_in and check_out field with multiple dates. This query could solve that problem

SELECT * 
FROM   `reservations` 
WHERE  '2014-09-04' BETWEEN check_in AND check_out 
        OR '2014-09-09' BETWEEN check_in AND check_out 

But this query is not efficient if I have a lot of date to compare. Anybody have solution to make this query shorter?

Answers


Shorter query:

SELECT DISTINCT r.* 
 FROM reservations r
 JOIN (
  SELECT '2014-09-04' AS dt UNION
  SELECT '2014-09-09' AS dt
 ) dates ON dates.dt BETWEEN r.check_in AND r.check_out

If you do not have indexes on check_in and check_out, then multiple conditions with or is the best approach. If you do have indexes on these columns and they are being used in the first case, then try using union:

SELECT * 
FROM `reservations` 
WHERE '2014-09-04' BETWEEN check_in AND check_out 
UNION
SELECT * 
FROM   `reservations` 
WHERE  '2014-09-09' BETWEEN check_in AND check_out ;

Note that union incurs an overhead of removing duplicates. I am using it because you could have duplicates in the result and, presumably, you do not want them. In general, I recommend using union all when possible.


Need Your Help

Facebook Graph API {id}/feed?limit=x - restrict to messages since a certain message id

c# facebook facebook-graph-api

I have a small problem, I am working on an aggregation application that is collecting messages from pages in realtime.

WCF/REST return group associated with tags, fix datacontracts & datamembers

c# wcf web-services rest

So im stuck on a few things in each part below, they are pretty trivial to most of you on stack so just wondering if I can get help with three issues I have in the below code segments the issues I ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.