Informix Guide to SQL: Syntax Segments

Use a condition to test data to determine whether it meets certain qualifications. Use the Condition segment wherever you see a reference to a condition in a syntax diagram.

A condition is a collection of one or more search conditions, optionally connected by the logical operators AND or OR. Search conditions fall into the following categories:

• Comparison conditions (also called filters or Boolean expressions)
• Conditions with a subquery

A condition can contain only an aggregate function if it is used in the HAVING clause of a SELECT statement or the HAVING clause of a subquery. You cannot use an aggregate function in a comparison condition that is part of a WHERE clause in a DELETE, SELECT, or UPDATE statement unless the aggregate is on a correlated column that originates from a parent query and the WHERE clause is within a subquery that is within a HAVING clause.

If you preface a condition with the keyword NOT, the test is true only if the condition that NOT qualifies is false. If the condition that NOT qualifies is unknown (uses a null in the determination), the NOT operator has no effect. The following truth table shows the effect of NOT. The letter T represents a true condition, F represents a false condition, and a question mark (?) represents an unknown condition. Unknown values occur when part of an expression that uses an arithmetic operator is null.

Five kinds of comparison conditions exist: Relational Operator, BETWEEN, IN, IS NULL, and LIKE and MATCHES. Comparison conditions are often called Boolean expressions because they evaluate to a simple true or false result. Their syntax is summarized in the following diagram and explained in detail after the diagram.

Refer to the following sections for more information on the use of the different types of comparison conditions:

• For relational-operator conditions, refer to Relational-Operator Condition.
• For the BETWEEN condition, refer to BETWEEN Condition.
• For the IN condition, refer to IN Condition.
• For the IS NULL condition, refer to IS NULL Condition.
• For the LIKE and MATCHES condition, refer to LIKE and MATCHES Condition.

For a discussion of the different types of comparison conditions in the context of the SELECT statement, see Using a Condition in the WHERE Clause.

Warning: When you specify a date value in a comparison condition, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the comparison condition. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the comparison condition, so the comparison condition might not work as you intended. For more information on the DBCENTURY environment variable, see the "Informix Guide to SQL: Reference."

Column Name

Quotation Marks in Conditions

When you compare a column expression with a constant expression in any type of comparison condition, observe the following rules:

• If the column has a numeric data type, do not surround the constant expression with quotation marks.
• If the column has a character data type, surround the constant expression with quotation marks.
• If the column has a date data type, surround the constant expression with quotation marks. Otherwise, you might get unexpected results.

The following example shows the correct use of quotation marks in comparison conditions. The ship_instruct column has a character data type. The order_date column has a date data type. The ship_weight column has a numeric data type.

SELECT * FROM orders
	WHERE ship_instruct = 'express'

	AND order_date > '05/01/98'

	AND ship_weight < 30

Some relational-operator conditions are shown in the following examples:

city[1,3] = 'San'



o.order_date > '6/12/98'



WEEKDAY(paid_date) = WEEKDAY(CURRENT-31 UNITS day)



YEAR(ship_date) < YEAR (TODAY)



quantity <= 3



customer_num <> 105



customer_num != 105

If either expression is null for a row, the condition evaluates to false. For example, if the paid_date column has a null, you cannot use either of the following statements to retrieve that row:

SELECT customer_num, order_date FROM orders

	WHERE paid_date = ''



SELECT customer_num, order_date FROM orders

	WHERE NOT PAID !=''

An IS NULL condition finds a null value, as shown in the following example. The IS NULL condition is explained fully in IS NULL Condition on page 4-36.

SELECT customer_num, order_date FROM orders

	WHERE paid_date IS NULL 

For a BETWEEN test to be true, the value of the expression on the left of the BETWEEN keyword must be in the inclusive range of the values of the two expressions on the right of the BETWEEN keyword. Null values do not satisfy the condition. You cannot use NULL for either expression that defines the range.

Some BETWEEN conditions are shown in the following examples:

order_date BETWEEN '6/1/97' and '9/7/97'



zipcode NOT BETWEEN '94100' and '94199'



EXTEND(call_dtime, DAY TO DAY) BETWEEN 

	(CURRENT - INTERVAL(7) DAY TO DAY) AND CURRENT



