当我们不能使用BETWEEN运算符时，为什么要使用它呢? [英] Why use the BETWEEN operator when we can do without it?

问题描述

从下面两个查询中可以看出，我们发现它们都工作良好.然后，我感到困惑，为什么我们应该使用BETWEEN，因为我发现BETWEEN在不同数据库中的行为不同，如

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;

推荐答案

BETWEEN可以避免不必要的表达式重新评估:

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只是具有1,000,000记录的虚拟表.

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

当然可以使用子查询解决此问题，但是在MySQL中效率较低.

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

当然，BETWEEN更易读.在查询中使用它需要3次才能永远记住该语法.

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

在SQL Server和MySQL中，LIKE相对于不带前导'%'的常量也是一对>=和<的简写:

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)

但是，LIKE语法更容易理解.

However, LIKE syntax is more legible.

这篇关于当我们不能使用BETWEEN运算符时，为什么要使用它呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！