= (Equals) (Transact-SQL)

= (Equals) (Transact-SQL)

Community Content

In this section

Statistics Annotations (2)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (November 2009)

= (Equals) (Transact-SQL)

Compares the equality of two expressions (a comparison operator).

Topic link icon Transact-SQL Syntax Conventions

Syntax

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.

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.

Comparing NULL to a non-NULL value always results in FALSE.

Result Types

Boolean

Tags

: Add a tag

Community Content

Add new content

Annotations

Comparing NULL to non-NULL results in Unknown

Mike C_1 | Edit | Show History

The text states "Comparing NULL to a non-NULL value always results in FALSE." This is incorrect. Per ANSI/ISO SQL standards, comparing NULL to a non-NULL value should always result in neither TRUE nor FALSE, but the third logical value: UNKNOWN.

If comparing NULL to a non-NULL value always resulted in FALSE, then the following would be false:

(10 = NULL)

Logically the following would have to be the "opposite" of FALSE (that is, it would have to be TRUE):

NOT (10 = NULL)

You put these in IF statements to quickly determine that neither returns TRUE nor FALSE.

Tags

: Add a tag

Flag as ContentBug

Comparing strings with trailing spaces gives unexpected results

JohnFx | Edit | Show History

When using the = operator to compare varChar strings of different lengths, SQL Server will pad the shorter string with spaces before performing the comparison which can give unexpected results for the following statements.

For example, all three of the following will output 'equal' despite the varying number of trailing spaces in the operands:

if '' = '        '    print 'equal' else  print 'not equal'
if '   ' = '     '    print 'equal' else print 'not equal'
if 'abc' = 'abc  '    print 'equal' else print 'not equal'

For more information on see MS KB 316626 "INF: How SQL Server Compares Strings with Trailing Spaces"
http://support.microsoft.com/default.aspx/kb/316626

Reference

Other Resources

Help and Information