Transact-SQL uses these comparison operators:
Operator |
Meaning |
|---|---|
= |
Equal to |
> |
Greater than |
< |
Less than |
>= |
Greater than or equal to |
<= |
Less than or equal to |
<> |
Not equal to |
!= |
Not equal to (Transact-SQL extension) |
!> |
Not greater than (Transact-SQL extension) |
!< |
Not less than (Transact-SQL extension) |
The operators are used in this syntax:
where expression comparison_operator expression
An expression is a constant, column name, function, subquery, case expression, or any combination of these, connected by arithmetic or bitwise operators. In comparing character data, < means earlier in the sort order and > means later in the sort order. Use sp_helpsort to display the sort order for your Adaptive Server.
Trailing blanks are ignored for the purposes of comparison. For example, “Dirk” is the same as “Dirk ”. In comparing dates, < means earlier than, and > means later than. Place apostrophes or quotation marks around all char, nchar, unichar, unitext, varchar, nvarchar, univarchar, text, and date/time data. For more information on entering date and time data, see Chapter 8, “Adding, Changing, Transferring, and Deleting Data.”
Here are some sample select statements that use comparison operators:
select * from titleauthor where royaltyper < 50
select authors.au_lname, authors.au_fname from authors where au_lname > "McBadden"
select au_id, phone from authors where phone != "415 658-9932"
select title_id, newprice = price * $1.15 from pubs2..titles where advance > 5000
not negates an expression. Either of the following two queries finds all business and psychology books that have advances of less than $5500. Note the difference in position between the negative logical operator (not) and the negative comparison operator (!>).
select title_id, type, advance from titles where (type = "business" or type = "psychology") and not advance >5500
select title_id, type, advance from titles where (type = "business" or type = "psychology") and advance !>5500
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)
