Where is the difference between using the mySQL BETWEEN operator and the classic date range?

Question

Possible duplicate:
The difference between SQL and the "> =" and "<="

I see that my developer uses this construct to select the data for the selected date interval:

SELECT ... WHERE `date` >= '2011-11-28' AND `date` <= '2011-12-04' 

I recommend him using a BETWEEN statement, like this one:

 SELECT ... WHERE date BETWEEN '2011-11-28' AND '2011-12-04' 

Which solution is better and what are the arguments for it?

Answer 1

AFAIK, there is no difference between these approaches in terms of implementation efficiency. But BETWEEN X AND Y seems more readable and enjoyable, so I would like to use this construct instead of date >= '2011-11-28' AND date <= '2011-12-04' .

BUT: If, for example, you write a simple date and time filter in your application, you need the ability to select only the first or only the second date - this is the case when you should use date >= '2011-11-28' AND date <= '2011-12-04' (we can omit the date> =' 2011-11-28 'or omit the date <=' 2011-12-04 'in our query builder without any problems).

Answer 2

I'm not sure if this is a “classic” date range, for example. for the period of the current month, if it were

 SELECT ... WHERE `date` >= '2011-12-01' AND `date` < '2012-01-01' 

The idea is that time is a continuum and a period infinitely divisible. In other words, it is theoretically impossible to determine the last time granule of the current month. Is this one microsecond until midnight January 1? One nanosecond?

Well, in practice, it may be possible to determine the minimum time granule using the temporary data type in the DBMS of your choice. However, this changes the time model from a continuum to a series of discrete points in time, and you will need to do a lot of extra work to ensure that comparisons are made using the same time granules. The classic mistake is that the current month is defined as start_date = 2011-01-12 end_date = 2011-12-31 during the pellet in one day, then someone wonders why the value of DATETIME 2011-12-31 12:00:00 does not fall in the current month or, even worse, does not fall in any month!

In contrast, it is easy to determine the first time granule, which is not included in the current month, that is, January 1, in the granule in one day. This is referred to in the literature as a closed presentation or “semi-open” and is apparently most widely used.

In conclusion, if you cannot guarantee that temporal values ​​of a pellet of less than one day can be effectively removed from your model (and I suggest you not do it!), Then use a closed view and avoid BETWEEN to verify that the values ​​fall over the period .

Answer 3

From MySQL Guide :

 expr BETWEEN min AND max 

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression ( min <= expr AND expr <= max ) if all arguments are of the same type.

Answer 4

I prefer to use BETWEEN because it is easier to read (in my opinion).

From a technical point of view (performance, what it returns) there is no difference