lead_time BETWEEN INTERVAL (1) DAY TO DAY 

	AND INTERVAL (4) DAY TO DAY



unit_price BETWEEN loprice AND hiprice

The IN condition is satisfied when the expression to the left of the word IN is included in the list of items.

The NOT option produces a search condition that is satisfied when the expression is not in the list of items. Null values do not satisfy the condition.

The following examples show some IN conditions:

WHERE state IN ('CA', 'WA', 'OR')



WHERE manu_code IN ('HRO', 'HSK')



WHERE user_id NOT IN (USER)



WHERE order_date NOT IN (TODAY) 

In ESQL/C, the built-in TODAY function is evaluated at execution time; the built-in CURRENT function is evaluated when a cursor opens or when the query executes, if it is a singleton SELECT statement.

The built-in USER function is case sensitive; it perceives minnie and Minnie as different values.

Using the IN Operator with Collection Data Types

You can use the IN operator to determine if an element is contained in a collection. The collection you search can be a simple or nested collection. In a nested collection type, the element type of the collection is also a collection type.

When you use the IN operator to search for an element in a collection, the expression to the left or right of the IN keyword cannot contain a BYTE or TEXT data type.

Suppose you create the following table that contains two collection columns:

CREATE TABLE tab_coll 
(
set_num SET(INT NOT NULL), 
list_name LIST(SET(CHAR(10) NOT NULL) NOT NULL)
);

The following partial examples show how you might use the IN operator for search conditions on the collection columns of the tab_coll table:

WHERE 5 IN set_num



WHERE 5.0::INT IN set_num



WHERE "5" NOT IN set_num



WHERE set_num IN ("SET{1,2,3}", "SET{7,8,9}")

WHERE "SET{'john', 'sally', 'bill'}" IN list_name


WHERE list_name IN ("LIST{""SET{'bill','usha'}"", 
						""SET{'ann' 'moshi'}""}",
					"LIST{""SET{'bob','ramesh'}"", 
						""SET{'bomani' 'ann'}""}")

In general, when you use the IN operator on a collection data type, the database server checks whether the value on the left of the IN operator is an element in the set of values on the right of the in operator.

The IS NULL condition is satisfied if the column contains a null value. If you use the IS NOT NULL option, the condition is satisfied when the column contains a value that is not null. The following example shows an IS NULL condition:

WHERE paid_date IS NULL

A LIKE or MATCHES condition tests for matching character strings. The condition is true, or satisfied, when either of the following tests is true:

• The value of the column on the left matches the pattern that the quoted string specifies. You can use wildcard characters in the string. Null values do not satisfy the condition.
• The value of the column on the left matches the pattern that the column on the right specifies. The value of the column on the right serves as the matching pattern in the condition.

