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?

Answer 1

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.