Why use the BETWEEN operator when we can do withou

问题:

As seen below the two queries, we find that they both work well. Then I am confused why should we ever use BETWEEN because I have found that BETWEEN behaves differently in different databases as found in w3school

SELECT *
FROM employees
WHERE salary BETWEEN 5000 AND 15000;

SELECT *
FROM employees
WHERE salary >= 5000
AND salary <= 15000;

回答1:

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.

回答2:

Using BETWEEN has extra merits when the expression that is compared is a complex calculation rather than just a simple column; it saves writing out that complex expression twice.

回答3:

BETWEEN in T-SQL supports NOT operator, so you can use constructions like

WHERE salary not between 5000 AND 15000; 

In my opinion it's more clear for a human then

WHERE salary < 5000 OR salary > 15000;

And finally if you type column name just one time it gives you less chances to make a mistake

回答4:

The version with "between" is easier to read. If I were to use the second version I'd probably write it as

5000 <= salary and salary <= 15000

for the same reason.

回答5:

I vote @Quassnoi - correctness is a big win.

I usually find literals more useful than the syntax symbols like <, <=, >, >=, != etc. Yes, we need (better, accurate) results. And at least I get rid of probabilities of mis-interpreting and reverting meanings of the symbols visually. If you use <= and sense logically incorrect output coming from your select query, you may wander some time and only arrive to the conclusion that you did write <= in place of >= [visual mis-interpretation?]. Hope I am clear.

And aren't we shortening the code (along with making it more higher-level-looking), which means more concise and easy to maintain?

SELECT * 
FROM emplyees 
WHERE salary between 5000 AND 15000; 



SELECT * 
FROM emplyees 
WHERE salary >= 5000 AND salary <= 15000; 

First query uses only 10 words and second uses 12!

回答6:

Personally, I wouldn't use BETWEEN, simply because there seems no clear definition of whether it should include, or exclude, the values which serve to bound the condition, in your given example:

SELECT *
FROM emplyees
WHERE salary between 5000 AND 15000;

The range could include the 5000 and 15000, or it could exclude them.

Syntactically I think it should exclude them, since the values themselves are not between the given numbers. But my opinion is precisely that, whereas using operators such as >= is very specific. And less likely to change between databases, or between incremements/versions of the same.

---

Edited in response to Pavel and Jonathan's comments.

As noted by Pavel, ANSI SQL (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) as far back as 1992, mandates the end-points should be considered within the returned date and equivalent to X >= lower_bound AND X <= upper_bound:

8.3

     Function

     Specify a range comparison.

     Format

     <between predicate> ::=
          <row value constructor> [ NOT ] BETWEEN
            <row value constructor> AND <row value constructor>


     Syntax Rules

     1) The three <row value constructor>s shall be of the same degree.

     2) Let respective values be values with the same ordinal position
        in the two <row value constructor>s.

     3) The data types of the respective values of the three <row value
        constructor>s shall be comparable.

     4) Let X, Y, and Z be the first, second, and third <row value con-
        structor>s, respectively.

     5) "X NOT BETWEEN Y AND Z" is equivalent to "NOT ( X BETWEEN Y AND
        Z )".

     6) "X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

回答7:

If the endpoints are inclusive, then BETWEEN is the preferred syntax.

Less references to a column means less spots to update when things change. It's the engineering principle, that less things means less stuff can break.

It also means less possibility of someone putting the wrong bracket for things like including an OR. IE:

WHERE salary BETWEEN 5000 AND (15000
  OR ...)

...you'll get an error if you put the bracket around the AND part of a BETWEEN statement. Versus:

WHERE salary >= 5000
 AND (salary <= 15000
  OR ...)

...you'd only know there's a problem when someone reviews the data returned from the query.

回答8:

Semantically, the two expressions have the same result.

However, BETWEEN is a single predicate, instead of two comparison predicates combined with AND. Depending on the optimizer provided by your RDBMS, a single predicate may be easier to optimize than two predicates.

Although I expect most modern RDBMS implementations should optimize the two expressions identically.

回答9:

worse if it's

  SELECT id FROM entries 
  WHERE 
     (SELECT COUNT(id) FROM anothertable WHERE something LEFT JOIN something ON...) 
     BETWEEN entries.max AND entries.min;

Rewrite this one with your syntax without using temporary storage.

回答10:

I'd better use the 2nd one, as you always know if it's <= or <

回答11:

In SQL, I agree that BETWEEN is mostly unnecessary, and can be emulated syntactically with 5000 <= salary AND salary <= 15000. It is also limited; I often want to apply an inclusive lower bound and an exclusive upper bound: @start <= when AND when < @end, which you can't do with BETWEEN.

OTOH, BETWEEN is convenient if the value being tested is the result of a complex expression.

It would be nice if SQL and other languages would follows Python's lead in using proper mathematical notation: 5000 <= salary <= 15000.

One small tip that wil make your code more readable: use < and <= in preference to > and >=.