ASA SQL User's Guide
  Queries: Selecting Data from a Table
    The WHERE clause: specifying rows

Using comparison operators in the WHERE clause

You can use comparison operators in the WHERE clause. The operators follow the syntax:

WHERE expression comparison-operator expression

For more information about comparison operators, see Comparison operators. For a description of what an expression can consist of, see Expressions.

Notes on comparisons 

• Sort orders    In comparing character data, < means earlier in the sort order and > means later in the sort order. The sort order is determined by the collation chosen when the database is created. You can find out the collation by running the dbinfo command-line utility against the database:

  dbinfo -c "uid=DBA;pwd=SQL"

  You can also find the collation from Sybase Central. It is on the Extended Information tab of the database property sheet.

• Trailing blanks    When you create a database, you indicate whether trailing blanks are to be ignored or not for the purposes of comparison.

  By default, databases are created with trailing blanks not ignored. For example, 'Dirk' is not the same as 'Dirk '. You can create databases with blank padding, so that trailing blanks are ignored. Trailing blanks are ignored by default in Adaptive Server Enterprise databases.

• Comparing dates    In comparing dates, < means earlier and > means later.

• Case sensitivity    When you create a database, you indicate whether string comparisons are case sensitive or not.

  By default, databases are created case insensitive. For example, 'Dirk' is the same as 'DIRK'. You can create databases to be case sensitive, which is the default behavior for Adaptive Server Enterprise databases.

Here are some SELECT statements using comparison operators:

SELECT *
FROM product
WHERE quantity < 20
SELECT E.emp_lname, E.emp_fname
FROM employee E
WHERE emp_lname > 'McBadden'
SELECT id, phone
FROM contact
WHERE state  != 'CA'

The NOT operator 

The NOT operator negates an expression. Either of the following two queries will find all Tee shirts and baseball caps that cost $10 or less. However, note the difference in position between the negative logical operator (NOT) and the negative comparison operator (!>).

SELECT id, name, quantity
FROM product
WHERE (name = 'Tee Shirt' OR name = 'BaseBall Cap')
AND NOT unit_price > 10
SELECT id, name, quantity
FROM product
WHERE (name = 'Tee Shirt' OR name = 'BaseBall Cap')
AND unit_price !> 10