equals-transact-sql.md

title	= (Equals) (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	12/06/2016
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	= = (Equals) Equals =_TSQL
dev_langs	TSQL
helpviewer_keywords	equals operator (=) = (equals operator)
ms.assetid	18885245-5f55-4831-8f0b-7f2a3e82e246
caps.latest.revision	40
author	BYHAM
ms.author	rickbyh
manager	jhubbard

= (Equals) (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Compares the equality of two expressions (a comparison operator) in [!INCLUDEssNoVersion_md].

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
expression = expression  

Arguments

expression
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

Boolean

Remarks

When you compare two NULL expressions, the result depends on the ANSI_NULLS setting:

• If ANSI_NULLS is set to ON, the result is NULL, following the ANSI convention that a NULL (or unknown) value is not equal to another NULL or unknown value.

• If ANSI_NULLS is set to OFF, the result of NULL compared to NULL is TRUE.

For more information, see SET ANSI_NULLS (Transact-SQL).

Any sort of comparison of a NULL value (an unknown) to a non-NULL value always results in FALSE.

Examples

A. Using = in a simple query

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';  
  

[!INCLUDEssResult]

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

B. Comparing NULL and non-NULL values

The following example uses the Equals (=) and Not Equal To (<>) comparison operators to make comparisons with NULL and nonnull values in a table. 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;  
  

[!INCLUDEssResult]

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

Data Types (Transact-SQL)
Expressions (Transact-SQL)
Operators (Transact-SQL)