= (Equals) (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Compares the equality of two expressions (a comparison operator) in SQL Server.
Transact-SQL syntax conventions
Syntax
expression = expression
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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 using a NULL expression, the result depends on the ANSI_NULLS
setting:
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.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.
For more information, see SET ANSI_NULLS (Transact-SQL).
A boolean expression resulting in UNKNOWN behaves similarly to FALSE in most, but not all cases. See NULL and UNKNOWN (Transact-SQL) and NOT (Transact-SQL) for more information.
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';
Here is the result set.
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;
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
Data Types (Transact-SQL)
Expressions (Transact-SQL)
Operators (Transact-SQL)