Chapter 2: Queries: Selecting Data from a Table

Comparison operators

Transact-SQL uses these comparison operators:

**Table 2-3: 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 7, “Adding, Changing, 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)

View this book as PDF