Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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)