Comparison Operators
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; they 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 are:
- > (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 check for a condition. For example, if the country column is UK rather than 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, it is recommended that you use comparison operators only if you know you have the appropriate permissions and that data will be changed by only a limited group of people (to 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 is the UK. The 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 containing one blank character (depending on SQL-92 settings). |