当我们不能使用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屋!