SQL BETWEEN vs and

Question

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  gt   10 15 2009    AND EventDate  lt   10 18 2009     Edit  the second Eventdate was originally missing  so the query was syntactically wrong

User · Answer

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  gt    2009-10-15  AND         EventDate  lt    2009-10-19          lt  lt  lt    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

User · Answer

Disclaimer   Everything below is only anecdotal and drawn directly from my personal experience   Anyone that feels up to conducting a more empirically rigorous analysis is welcome to carry it out and down vote if I m   I am also aware that SQL is a declarative language and you re not supposed to have to consider HOW your code is processed when you write it  but  because I value my time  I do   There are infinite logically equivalent statements  but I ll consider three ish    Case 1  Two Comparisons in a standard order  Evaluation order fixed      A    MinBound AND A  lt   MaxBound      Case 2  Syntactic sugar  Evaluation order is not chosen by author      A BETWEEN MinBound AND MaxBound   Case 3  Two Comparisons in an educated order  Evaluation order chosen at write time      A    MinBound AND A  lt   MaxBound   Or     A  lt   MaxBound AND A    MinBound   In my experience  Case 1 and Case 2 do not have any consistent or notable differences in performance as they are dataset ignorant    However  Case 3 can greatly improve execution times   Specifically  if you re working with a large data set and happen to have some heuristic knowledge about whether A is more likely to be greater than the MaxBound or lesser than the MinBound you can improve execution times noticeably by using Case 3 and ordering the comparisons accordingly   One use case I have is querying a large historical dataset with non-indexed dates for records within a specific interval   When writing the query  I will have a good idea of whether or not more data exists BEFORE the specified interval or AFTER the specified interval and can order my comparisons accordingly   I ve had execution times cut by as much as half  depending on the size of the dataset  the complexity of the query  and the amount of records filtered by the first comparison

User · Answer

Logically there are no difference at all  Performance-wise there are -typically  on most DBMSes- no difference at all

User · Answer

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  gt   first AND dt  lt  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

User · Answer

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 gt   2009-04-17  and transactiondate lt   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 gt   2009-04-17    and salestype  A    and customernumber customer idnumber   and transactiondate lt   2009-04-22    If they try this with a BETWEEN  of course  it will be a syntax error and promptly fixed

User · Answer

I think the only difference is the amount of syntactical sugar on each query  BETWEEN is just a slick way of saying exactly the same as the second query   There might be some RDBMS specific difference that I m not aware of  but I don t really think so

User · Answer

Typically  there is no difference - the BETWEEN keyword is not supported on all RDBMS platforms  but if it is  the two queries should be identical   Since they re identical  there s really no distinction in terms of speed or anything else - use the one that seems more natural to you

User · Answer

In this scenario col BETWEEN     AND     and col  lt       and col  gt       are equivalent   SQL Standard defines also T461 Symmetric BETWEEN predicate     lt between predicate part 2 gt         NOT   BETWEEN   ASYMMETRIC   SYMMETRIC     lt row value predicand gt  AND  lt row value predicand gt   Transact-SQL does not support this feature   BETWEEN requires that values are sorted  For instance  SELECT 1 WHERE 3 BETWEEN 10 AND 1 -- no rows   lt   gt   SELECT 1 WHERE 3  gt   10 AND 3  lt   1 -- no rows  On the other hand  SELECT 1 WHERE 3 BETWEEN SYMMETRIC 1 AND 10  -- 1  SELECT 1 WHERE 3 BETWEEN SYMMETRIC 10 AND 1 -- 1  It works exactly as the normal BETWEEN but after sorting the comparison values  db lt  gt fiddle demo

User · Answer

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    lt   20 10 2009   is not the same as    lt   20 10 2009 23 59 59    it would match against  lt   20 10 2009 00 00 00 000

User · Answer

See this excellent blog post from Aaron Bertrand about why you should change your string format and how the boundary values are handled in date range queries

User · Answer

They are identical  BETWEEN is a shorthand for the longer syntax in the question  Use an alternative longer syntax where BETWEEN doesn t work e g  Select EventId EventName from EventMaster where EventDate  gt    10 15 2009  and EventDate  lt   10 19 2009    Note  lt  rather than  lt   in second condition

[sql] SQL : BETWEEN vs <= and >=

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to where

Examples related to between