COMPARISION OPERATORS

= (Equals)

Compares two expressions (a comparison operator).

When you compare nonnull expressions, the result is TRUE if both operands are equal; otherwise, the result is FALSE.

If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL.

If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.

Syntax

expression = expression

Arguments

Expression

Is any valid Microsoft® SQL Server™ expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

Examples:

ANSI_NULLS OFF

go

set ansi_nulls off

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 = @var2

print 'Null Equals to Null is true'

else

print 'Null Equals to Null is false'

      go

Here is the Output

----------------------------

      Null Equals to Null is true

ANSI_NULLS ON

go

set ansi_nulls on

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 = @var2

print 'Null Equals to Null is true'

else

print 'Null Equals to Null is false'

go

Here is the Output

----------------------------

Null Equals to Null is false

> (Greater Than)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand has a higher value than the right operand; otherwise, the result is FALSE.

The result is FALSE. If either or both operands are NULL doesn’t depend on the ANSI_NULLS setting.

Syntax

expression > expression

Arguments

expression

Is any valid Microsoft® SQL Server™ expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

go

set ansi_nulls off

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 > @var2

print 'Null greater than Null is true'

else

print 'Null greater than Null is false'

go

Here is the output:

--------------------------------

Null greater than Null is false

go

set ansi_nulls on

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 > @var2

print 'Null greater than Null is true'

else

print 'Null greater than Null is false'

go

Here is the output:

--------------------------------

Null greater than Null is false

< (Less Than)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand has a lower value than the right operand; otherwise, the result is FALSE.

The result is FALSE. If either or both operands are NULL doesn’t depend on the ANSI_NULLS setting.

Syntax

expression < expression

Arguments

expression

Is any valid Microsoft® SQL Server™ expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

go

set ansi_nulls off

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 < @var2

print 'Null Less than Null is true'

else

print 'Null Less than Null is false'

go

Here is the output:

---------------------

Null Less than Null is false

go

set ansi_nulls on

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 < @var2

print 'Null Less than Null is true'

else

print 'Null Less than Null is false'

go

Here is the output:

---------------------

Null Less than Null is false

>= (Greater Than or Equal To)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand has a higher or equal value than the right operand; otherwise, the result is FALSE.

The result is FALSE. If either or both operands are NULL doesn’t depend on the ANSI_NULLS setting.

Syntax

expression > = expression

Arguments

expression

Is any valid Microsoft® SQL Server™ expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

go

set ansi_nulls off

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 >= @var2

print 'Null grater than or equals to Null is true'

else

print 'Null grater than or equals to Null is false'

go

Here is the output:

-------------------------------------------

Null grater than or equals to Null is false

go

set ansi_nulls on

go

Declare

@var1 int =NULL,

@var2 int =NULL

if @var1 >= @var2

print 'Null grater than or equals to Null is true'

else

print 'Null grater than or equals to Null is false'

go

Here is the output:

-------------------------------------------

Null grater than or equals to Null is false

<= (Less Than or Equal To)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand has a value lower than or equal to the right operand; otherwise, the result is FALSE.

Unlike the = (equality) comparison operator, the result of the >= comparison of two NULL values does not depend on the ANSI_NULLS setting.

Syntax

expression <= expression

Arguments

expression

Is any valid expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

<> (Not Equal To) Or

!= (This operator is not ISO standard)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. If either or both operands are NULL.

Syntax

 expression < > expression

Arguments

expression

Is any valid expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

!< (Not Less Than) (This operator is not ISO standard)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand does not have a value lower than the right operand; otherwise, the result is FALSE. If either or both operands are NULL.

Syntax

 expression !< expression

Arguments

expression

Is any valid expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean

!> (Not Greater Than) (This operator is not ISO standard)

Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if the left operand does not have a greater value than the right operand; otherwise, the result is FALSE.

Unlike the = (equality) comparison operator, the result of the !> comparison of two NULL values does not depend on the ANSI_NULLS setting.

Syntax

 expression !> expression

Arguments

expression

Is any valid expression. Both expressions must have implicitly convertible data types. The conversion depends on the rules of data type precedence.

Result Types

Boolean