SQL why use 'between' instead of '>= and <='

Question

I'd like to understand any differences in using 'where num between 2 and 4' and 'where num >= 2 and num <= 4', as it applies to Oracle, Sybase-ASE and MSSQL.

From purely visual perspective, "between" looks better to me. However I'm more worried about performance and any other technical issues. (for example is it possible for execution plan to be different)

Is 'between' a purely cosmetic keyword? to avoid having the 'and', or does it offer something more?

`a between b and c` is syntactic sugar for `(b <= a and a <= c)`. It's just for convenience. — Andomar, Feb 27 '13 at 17:35
Simple answer: because it is available and you have to type less. — Art, Feb 27 '13 at 17:36
In SQL Server 2000 `BETWEEN` used to have an advantage in preventing double evaluation of sub queries but [no longer the case](http://blogs.msdn.com/b/craigfr/archive/2008/06/27/subqueries-in-between-and-case-statements.aspx) — Martin Smith, Feb 27 '13 at 17:37
BETWEEN would prevent the need to fetch `a` twice. Probably no big deal in "smart" SQL implementations, but may be not trivial in some less smart ones. — Hot Licks, Feb 27 '13 at 17:41
This is a very difficult question to answer because in some platforms BETWEEN is just syntactic sugar, but I don't know if anybody can provide a single authoritative answer that represents *all* platforms accurately. — Aaron Bertrand, Feb 27 '13 at 17:42

score 3 · Accepted Answer · answered Feb 27 '13 at 17:38

Sometimes using BETWEEN can save you from evaluating an operation more than once:

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

In the first query RAND() is only called once, but in the second query RAND is called twice, here BETWEEN saves you a second function call to RAND.

There is also something to be said for readability in SQL queries, queries can become massive, functions like BETWEEN help improve this.

score 2 · Answer 2 · answered Feb 27 '13 at 17:35

2

The database optimized queries, and should have the same performance either way.

That is, the optimizer will create a parse tree, and the two should have the same execution plan.

answered Feb 27 '13 at 17:35

Marlin Pierce

9,931
4
30
52

SQL why use 'between' instead of '>= and <='

2 Answers2