Skip to content

comparison-operators-transact-sql.md

Latest commit

 

History

History
65 lines (54 loc) · 3.5 KB

comparison-operators-transact-sql.md

File metadata and controls

65 lines (54 loc) · 3.5 KB
title Comparison Operators (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service sql-database
ms.reviewer
ms.technology t-sql
ms.topic language-reference
dev_langs
TSQL
helpviewer_keywords
expressions [SQL Server], testing
operators [Transact-SQL], comparison
testing expressions
Boolean data type
Boolean expressions
comparing expressions
comparison operators [SQL Server]
ms.assetid b0cc68ef-3029-484c-a917-0c15dcbc230d
author rothja
ms.author jroth

Comparison Operators (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2012-xxxx-xxxx-xxx-md]

Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. The following table lists the [!INCLUDEtsql] comparison operators.

Operator Meaning
= (Equals) Equal to
> (Greater Than) Greater than
< (Less Than) Less than
>= (Greater Than or Equal To) Greater than or equal to
<= (Less Than or Equal To) Less than or equal to
<> (Not Equal To) Not equal to
!= (Not Equal To) Not equal to (not ISO standard)
!< (Not Less Than) Not less than (not ISO standard)
!> (Not Greater Than) Not greater than (not ISO standard)

Boolean Data Type

The result of a comparison operator has the Boolean data type. This has three values: TRUE, FALSE, and UNKNOWN. Expressions that return a Boolean data type are known as Boolean expressions.

Unlike other [!INCLUDEssNoVersion] data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot be returned in a result set.

When SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN. When SET ANSI_NULLS is OFF, the same rules apply, except for the equals (=) and not equals (<>) operators. When SET ANSI_NULLS is OFF, these operators treat NULL as a known value, equivalent to any other NULL, and only return TRUE or FALSE (never UNKNOWN).

Expressions with Boolean data types are used in the WHERE clause to filter the rows that qualify for the search conditions and in control-of-flow language statements such as IF and WHILE, for example:

-- Uses AdventureWorks  
  
DECLARE @MyProduct int;  
SET @MyProduct = 750;  
IF (@MyProduct <> 0)  
   SELECT ProductID, Name, ProductNumber  
   FROM Production.Product  
   WHERE ProductID = @MyProduct;

See Also

Expressions (Transact-SQL)
Operators (Transact-SQL)