Why Use the Between Operator When We Can Do Without It

Why use the BETWEEN operator when we can do without it?

BETWEEN can help to avoid unnecessary reevaluation of the expression:

SELECT  AVG(RAND(20091225) BETWEEN 0.2 AND 0.4)
FROM    t_source;

---
0.1998

SELECT  AVG(RAND(20091225) >= 0.2 AND RAND(20091225) <= 0.4)
FROM    t_source;

---
0.3199

t_source is just a dummy table with 1,000,000 records.

Of course this can be worked around using a subquery, but in MySQL it's less efficient.

And of course, BETWEEN is more readable. It takes 3 times to use it in a query to remember the syntax forever.

In SQL Server and MySQL, LIKE against a constant with non-leading '%' is also a shorthand for a pair of >= and <:

SET SHOWPLAN_TEXT ON
GO
SELECT  *
FROM    master
WHERE   name LIKE 'string%'
GO
SET SHOWPLAN_TEXT OFF
GO

|--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name] < 'strinH' AND [test].[dbo].[master].[name] >= 'string'),  WHERE:([test].[dbo].[master].[name] like 'string%') ORDERED FORWARD)

However, LIKE syntax is more legible.

Difference in SQL Between operator and = & = operator

Modern databases ship with very intelligent query execution optimisers. One of their main features is query transformation. Logically equivalent expressions can usually be transformed into each other. e.g. as Anthony suggested, the BETWEEN operator can be rewritten by Oracle (and MySQL) as two AND-connected comparisons, and vice versa, if BETWEEN isn't just implemented as syntactic sugar.

So even if there would be a difference (in performance), you can be assured that Oracle will very likely choose the better option.

This means that you can freely choose your preference, e.g. because of readability.

Note: it is not always obvious, what's logically equivalent. Query transformation rules become more complex when it comes to transforming EXISTS, IN, NOT EXISTS, NOT IN... But in this case, they are. For more details read the specification (chapter 8.3 between predicate):

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

SQL : BETWEEN vs = and =

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.)

SQL between not inclusive

It is inclusive. You are comparing datetimes to dates. The second date is interpreted as midnight when the day starts.

One way to fix this is:

SELECT *
FROM Cases
WHERE cast(created_at as date) BETWEEN '2013-05-01' AND '2013-05-01'

Another way to fix it is with explicit binary comparisons

SELECT *
FROM Cases
WHERE created_at >= '2013-05-01' AND created_at < '2013-05-02'

Aaron Bertrand has a long blog entry on dates (here), where he discusses this and other date issues.

SQL Server: how to avoid between operator with datetime column?

You may use this if you don't want the row of 2011-04-06 00:00:00.000 date

SELECT *
FROM   dbo.mytable
WHERE  daycol >= '20110404' AND daycol < '20110406';

Does MS SQL Server's between include the range boundaries?

The BETWEEN operator is inclusive.

From Books Online:

BETWEEN returns TRUE if the value of
test_expression is greater than or
equal to the value of begin_expression
and less than or equal to the value of
end_expression.

DateTime Caveat

NB: With DateTimes you have to be careful; if only a date is given the value is taken as of midnight on that day; to avoid missing times within your end date, or repeating the capture of the following day's data at midnight in multiple ranges, your end date should be 3 milliseconds before midnight on of day following your to date. 3 milliseconds because any less than this and the value will be rounded up to midnight the next day.

e.g. to get all values within June 2016 you'd need to run:

where myDateTime between '20160601' and DATEADD(millisecond, -3, '20160701')

i.e.

where myDateTime between '20160601 00:00:00.000' and '20160630 23:59:59.997'

datetime2 and datetimeoffset

Subtracting 3 ms from a date will leave you vulnerable to missing rows from the 3 ms window. The correct solution is also the simplest one:

where myDateTime >= '20160601' AND myDateTime < '20160701'

Between operator does not return any value if the dates are equal

The date without a time is exactly midnight. If you want data on that date, I would recommend:

SELECT p.*
FROM Products p
WHERE datetime >= '2018-04-03' AND datetime < '2018-04-04';

This will return all times on 2018-04-03.

I strongly discourage using between with date/times. It is just confusing, because of the time component. Aaron Bertrand has a rather extensive blog on this subject, What Do The Devil and Between Have In Common. Although the blog is for SQL Server, it applies to other databases as well.

Difference between IN and ANY operators in SQL

SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>

SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SELECT *
  2  FROM employee
  3  WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
  2  FROM employee
  3  WHERE salary IN (2000, 3000, 4000);

But with the IN operator you cannot use =, <>, <, >, <=, or >=

---

Related Topics