You can use the single quote (') only with the quoted string to match a literal quote; you cannot use the ESCAPE clause. You can use the quote character as the escape character in matching any other pattern if you write it as ''''.

Important: You cannot specify a row-type column in a LIKE or MATCHES condition. A row-type column is a column that is defined on a named row type or unnamed row type. NOT Option

The NOT option makes the search condition successful when the column on the left has a value that is not null and does not match the pattern that the quoted string specifies. For example, the following conditions exclude all rows that begin with the characters Baxter in the lname column:

WHERE lname NOT LIKE 'Baxter%'

WHERE lname NOT MATCHES 'Baxter*'

If you use the keyword LIKE, you can use the following wildcard characters in the quoted string.

Using the backslash (\) as an escape character is an Informix extension to ANSI-compliant SQL.

In an ANSI-compliant database, you can only use an escape character to escape a percent sign (%), an underscore (_), or the escape character itself.

The following condition tests for the string tennis, alone or in a longer string, such as tennis ball or table tennis paddle:

WHERE description LIKE '%tennis%'

The following condition tests for all descriptions that contain an underscore. The backslash (\) is necessary because the underscore (_) is a wildcard character.

WHERE description LIKE '%\_%'

The LIKE operator has an associated operator function called like(). You can define a like() function to handle your own user-defined data types. For more information, see the Extending Informix Dynamic Server 2000 manual.

If you use the keyword MATCHES, you can use the following wildcard characters in the quoted string.

The following condition tests for the string tennis, alone or in a longer string, such as tennis ball or table tennis paddle:

WHERE description MATCHES '*tennis*'

The following condition is true for the names Frank and frank:

WHERE fname MATCHES '[Ff]rank'

The following condition is true for any name that begins with either F or f:

WHERE fname MATCHES '[Ff]*'

The following condition is true for any name that ends with the letters a, b, c, or d:

WHERE fname MATCHES '*[a-d]'

The MATCHES operator has an associated operator function called matches(). You can define a matches() function to handle your own user-defined data types. For more information, see the Extending Informix Dynamic Server 2000 manual.

The ESCAPE clause lets you include an underscore (_) or a percent sign (%) in the quoted string and avoid having them be interpreted as wildcards. If you choose to use z as the escape character, the characters z_ in a string stand for the underscore character (_). Similarly, the characters z% represent the percent sign (%). Finally, the characters zz in the string stand for the single character z. The following statement retrieves rows from the customer table in which the company column includes the underscore character:

SELECT * FROM customer

	WHERE company LIKE '%z_%' ESCAPE 'z'

You can also use a single-character host variable as an escape character. The following statement shows the use of a host variable as an escape character:

EXEC SQL BEGIN DECLARE SECTION;

	char escp='z';

	char fname[20];

EXEC SQL END DECLARE SECTION;

EXEC SQL select fname from customer 

	into :fname

	where company like '%z_%' escape :escp;

The ESCAPE clause lets you include a question mark (?), an asterisk (*), and a left or right square bracket ([]) in the quoted string and avoid having them be interpreted as wildcards. If you choose to use z as the escape character, the characters z? in a string stand for the question mark (?). Similarly, the characters z* stand for the asterisk (*). Finally, the characters zz in the string stand for the single character z.

The following example retrieves rows from the customer table in which the value of the company column includes the question mark (?):

SELECT * FROM customer

	WHERE company MATCHES '*z?*' ESCAPE 'z'

A stand-alone condition can be any expression that is not explicitly listed in the syntax for the comparison condition. Such an expression is valid only if its result is of the Boolean type. For example, the following example returns a value of the Boolean type:

funcname(x) 

You can use a SELECT statement within a condition; this combination is called a subquery. You can use a subquery in a SELECT statement to perform the following functions:

• Compare an expression to the result of another SELECT statement
• Determine whether an expression is included in the results of another SELECT statement
• Ask whether another SELECT statement selects any rows

The subquery can depend on the current row that the outer SELECT statement is evaluating; in this case, the subquery is a correlated subquery.

The kinds of subquery conditions are shown in the following sections with their syntax. For a discussion of the different kinds of subquery conditions in the context of the SELECT statement, see Using a Condition in the WHERE Clause.

A subquery can return a single value, no value, or a set of values depending on the context in which it is used. If a subquery returns a value, it must select only a single column. If the subquery simply checks whether a row (or rows) exists, it can select any number of rows and columns. A subquery cannot contain BYTE or TEXT data types, nor can it contain an ORDER BY clause. For a complete description of SELECT syntax and usage, see SELECT.

An IN subquery condition is true if the value of the expression matches one or more of the values that the subquery selects. The subquery must return only one column, but it can return more than one row. The keyword IN is equivalent to the =ANY sequence. The keywords NOT IN are equivalent to the !=ALL sequence. See ALL, ANY, SOME Subquery.

The following example of an IN subquery finds the order numbers for orders that do not include baseball gloves (stock_num = 1):

WHERE order_num NOT IN

	(SELECT order_num FROM items WHERE stock_num = 1)

Because the IN subquery tests for the presence of rows, duplicate rows in the subquery results do not affect the results of the main query. Therefore, you can put the UNIQUE or DISTINCT keyword into the subquery with no effect on the query results, although eliminating testing duplicates can reduce the time needed for running the query.

An EXISTS subquery condition evaluates to true if the subquery returns a row. With an EXISTS subquery, one or more columns can be returned. The subquery always contains a reference to a column of the table in the main query. If you use an aggregate function in an EXISTS subquery, at least one row is always returned.

The following example of a SELECT statement with an EXISTS subquery returns the stock number and manufacturer code for every item that has never been ordered (and is therefore not listed in the items table). You can appropriately use an EXISTS subquery in this SELECT statement because you use the subquery to test both stock_num and manu_code in items.

SELECT stock_num, manu_code FROM stock

	WHERE NOT EXISTS (SELECT stock_num, manu_code FROM items

		WHERE stock.stock_num = items.stock_num AND

		stock.manu_code = items.manu_code)

The preceding example works equally well if you use SELECT * in the subquery in place of the column names because the existence of the whole row is tested; specific column values are not tested.

You use the ALL, ANY, and SOME keywords to specify what makes the search condition true or false. A search condition that is true when the ANY keyword is used might not be true when the ALL keyword is used, and vice versa.

The ALL keyword denotes that the search condition is true if the comparison is true for every value that the subquery returns. If the subquery returns no value, the condition is true.

In the following example of the ALL subquery, the first condition tests whether each total_price is greater than the total price of every item in order number 1023. The second condition uses the MAX aggregate function to produce the same results.

total_price > ALL (SELECT total_price FROM items

 					WHERE order_num = 1023)



total_price > (SELECT MAX(total_price) FROM items

 					WHERE order_num = 1023)

Using the NOT keyword with an ALL subquery tests whether an expression is not true for at least one element returned by the subquery. For example, the following condition is true when the expression total_price is not greater than all the selected values. That is, it is true when total_price is not greater than the highest total price in order number 1023.

NOT total_price > ALL (SELECT total_price FROM items

	 					WHERE order_num = 1023)

The ANY keyword denotes that the search condition is true if the comparison is true for at least one of the values that is returned. If the subquery returns no value, the search condition is false. The SOME keyword is an alias for ANY.

The following conditions are true when the total price is greater than the total price of at least one of the items in order number 1023. The first condition uses the ANY keyword; the second uses the MIN aggregate function.

total_price > ANY (SELECT total_price FROM items

 					WHERE order_num = 1023)



total_price > (SELECT MIN(total_price) FROM items

					WHERE order_num = 1023)

Using the NOT keyword with an ANY subquery tests whether an expression is not true for all elements returned by the subquery. For example, the following condition is true when the expression total_price is not greater than any selected value. That is, it is true when total_price is greater than none of the total prices in order number 1023.

NOT total_price > ANY (SELECT total_price FROM items

	 					WHERE order_num = 1023)

You can omit the keywords ANY, ALL, or SOME in a subquery if you know that the subquery will return exactly one value. If you omit the ANY, ALL, or SOME keywords, and the subquery returns more than one value, you receive an error. The subquery in the following example returns only one row because it uses an aggregate function:

SELECT order_num FROM items 

	WHERE stock_num = 9 AND quantity =

		(SELECT MAX(quantity) FROM items WHERE stock_num = 9)

You can combine simple conditions with the logical operators AND or OR to form complex conditions. The following SELECT statements contain examples of complex conditions in their WHERE clauses:

SELECT customer_num, order_date FROM orders

	WHERE paid_date > '1/1/97' OR paid_date IS NULL



SELECT order_num, total_price FROM items

	WHERE total_price > 200.00 AND manu_code LIKE 'H%'



SELECT lname, customer_num FROM customer

	WHERE zipcode BETWEEN '93500' AND '95700' 

	OR state NOT IN ('CA', 'WA', 'OR')

The following truth tables show the effect of the AND and OR operators.The letter T represents a true condition, F represents a false condition, and the question mark (?) represents an unknown value. Unknown values occur when part of an expression that uses a logical operator is null.

If the Boolean expression evaluates to UNKNOWN, the condition is not satisfied.

Consider the following example within a WHERE clause:

WHERE ship_charge/ship_weight < 5

	AND order_num = 1023

The row where order_num = 1023 is the row where ship_weight is null. Because ship_weight is null, ship_charge/ship_weight is also null; therefore, the truth value of ship_charge/ship_weight < 5 is UNKNOWN. Because order_num = 1023 is TRUE, the AND table states that the truth value of the entire condition is UNKNOWN. Consequently, that row is not chosen. If the condition used an OR in place of the AND, the condition would be true.

For discussions of comparison conditions in the SELECT statement and of conditions with a subquery, see the Informix Guide to SQL: Tutorial.

For information on the GLS aspects of conditions, see the Informix Guide to GLS Functionality.