Comparison Operators (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
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 Transact-SQL comparison operators.
Greater than or equal to
Less than or equal to
Not equal to
Not equal to (not ISO standard)
Not less than (not ISO standard)
Not greater than (not ISO standard)
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 SQL Server 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 an equals (=) operator returns TRUE if both expressions are NULL. For example, NULL = NULL returns TRUE when SET ANSI_NULLS is OFF.
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:
USE AdventureWorks2012; GO DECLARE @MyProduct int; SET @MyProduct = 750; IF (@MyProduct <> 0) SELECT ProductID, Name, ProductNumber FROM Production.Product WHERE ProductID = @MyProduct;