The WHERE clause of a SELECT statement specifies the rows that you want to see. A comparison condition employs specific keywords and operators to define the search criteria.
For example, you might use one of the keywords BETWEEN, IN, LIKE, or MATCHES to test for equality, or the keywords IS NULL to test for null values. You can combine the keyword NOT with any of these keywords to specify the opposite condition.
The following table lists the relational operators that you can use in a WHERE clause in place of a keyword to test for equality.
For CHAR expressions, greater than means after in ASCII collating order, where lowercase letters are after uppercase letters, and both are after numerals. See the ASCII Character Set chart in the IBM Informix: Guide to SQL Syntax. For DATE and DATETIME expressions, greater than means later in time, and for INTERVAL expressions, it means of longer duration.
You cannot use TEXT or BYTE columns to create a comparison condition, except when you use the IS NULL or IS NOT NULL keywords to test for NULL values.
You cannot specify BLOB or CLOB columns to create a comparison condition on Dynamic Server, except when you use the IS NULL or IS NOT NULL keywords to test for NULL values.
You can use the preceding keywords and operators in a WHERE clause to create comparison-condition queries that perform the following actions:
To perform variable text searches using the following criteria, use the preceding keywords and operators in a WHERE clause to create comparison-condition queries:
The following section contains examples that illustrate these types of queries.
Use the equal sign (=) relational operator to include rows in a WHERE clause, as Figure 46 shows.
SELECT customer_num, call_code, call_dtime, res_dtime FROM cust_calls WHERE user_id = 'maryj'
Figure 46 returns the set of rows that Figure 47 shows.
customer_num call_code call_dtime res_dtime
106 D 1998-06-12 08:20 1998-06-12 08:25
121 O 1998-07-10 14:05 1998-07-10 14:06
127 I 1998-07-31 14:30Use the relational operators != or <> to exclude rows in a WHERE clause.
Figure 48 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 IBM Informix: Guide to SQL Syntax.
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 Figure 48 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 Figure 49 shows.
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 AZFigure 50 shows two ways to specify a range of rows in a WHERE clause.
SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog WHERE catalog_num BETWEEN 10005 AND 10008 SELECT catalog_num, stock_num, manu_code, cat_advert FROM catalog WHERE catalog_num >= 10005 AND catalog_num <= 10008
Each statement in Figure 50 specifies a range for catalog_num from 10005 through 10008, inclusive. The first statement uses keywords, and the second statement uses relational operators to retrieve the rows, as Figure 51 shows.
catalog_num 10005
stock_num 3
manu_code HSK
cat_advert High-Technology Design Expands the Sweet Spot
catalog_num 10006
stock_num 3
manu_code SHM
cat_advert Durable Aluminum for High School and Collegiate Athletes
catalog_num 10007
stock_num 4
manu_code HSK
cat_advert Quality Pigskin with Joe Namath Signature
catalog_num 10008
stock_num 4
manu_code HRO
cat_advert Highest Quality Football for High School
and Collegiate CompetitionsAlthough the catalog table includes a column with the BYTE data type, that column is not included in this SELECT statement because the output would show only the words <BYTE value> by the column name. You can write an SQL API application to display TEXT and BYTE values.
Figure 52 uses the keywords NOT BETWEEN to exclude rows that have the character range 94000 through 94999 in the zipcode column, as Figure 53 shows.
SELECT fname, lname, city, state FROM customer WHERE zipcode NOT BETWEEN '94000' AND '94999' ORDER BY state
fname lname city state Frank Lessor Phoenix AZ Fred Jewell Phoenix AZ Eileen Neelie Denver CO Jason Wallack Wilmington DE Marvin Hanlon Jacksonville FL James Henry Brighton MA Bob Shorter Cherry Hill NJ Cathy O'Brian Princeton NJ Kim Satifer Blue Island NY Chris Putnum Bartlesville OK
Like Excluding Rows, Figure 54 assumes the use of an ANSI-compliant database. The owner qualifier is in quotation marks to preserve the case sensitivity of the literal string.
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 lnameEach statement in Figure 54 retrieves rows that include the subset of AZ or NJ in the state column of the Aleta.customer table, as Figure 55 shows.
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 Dynamic Server, you cannot test BLOB or CLOB columns with the IN keyword.
In Figure 56, an example of a query on an ANSI-compliant database, no quotation marks exist around the table owner name. Whereas the two statements in Figure 54 searched the Aleta.customer table, Figure 56 searches the table ALETA.customer, which is a different table, because of the way ANSI-compliant databases look at owner names.
SELECT lname, city, state, phone
FROM Aleta.customer
WHERE state NOT IN ('AZ', 'NJ')
ORDER BY state Figure 56 adds the keywords NOT IN, so the subset changes to exclude the subsets AZ and NJ in the state column. Figure 57 shows the results in order of the state column.
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
Use the IS NULL or IS NOT NULL option to check for NULL values. A NULL value represents either the absence of data or an unknown value. A NULL value is not the same as a zero or a blank.
Figure 58 returns all rows that have a null paid_date, as Figure 59 shows.
SELECT order_num, customer_num, po_num, ship_date FROM orders WHERE paid_date IS NULL ORDER BY customer_num
order_num customer_num po_num ship_date
1004 106 8006 05/30/1998
1006 112 Q13557
1007 117 278693 06/05/1998
1012 117 278701 06/29/1998
1016 119 PC6782 07/12/1998
1017 120 DM354331 07/13/1998To connect two or more comparison conditions, or Boolean expressions, use the logical operators AND, OR, and NOT. A Boolean expression evaluates as true or false or, if NULL values are involved, as unknown.
In Figure 60, the operator AND combines two comparison expressions in the WHERE clause.
SELECT order_num, customer_num, po_num, ship_date
FROM orders
WHERE paid_date IS NULL
AND ship_date IS NOT NULL
ORDER BY customer_numThe query returns all rows that have NULL paid_date or a NOT NULL ship_date, as Figure 61 shows.
order_num customer_num po_num ship_date
1004 106 8006 05/30/1998
1007 117 278693 06/05/1998
1012 117 278701 06/29/1998
1017 120 DM354331 07/13/1998The following examples include a WHERE clause that searches for exact-text comparisons by using the keyword LIKE or MATCHES or the equal sign (=) relational operator. Unlike earlier examples, these examples illustrate how to query a table that is not in the current database. You can access a table that is not in the current database only if the database that contains the table has the same ANSI compliance status as the current database. If the current database is an ANSI-compliant database, the table you want to access must also reside in an ANSI-compliant database. If the current database is not an ANSI-compliant database, the table you want to access must also reside in a database that is not an ANSI-compliant database.
Although the database used previously in this chapter is the demonstration database, the FROM clause in the following examples specifies the manatee table, created by the owner bubba, which resides in an ANSI-compliant database named syzygy. For more information on how to access tables that are not in the current database, see the IBM Informix: Guide to SQL Syntax.
Each statement in Figure 62 retrieves all the rows that have the single word helmet in the description column, as Figure 63 shows.
SELECT stock_no, mfg_code, description, unit_price FROM syzygy:bubba.manatee WHERE description = 'helmet' ORDER BY mfg_code SELECT stock_no, mfg_code, description, unit_price FROM syzygy:bubba.manatee WHERE description LIKE 'helmet' ORDER BY mfg_code SELECT stock_no, mfg_code, description, unit_price FROM syzygy:bubba.manatee WHERE description MATCHES 'helmet' ORDER BY mfg_code
The results might look like Figure 63.
stock_no mfg_code description unit_price
991 ABC helmet $222.00
991 BKE helmet $269.00
991 HSK helmet $311.00
991 PRC helmet $234.00
991 SPR helmet $245.00You can use the keywords LIKE and MATCHES for variable-text queries that are based on substring searches of fields. Include the keyword NOT to indicate the opposite condition. The keyword LIKE is the ANSI standard, whereas MATCHES is an Informix extension.
Variable-text search strings can include the wildcards listed with LIKE or MATCHES in the following table.
| Keyword | Symbol | Meaning | |
|---|---|---|---|
| LIKE | % | Evaluates to zero or more characters | |
| LIKE | _ | Evaluates to a single character | |
| LIKE | \ | Escapes special significance of next character | |
| MATCHES | * | Evaluates to zero or more characters | |
| MATCHES | ? | Evaluates to a single character (except null) | |
| MATCHES | [ ] | Evaluates to a single character or range of values | |
| MATCHES | \ | Escapes special significance of next character | |
You cannot test TEXT or BYTE columns with the LIKE or MATCHES keywords.
Also, when you use Dynamic Server, you cannot test BLOB or CLOB columns with the LIKE or MATCHES keywords.
The statements in Figure 64 illustrate the use of a single-character wildcard in a WHERE clause. Further, they demonstrate a query on a table that is not in the current database. The stock table is in the database sloth. Besides being outside the current demonstration database, sloth is on a separate database server called meerkat.
For more information, see Accessing and Modifying Data in an External Database and the IBM Informix: Guide to SQL Syntax.
SELECT stock_num, manu_code, description, unit_price
FROM sloth@meerkat:stock
WHERE manu_code LIKE '_R_'
AND unit_price >= 100
ORDER BY description, unit_price
SELECT stock_num, manu_code, description, unit_price
FROM sloth@meerkat:stock
WHERE manu_code MATCHES '?R?'
AND unit_price >= 100
ORDER BY description, unit_priceEach statement in Figure 64 retrieves only those rows for which the middle letter of the manu_code is R, as Figure 65 shows. The comparison '_R_' (for LIKE) or '?R?' (for MATCHES) specifies, from left to right, the following items:
stock_num manu_code description unit_price
205 HRO 3 golf balls $312.00
2 HRO baseball $126.00
1 HRO baseball gloves $250.00
7 HRO basketball $600.00
102 PRC bicycle brakes $480.00
114 PRC bicycle gloves $120.00
4 HRO football $480.00
110 PRC helmet $236.00
110 HRO helmet $260.00
307 PRC infant jogger $250.00
306 PRC tandem adapter $160.00
308 PRC twin jogger $280.00
304 HRO watch $280.00Figure 66 selects only those rows where the manu_code begins with A through H and returns the rows that Figure 67 shows. The test '[A-H]' specifies any single letter from A through H, inclusive. No equivalent wildcard symbol exists for the LIKE keyword.
SELECT stock_num, manu_code, description, unit_price FROM stock WHERE manu_code MATCHES '[A-H]*' ORDER BY description, manu_code
stock_num manu_code description unit_price
205 ANZ 3 golf balls $312.00
205 HRO 3 golf balls $312.00
2 HRO baseball $126.00
3 HSK baseball bat $240.00
1 HRO baseball gloves $250.00
1 HSK baseball gloves $800.00
7 HRO basketball $600.00
·
·
·
313 ANZ swim cap $60.00
6 ANZ tennis ball $48.00
5 ANZ tennis racquet $19.80
8 ANZ volleyball $840.00
9 ANZ volleyball net $20.00
304 ANZ watch $170.00The statements in Figure 68 use a wildcard at the end of a string to retrieve all the rows where the description begins with the characters bicycle.
SELECT stock_num, manu_code, description, unit_price FROM stock WHERE description LIKE 'bicycle%' ORDER BY description, manu_code SELECT stock_num, manu_code, description, unit_price FROM stock WHERE description MATCHES 'bicycle*' ORDER BY description, manu_code
Either statement returns the rows that Figure 69 shows.
stock_num manu_code description unit_price
102 PRC bicycle brakes $480.00
102 SHM bicycle brakes $220.00
114 PRC bicycle gloves $120.00
107 PRC bicycle saddle $70.00
106 PRC bicycle stem $23.00
101 PRC bicycle tires $88.00
101 SHM bicycle tires $68.00
105 PRC bicycle wheels $53.00
105 SHM bicycle wheels $80.00The comparison 'bicycle%' or 'bicycle*' specifies the characters bicycle followed by any sequence of zero or more characters. It matches bicycle stem with stem matched by the wildcard. It matches to the characters bicycle alone, if a row exists with that description.
Figure 70 narrows the search by adding another comparison condition that excludes a manu_code of PRC.
SELECT stock_num, manu_code, description, unit_price
FROM stock
WHERE description LIKE 'bicycle%'
AND manu_code NOT LIKE 'PRC'
ORDER BY description, manu_codeThe statement retrieves only the rows that Figure 71 shows.
stock_num manu_code description unit_price
102 SHM bicycle brakes $220.00
101 SHM bicycle tires $68.00
105 SHM bicycle wheels $80.00When you select from a large table and use an initial wildcard in the comparison string (such as '%cycle'), the query often takes longer to execute. Because indexes cannot be used, every row is searched.
By default, Informix database servers use the U.S. English language environment, called a locale, for database data. This default locale uses the ISO 8859-1 code set. The U.S. English locale specifies that MATCHES will use code-set order.
If your database uses a non-default locale, a MATCHES clause that specifies a range uses the collation order of that locale for character data types (including CHAR, NCHAR, VARCHAR, NVARCHAR, and LVARCHAR). This feature of MATCHES ranges is an exception to the general rule that only NCHAR and NVARCHAR columns can use locale-specific collation. If the locale does not specify any special collation order, however, then MATCHES uses the code-set order.
You can use the SET COLLATION statement to specify a database locale for your session that is different from the DB_LOCALE setting. See the IBM Informix: Guide to SQL Syntax for a description of SET COLLATION.
SELECT numéro,nom,prénom FROM abonnés WHERE nom MATCHES '[E-P]*' ORDER BY nom
In Figure 73, the rows for Étaix, Ötker, and Øverst are not selected and listed because, with ISO 8859-1 code-set order, the accented first letter of each name is not in the E through P MATCHES range for the nom column.
numéro nom prénom 13607 Hammer Gerhard 13602 Hämmer Greta 13604 LaForêt Jean-Noël 13610 LeMaître Héloïse 13613 Llanero Gloria Dolores 13603 Montaña José Antonio 13611 Oatfield Emily
For more information on non-English data and locales, see the IBM Informix: GLS User's Guide.
Figure 74 uses the keyword ESCAPE with LIKE or MATCHES so you can protect a special character from misinterpretation as a wildcard symbol.
SELECT * FROM cust_calls WHERE res_descr LIKE '%!%%' ESCAPE '!'
The ESCAPE keyword designates an escape character (! in this example) that protects the next character so that it is interpreted as data and not as a wildcard. In the example, the escape character causes the middle percent sign (%) to be treated as data. By using the ESCAPE keyword, you can search for occurrences of a percent sign (%) in the res_descr column by using the LIKE wildcard percent sign (%). The query retrieves the row that Figure 75 shows.
customer_num 116
call_dtime 1997-12-21 11:24
user_id mannyn
call_code I
call_descr Second complaint from this customer!
Received two cases righthanded outfielder
glove (1 HRO) instead of one case lefties.
res_dtime 1997-12-27 08:19
res_descr Memo to shipping (Ava Brown) to send case
of lefthanded gloves, pick up wrong case;
memo to billing requesting 5% discount to
placate customer due to second offense
and lateness of resolution because of
holiday.You can use subscripting in the WHERE clause of a SELECT statement to specify a range of characters or numbers in a column, as Figure 76 shows.
SELECT catalog_num, stock_num, manu_code, cat_advert,
cat_descr
FROM catalog
WHERE cat_advert[1,4] = 'High' The subscript [1,4] causes Figure 76 to retrieve all rows in which the first four letters of the cat_advert column are High, as Figure 77 shows.
catalog_num 10004
stock_num 2
manu_code HRO
cat_advert Highest Quality Ball Available, from Hand-Sti
tching to the Robinson Signature
cat_descr
Jackie Robinson signature ball. Highest professional quality,
used by National League.
catalog_num 10005
stock_num 3
manu_code HSK
cat_advert High-Technology Design Expands the Sweet Spot
cat_descr
Pro-style wood. Available in sizes: 31, 32, 33, 34, 35.
·
·
·
catalog_num 10045
stock_num 204
manu_code KAR
cat_advert High-Quality Beginning Set of Irons. Appropriate
for High School Competitions
cat_descr
Ideally balanced for optimum control. Nylon covered shaft.
catalog_num 10068
stock_num 310
manu_code ANZ
cat_advert High-Quality Kickboard
cat_descr
White. Standard size.