SQL : BETWEEN vs <= and >=
Solution 1
They are identical: BETWEEN
is a shorthand for the longer syntax in the question that includes both values (EventDate >= '10/15/2009' and EventDate <= '10/19/2009'
).
Use an alternative longer syntax where BETWEEN
doesn't work because one or both of the values should not be included e.g.
Select EventId,EventName from EventMaster
where EventDate >= '10/15/2009' and EventDate < '10/19/2009'
(Note <
rather than <=
in second condition.)
Solution 2
They are the same.
One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day:
<= 20/10/2009
is not the same as:
<= 20/10/2009 23:59:59
(it would match against <= 20/10/2009 00:00:00.000
)
Solution 3
Although BETWEEN
is easy to read and maintain, I rarely recommend its use because it is a closed interval and as mentioned previously this can be a problem with dates - even without time components.
For example, when dealing with monthly data it is often common to compare dates BETWEEN first AND last
, but in practice this is usually easier to write dt >= first AND dt < next-first
(which also solves the time part issue) - since determining last
usually is one step longer than determining next-first
(by subtracting a day).
In addition, another gotcha is that lower and upper bounds do need to be specified in the correct order (i.e. BETWEEN low AND high
).
Solution 4
I have a slight preference for BETWEEN
because it makes it instantly clear to the reader that you are checking one field for a range. This is especially true if you have similar field names in your table.
If, say, our table has both a transactiondate
and a transitiondate
, if I read
transactiondate between ...
I know immediately that both ends of the test are against this one field.
If I read
transactiondate>='2009-04-17' and transactiondate<='2009-04-22'
I have to take an extra moment to make sure the two fields are the same.
Also, as a query gets edited over time, a sloppy programmer might separate the two fields. I've seen plenty of queries that say something like
where transactiondate>='2009-04-17'
and salestype='A'
and customernumber=customer.idnumber
and transactiondate<='2009-04-22'
If they try this with a BETWEEN
, of course, it will be a syntax error and promptly fixed.
Solution 5
As mentioned by @marc_s, @Cloud, et al. they're basically the same for a closed range.
But any fractional time values may cause issues with a closed range (greater-or-equal and less-or-equal) as opposed to a half-open range (greater-or-equal and less-than) with an end value after the last possible instant.
So to avoid that the query should be rewritten as:
SELECT EventId, EventName
FROM EventMaster
WHERE (EventDate >= '2009-10-15' AND
EventDate < '2009-10-19') /* <<<== 19th, not 18th */
Since BETWEEN
doesn't work for half-open intervals I always take a hard look at any date/time query that uses it, since its probably an error.
![Dushan Perera](https://lh3.googleusercontent.com/-5H9oECyWgag/AAAAAAAAAAI/AAAAAAAAAKY/qjA3TvvHzVU/photo.jpg?sz=256)
Dushan Perera
❤️ building super 🚅 fast 🏎️ web experience 🚀 Azure functions <⚡> is my recent area of interest. Former ASP.NET MVP Please feel free to suggest improvements to my posts
Updated on September 08, 2021Comments
-
Dushan Perera almost 3 years
In SQL Server 2000 and 2005:
- what is the difference between these two
WHERE
clauses? - which one I should use on which scenarios?
Query 1:
SELECT EventId, EventName FROM EventMaster WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'
Query 2:
SELECT EventId, EventName FROM EventMaster WHERE EventDate >='10/15/2009' AND EventDate <='10/18/2009'
(Edit: the second Eventdate was originally missing, so the query was syntactically wrong)
-
mjv over 14 yearsThis is a quasi duplicate with stackoverflow.com/questions/1572840/sql-between-v1-and-v2
-
Irfy over 14 yearsnot really, the handling of datetime is slightly different, plus that was for SQL server 2008, and there is no way Shyju could be certain without asking that the answer would be the same for previous versions.
- what is the difference between these two
-
Adir D over 14 yearsI would add that I strongly recommend never using BETWEEN unless you are dealing with the DATE data type or have otherwise guaranteed that your datetime values will never have a time component. Being consistent about this will make it less likely that you'll use BETWEEN by mistake instead of >= and <, and either get some data in the query that you didn't mean to, or think that you were getting an additional day of data when you're not...
-
James Scott almost 8 yearsWould there be a second compiler step as BETWEEN gets converted to conditionals? I understand this is a bit pedantic but would there be an additional overhead?
-
xmashallax almost 8 yearsHow can one say they're identical if they aren't?!?
-
Tony Andrews almost 8 years@xmashallax because they are? How are they not?
-
xmashallax almost 8 yearsOP asked if <=, >= and BETWEEN are equal. You say yes, but in your note at the end you basically say no). Am I missing something here?!?
'2016-09-19' <= CURDATE() AND '2016-09-19' >= CURDATE()
will pass,CURDATE() BETWEEN '2016-09-19' AND '2016-09-19'
will not. -
Tony Andrews almost 8 years@xmashallax You are indeed missing something here! The OP asked if there was any difference between
between
and>=, <=
and I said there in not, they are identical. I then went on to say that for a different condition like>=, <
(note:<
not<=
) then you cannot usebetween
. I think I added that in response to a comment that asked me to. -
Tony Andrews almost 8 years@xmashallax Can you explain why (you say) "
'2016-09-19' <= CURDATE() AND '2016-09-19' >= CURDATE()
will pass,CURDATE() BETWEEN '2016-09-19' AND '2016-09-19'
will not"? -
xmashallax almost 8 yearsBut your second (<) example is exactly what BETWEEN behaves like. Your first (<=) one is not working for me. I googled if <=,>= and BETWEEN were identical, found this answer, changed my code and found an example that proves to me that they aren't identical ... that's my problem here.
-
xmashallax almost 8 yearsStrange...I think I got confused by the question, the writing of the answer, the comments and the fact that my code obviously has a bug now =)
-
David Andrei Ned over 7 yearsYou can simply use between '2009-10-20' and '2009-10-21' in that case to capture the day
-
Hans Kesting over 7 years@DavidAndreiNed that would also match '2009-10-21 00:00:00.000' - probably not what you want.
-
Paul over 7 yearsDredging up the past, I know, but ... @xmashallax: The second version posted by Tony allows for testing against dates that have a time element, though the version posted will only check for dates from
2009/10/15 00:00:00
to2009/10/17 23:59:59.999
(note: not2009/10/18 23:59:59.999
) inclusive. UsingBETWEEN
in such circumstances would not work properly as this is an inclusive (i.e. >= and <=) operator; any records that have a date and time value of2009/10/18 00:00:00
would be included in the result set. -
AndyS about 7 yearsReferences: Microsoft Docs; W3 Schools
-
geilt over 5 yearsYou would want field BETWEEN '2009-10-20 00:00:00' AND '2009-10-20 23:59:59' or field >= '2009-10-20 00:00:00' AND field <= '2009-10-20 23:59:59' to be absolutely certain.
-
Seth Flowers about 5 years@geilt Your examples would miss anything that occurred within the last second of the day... ie: in between 23:59:59 and 00:00:00 on the next day.
-
mickmackusa almost 5 yearsUm, what? Case 3 doesn't share the same logic as Case 1 and Case 2. If you want to see if
A
is greater than both bounds, then just check if theA
is greater than theMaxBound
. Your post needs some adjusting. -
geilt almost 5 years00:00:00 is the start of next day and why I use >= and <= and not > or <.But if you meant microseconds and you stores them then you would want to put the last and final microsecond as well.
-
LanchPad almost 5 yearsLooks like I made a typo on the equality operators. Good catch.
-
Michele La Ferla over 3 yearsbasically to make sure that everyone reading this post does not get confused, the
between
clause is inclusive of both dates, while when you use the>
and<
syntax, you can exclude any of the two dates at the start and end of the range. -
jmtennant about 3 yearsI misread this answer and used between incorrectly for months. This is not your fault, however others may skim your answer and draw the same conclusions I did. May I suggest writing one code block which shows the syntax which IS equivalent to between, and then have a code block which shows syntax which IS NOT equivalent to between.
-
Phil Goldenberg almost 3 years@geilt, I think to be absolutely certain you would use < '2009-10-21' so that everything before 10/21 00:00:00 is included, and there's no need to worry about microseconds.
-
Heberto Mayorquin over 2 yearsThere are claims that the AND operator, in opposition to the BETWEEN, does not make efficient use of the indexes here: datacamp.com/community/tutorials/sql-tutorial-query But that does not seem to make any sense considering it is just syntactic sugar, any comment on indexing?
-
Tony Andrews over 2 years@RamonMartinez I had never heard that. I can imagine that some DBMS might recognise that a BETWEEN filters a small range of indexed values, but with the separate predicates
d >= v1 and d <= v2
might just see 2 separate predicates, neither of which benefits from the index on its own. But that's just a guess!