Exclude rows

Use the relational operators != or <> to exclude rows in a WHERE clause.

The following query assumes that you are selecting from an ANSI-compliant database; the statements specify the owner or login name of the creator of the customer table. This qualifier is not required when the creator of the table is the current user, or when the database is not ANSI compliant. However, you can include the qualifier in either case. For a detailed discussion of owner naming, see the HCL Informix® Guide to SQL: Syntax.

Figure 1. Query

SELECT customer_num, company, city, state
   FROM odin.customer
   WHERE state != 'CA';

SELECT customer_num, company, city, state
   FROM odin.customer
   WHERE state <> 'CA';

Both statements in the query exclude values by specifying that, in the customer table that the user odin owns, the value in the state column should not be equal to CA, as the result shows.

Figure 2. Query result

customer_num  company              city            state 

        119  The Triathletes Club Cherry Hill     NJ
        120  Century Pro Shop     Phoenix         AZ
        121  City Sports          Wilmington      DE
        122  The Sporting Life    Princeton       NJ
        123  Bay Sports           Jacksonville    FL
        124  Putnum's Putters     Bartlesville    OK
        125  Total Fitness Sports Brighton        MA
        126  Neelie's Discount Sp Denver          CO
        127  Big Blue Bike Shop   Blue Island     NY
        128  Phoenix College      Phoenix         AZ