Comparison Operators

Transact-SQL uses the following comparison operators:



Operator	Meaning

=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
< >	Not equal to
!>	Not greater than
!<	Not less than

The operators have the following syntax:

WHERE expression comparison_operator expression

An expression is a constant, column name, function, subquery, or any combination of them connected by arithmetic or bitwise operators.

In comparing char and varchar data, < means earlier in the installed sort order and > means later. Installation options for case and accent sensitivity affect the comparisons. (For more information about sort orders, see Microsoft SQL Server Setup.) In comparing dates, < means earlier and > means later.

Trailing blanks are ignored in comparisons. For example, the following are equivalent.

'Dirk'
'Dirk '

When you use comparison operators, be sure to put single quotation marks around all char, varchar, text, datetime, and smalldatetime data.

The use of NOT negates an expression. The following query finds all business and psychology books that do not have an advance over $5500:

SELECT title_id, type, advance
FROM titles
WHERE (type = 'business' OR type = 'psychology')
    AND NOT advance > $5500

These are the results:

title_id	type	advance
--------	----------	--------
BU1032	business	5,000.00
BU1111	business	5,000.00
BU7832	business	5,000.00
PS2091	psychology	2,275.00
PS3333	psychology	2,000.00
PS7777	psychology	4,000.00

(6 rows affected)

Comparison Operator Examples

The following examples use SELECT statements with comparison operators:

A.    SELECT *
    FROM titleauthor
    WHERE royaltyper < 50

B.    SELECT authors.au_lname, authors.au_fname
    FROM authors
    WHERE au_lname > 'McBadden'

C.    SELECT au_id, phone
    FROM authors
    WHERE phone <> '415 658-9932'

D.    SELECT title_id, newprice = price * $1.15
    FROM pubs..titles
    WHERE advance > $5000

Important The output for statements involving comparison operators depends on the sort order chosen during installation. For information about the effects of different sort orders, see Microsoft SQL Server Setup.