=(等于)(Transact-SQL)= (Equals) (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

比较 SQL ServerSQL Server 中两个表达式的等价性(比较运算符)。Compares the equality of two expressions (a comparison operator) in SQL ServerSQL Server.

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

语法Syntax

expression = expression  

参数Arguments

expressionexpression
为任意有效的表达式Is any valid expression. 如果表达式的数据类型不同,则其中一个表达式的数据类型必须可以隐式转换为另一个表达式的数据类型。If the expressions are not of the same data type, the data type for one expression must be implicitly convertible to the data type of the other. 该转换基于数据类型优先级的规则进行。The conversion is based on the rules of data type precedence.

结果类型Result Types

BooleanBoolean

RemarksRemarks

使用 NULL 表达式进行比较时,结果取决于 ANSI_NULLS 设置:When you compare using a NULL expression, the result depends on the ANSI_NULLS setting:

  • 如果 ANSI_NULLS 设置为 ON,根据 ANSI 约定,即 NULL 是未知值且不能与任何其他值(包括其他 NULL)进行比较,任何与 NULL 进行比较的结果均为 UNKNOWN。If ANSI_NULLS is set to ON, the result of any comparison with NULL is UNKNOWN, following the ANSI convention that NULL is an unknown value and cannot be compared with any other value, including other NULLs.

  • 如果 ANSI_NULLS 设置为 OFF,将 NULL 与 NULL 进行比较的结果为 TRUE,将 NULL 与任何其他值进行比较的结果为 FALSE。If ANSI_NULLS is set to OFF, the result of comparing NULL to NULL is TRUE, and the result of comparing NULL to any other value is FALSE.

有关详细信息,请参阅 SET ANSI_NULLS (Transact-SQL)For more information, see SET ANSI_NULLS (Transact-SQL).

在大多数情况下(并非所有情况),导致 UNKNOWN 的布尔表达式的行为类似于 FALSE。A boolean expression resulting in UNKNOWN behaves similarly to FALSE in most, but not all cases. 请参阅 NULL 和 UNKNOWN (Transact-SQL)NOT (Transact-SQL) 了解详细信息。See NULL and UNKNOWN (Transact-SQL) and NOT (Transact-SQL) for more information.

示例Examples

A.A. 在简单查询中使用 =Using = in a simple query

下面的示例使用“等于”运算符返回 HumanResources.Department 表中的所有行,在该表中,GroupName 列中的值等于字词“Manufacturing”。The following example uses the Equals operator to return all rows in the HumanResources.Department table in which the value in the GroupName column is equal to the word 'Manufacturing'.

-- Uses AdventureWorks  
  
SELECT DepartmentID, Name  
FROM HumanResources.Department  
WHERE GroupName = 'Manufacturing';  
  

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

  
DepartmentID Name  
------------ --------------------------------------------------  
7            Production  
8            Production Control  
  
(2 row(s) affected)  
  

B.B. 比较 NULL 值和非 NULL 值Comparing NULL and non-NULL values

以下示例使用等于 (=) 和不等于 (<>) 比较运算符对表中的 NULL 值和非空值进行比较。The following example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL and nonnull values in a table. 该示例还表明,IS NULL 不受 SET ANSI_NULLS 设置的影响。The example also shows that IS NULL is not affected by the SET ANSI_NULLS setting.

-- Create table t1 and insert 3 rows.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 VALUES (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- SET ANSI_NULLS to ON and test.  
PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- SET ANSI_NULLS to OFF and test.  
PRINT 'Testing SET ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;  
  

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

Testing default setting  
a  
-----------  
NULL  
  
(1 row(s) affected)  
  
a  
-----------  
0  
1  
  
(2 row(s) affected)  
  
a  
-----------  
NULL  
  
(1 row(s) affected)  
  
Testing ANSI_NULLS ON  
a  
-----------  
  
(0 row(s) affected)  
  
a  
-----------  
  
(0 row(s) affected)  
  
a  
-----------  
NULL  
  
(1 row(s) affected)  
  
Testing SET ANSI_NULLS OFF  
a  
-----------  
NULL  
  
(1 row(s) affected)  
  
a  
-----------  
0  
1  
  
(2 row(s) affected)  
  
a  
-----------  
NULL  
  
(1 row(s) affected)  
  

另请参阅See Also

数据类型 (Transact-SQL) Data Types (Transact-SQL)
表达式 (Transact-SQL) Expressions (Transact-SQL)
运算符 (Transact-SQL)Operators (Transact-SQL)