BETWEEN (Transact-SQL)BETWEEN (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

指定测试范围。Specifies a range to test.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

test_expression [ NOT ] BETWEEN begin_expression AND end_expression  

参数Arguments

test_expression test_expression
要在由 begin_expression 和 end_expression 定义的范围内测试的表达式Is the expression to test for in the range defined by begin_expressionand end_expression. test_expression 的数据类型必须与 begin_expression 和 end_expression 的数据类型相同 。test_expression must be the same data type as both begin_expression and end_expression.

NOTNOT
指定谓词的结果被取反。Specifies that the result of the predicate be negated.

begin_expression begin_expression
为任意有效的表达式。Is any valid expression. begin_expression 的数据类型必须与 test_expression 和 end_expression 的数据类型相同 。begin_expression must be the same data type as both test_expression and end_expression.

end_expression end_expression
为任意有效的表达式。Is any valid expression. end_expression 的数据类型必须与 test_expression 和 begin_expression 的数据类型相同 。end_expression must be the same data type as both test_expressionand begin_expression.

AND
充当一个占位符,用于指示 test_expression 应该在 begin_expression 和 end_expression 指定的范围内 。Acts as a placeholder that indicates test_expression should be within the range indicated by begin_expression and end_expression.

结果类型Result Types

BooleanBoolean

结果值Result Value

如果 test_expression 的值大于或等于 begin_expression 的值,并且小于或等于 end_expression 的值,则 BETWEEN 返回 TRUE 。BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

如果 test_expression 的值小于 begin_expression 的值,或大于 end_expression 的值,则 NOT BETWEEN 返回 TRUE 。NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.

RemarksRemarks

若要指定排他范围,请使用大于 (>) 和小于 (<) 运算符。To specify an exclusive range, use the greater than (>) and less than operators (<). 如果任何 BETWEEN 或 NOT BETWEEN 谓词的输入为 NULL,则结果为 UNKNOWN。If any input to the BETWEEN or NOT BETWEEN predicate is NULL, the result is UNKNOWN.

示例Examples

A.A. 使用 BETWEENUsing BETWEEN

以下示例返回有关数据库中数据库角色的信息。The following example returns information about the database roles in a database. 第一个查询返回所有角色。The first query returns all the roles. 第二个示例使用 BETWEEN 子句将角色限制为指定的 database_id 值。The second example uses the BETWEEN clause to limit the roles to the specified database_id values.

SELECT principal_id, name 
FROM sys.database_principals
WHERE type = 'R';

SELECT principal_id, name 
FROM sys.database_principals
WHERE type = 'R'
AND principal_id BETWEEN 16385 AND 16390;
GO  

下面是结果集:Here is the result set.

principal_id    name
------------  ---- 
0               public
16384           db_owner
16385           db_accessadmin
16386           db_securityadmin
16387           db_ddladmin
16389           db_backupoperator
16390           db_datareader
16391           db_datawriter
16392           db_denydatareader
16393           db_denydatawriter
principal_id    name
------------  ---- 
16385           db_accessadmin
16386           db_securityadmin
16387           db_ddladmin
16389           db_backupoperator
16390           db_datareader

B.B. 使用 > 和 <,而不使用 BETWEENUsing > and < instead of BETWEEN

下面的示例使用大于 (>) 和小于 (<) 运算符,因为这些运算符是非包含的,所以该示例返回九行,而不是像上一个示例那样返回十行。The following example uses greater than (>) and less than (<) operators and, because these operators are not inclusive, returns nine rows instead of ten that were returned in the previous example.

-- Uses AdventureWorks  
  
SELECT e.FirstName, e.LastName, ep.Rate  
FROM HumanResources.vEmployee e   
JOIN HumanResources.EmployeePayHistory ep   
    ON e.BusinessEntityID = ep.BusinessEntityID  
WHERE ep.Rate > 27 AND ep.Rate < 30  
ORDER BY ep.Rate;  
GO  

下面是结果集:Here is the result set.

FirstName   LastName             Rate  
---------   -------------------  ---------  
Paula       Barreto de Mattos    27.1394  
Janaina     Bueno                27.4038  
Dan         Bacon                27.4038  
Ramesh      Meyyappan            27.4038  
Karen       Berg                 27.4038  
David       Bradley              28.7500  
Hazem       Abolrous             28.8462  
Ovidiu      Cracium              28.8462  
Rob         Walters              29.8462  

C.C. 使用 NOT BETWEENUsing NOT BETWEEN

下面的示例查找处于指定范围 2730 以外的所有行。The following example finds all rows outside a specified range of 27 through 30.

-- Uses AdventureWorks  
  
SELECT e.FirstName, e.LastName, ep.Rate  
FROM HumanResources.vEmployee e   
JOIN HumanResources.EmployeePayHistory ep   
    ON e.BusinessEntityID = ep.BusinessEntityID  
WHERE ep.Rate NOT BETWEEN 27 AND 30  
ORDER BY ep.Rate;  
GO  

D.D. 使用带有日期时间值的 BETWEENUsing BETWEEN with datetime values

以下示例检索 datetime 值介于 '20011212''20020105'(含)之间的行 。The following example retrieves rows in which datetime values are between '20011212' and '20020105', inclusive.

-- Uses AdventureWorks  
  
SELECT BusinessEntityID, RateChangeDate  
FROM HumanResources.EmployeePayHistory  
WHERE RateChangeDate BETWEEN '20011212' AND '20020105';  

下面是结果集:Here is the result set.

BusinessEntityID RateChangeDate  
----------- -----------------------  
3           2001-12-12 00:00:00.000  
4           2002-01-05 00:00:00.000  

由于指定查询中的日期值和 RateChangeDate 列中存储的 datetime 值时未指定日期的时间部分,因此该查询将检索预期行 。The query retrieves the expected rows because the date values in the query and the datetime values stored in the RateChangeDate column have been specified without the time part of the date. 未指定时间部分时,将默认使用 12:00 A.M。When the time part is unspecified, it defaults to 12:00 A.M. 请注意,若某行的时间部分晚于 2002-01-05 12:00 A.M.,Note that a row that contains a time part that is after 12:00 A.M. 则由于它处于范围之外,因此此查询不返回该行。on 2002-01-05 would not be returned by this query because it falls outside the range.

另请参阅See Also

>(大于)(Transact-SQL) > (Greater Than) (Transact-SQL)
<(小于)(Transact-SQL) < (Less Than) (Transact-SQL)
表达式 (Transact-SQL) Expressions (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
运算符 (Transact-SQL) Operators (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)