Comparison Operators (Database Engine)

Comparison operators are used with character, numeric, or date data and can be used in the WHERE or HAVING clause of a query. Comparison operators evaluate to a Boolean data type and return TRUE or FALSE, based on the outcome of the tested condition.

For example, to calculate a bonus for those employees who have been hired on or before March 15, 1998, a computation of whether the hire_date for an employee is less than or equal to March 15, 1998 provides the list of employees who should receive bonuses.

Valid comparison operators include the following:

  • > (greater than)
  • < (less than)
  • = (equals)
  • <= (less than or equal to)
  • >= (greater than or equal to)
  • != (not equal to)
  • <> (not equal to)
  • !< (not less than)
  • !> (not greater than)

Comparison operators can also be used in program logic to look for a condition. For example, if a country/region value is UK instead of Spain, different shipping rates may apply. In this case, a combination of a comparison operator, an expression (the column name), a literal ('UK'), and a control-of-flow programming keyword (IF) are used together to achieve this purpose.

Anyone with access to the actual data, for queries, can use comparison operators in additional queries. For those data-modification statements, you should use comparison operators only if you know you have the appropriate permissions and that the data will be changed by only a limited group of people. This will help maintain data integrity.

Queries also use string comparisons to compare the value in a local variable, cursor, or column with a constant. For example, all customer rows should be printed if the country/region is the UK. The following table shows string comparison examples between Unicode and non-Unicode data.ST1 is char and ST2 is nchar.

Comparison Description

ST1 = ST2

Equivalent to CONVERT(nchar, ST1) = ST2 or CAST(ST1 as nchar) = ST2.

ST1 = 'non-Unicode string'

Regular SQL-92 string comparison.

ST2 = 'non-Unicode string'

Equivalent to ST2 = CONVERT(nchar, 'non-Unicode string') or ST2 = CAST('non-Unicode string' AS nchar).

ST2 = N'Unicode string'

Unicode comparison.

CONVERT(nchar, ST1) = ST2 or CAST(ST1 AS nchar) = ST2

Unicode comparison.

ST1 = CONVERT(char, ST2) or ST1 = CAST(ST2 AS char)

Regular SQL-92 string comparison.

N'' (Unicode empty string in parentheses)

Empty string.

'' (non-Unicode empty string)

Either an empty string or a string that contains one blank character, depending on SQL-92 settings.

See Also

Other Resources

= (Equals) (Transact-SQL)
<> (Not Equal To) (Transact-SQL)
> (Greater Than) (Transact-SQL)
!< (Not Less Than) (Transact-SQL)
< (Less Than) (Transact-SQL)
!= (Not Equal To) (Transact-SQL)
>= (Greater Than or Equal To) (Transact-SQL)
!> (Not Greater Than) (Transact-SQL)
<= (Less Than or Equal To) (Transact-SQL)
WHERE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance