Use a WHERE clause to find a subset of values

Like Exclude rows, the following query assumes the use of an ANSI-compliant database. The owner qualifier is in quotation marks to preserve the case sensitivity of the literal string.

Figure 1. Query

SELECT lname, city, state, phone
   FROM 'Aleta'.customer
   WHERE state = 'AZ' OR state = 'NJ'
   ORDER BY lname;

SELECT lname, city, state, phone
   FROM 'Aleta'.customer
   WHERE state IN ('AZ', 'NJ')
   ORDER BY lname;

Each statement in the query retrieves rows that include the subset of AZ or NJ in the state column of the Aleta.customer table.

Figure 2. Query result

lname      city            state phone

Jewell     Phoenix         AZ    602-265-8754
Lessor     Phoenix         AZ    602-533-1817
O'Brian    Princeton       NJ    609-342-0054
Shorter    Cherry Hill     NJ    609-663-6079

You cannot test TEXT or BYTE columns with the IN keyword.

Also, when you use HCL Informix®, you cannot test BLOB or CLOB columns with the IN keyword.

In the example of a query on an ANSI-compliant database, no quotation marks exist around the table owner name. Whereas the two statements in Figure 1 searched the Aleta.customer table, the following query searches the table ALETA.customer, which is a different table, because of the way ANSI-compliant databases look at owner names.

Figure 3. Query

SELECT lname, city, state, phone
   FROM Aleta.customer
   WHERE state NOT IN ('AZ', 'NJ')
   ORDER BY state;

The previous query adds the keywords NOT IN, so the subset changes to exclude the subsets AZ and NJ in the state column. The following figure shows the results in order of the state column.

Figure 4. Query result

lname        city            state phone

Pauli        Sunnyvale       CA    408-789-8075
Sadler       San Francisco   CA    415-822-1289
Currie       Palo Alto       CA    415-328-4543
Higgins      Redwood City    CA    415-368-1100
Vector       Los Altos       CA    415-776-3249
Watson       Mountain View   CA    415-389-8789
Ream         Palo Alto       CA    415-356-9876
Quinn        Redwood City    CA    415-544-8729
Miller       Sunnyvale       CA    408-723-8789
Jaeger       Redwood City    CA    415-743-3611
Keyes        Sunnyvale       CA    408-277-7245
Lawson       Los Altos       CA    415-887-7235
Beatty       Menlo Park      CA    415-356-9982
Albertson    Redwood City    CA    415-886-6677
Grant        Menlo Park      CA    415-356-1123
Parmelee     Mountain View   CA    415-534-8822
Sipes        Redwood City    CA    415-245-4578
Baxter       Oakland         CA    415-655-0011
Neelie       Denver          CO    303-936-7731
Wallack      Wilmington      DE    302-366-7511
Hanlon       Jacksonville    FL    904-823-4239
Henry        Brighton        MA    617-232-4159
Satifer      Blue Island     NY    312-944-5691
Putnum       Bartlesville    OK    918-355-2074