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 ,
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 Query 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