Search conditions

Reference Manual
CHAPTER 6. SQL Language Elements

Search conditions

Function

To specify a search condition for a WHERE clause, a HAVING clause, a CHECK clause, a JOIN clause, or an IF expression.

Syntax

search condition:
expression compare expression
| expression compare { [ ANY | SOME ] | ALL }( subquery )
| expression IS [ NOT ] NULL
| expression [ NOT ] BETWEEN expression AND expression
| expression [ NOT ] LIKE expression [ ESCAPE expression ]
| expression [ NOT ] IN ( { expression | subquery | value-expr1 , value-expr2 [, value-expr3 ] ...} )
| EXISTS ( subquery )
| NOT condition
| condition AND condition
| condition OR condition
| ( condition )
| ( condition , estimate )
| condition IS [ NOT ] { TRUE | FALSE | UNKNOWN }

Parameters

compare:
= | > | < | >= | <= | <> | != | !< | !>

Usage

Anywhere.

Authorization

Must be connected to the database.

Side effects

None.

Description

Conditions are used to choose a subset of the rows from a table, or in a control statement such as an IF statement to determine control of flow.

SQL conditions do not follow boolean logic, where conditions are either true or false. In SQL, every condition evaluates as one of TRUE, FALSE, or UNKNOWN. This is called three-valued logic. The result of a comparison is UNKNOWN if either value being compared is the NULL value. For tables showing how logical operators combine in three-valued logic, see the section Three-valued logic.

Rows satisfy a search condition if and only if the result of the condition is TRUE. Rows for which the condition is UNKNOWN do not satisfy the search condition. For more information about NULL, see NULL value.

Subqueries form an important class of expression that is used in many search conditions. For information about using subqueries in search conditions, see Subqueries in search conditions.

The different types of search condition are discussed in the following sections.

Comparison conditions

The syntax for comparison conditions is as follows:

... expression compare expression

where compare is a comparison operator. The following comparison operators are available:

operator

description

=

Equal to

>

Greater than

<

Less than

>=

Greater than or equal to

<=

Less than or equal to

!=

Not equal to

<>

Not equal to

!>

Not greater than

!<

Not less than

operator	description
=	Equal to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
!=	Not equal to
<>	Not equal to
!>	Not greater than
!<	Not less than

Comparisons are case insensitive
All string comparisons are case insensitive unless the database was created as case sensitive.

Compatibility

Trailing blanks Any trailing blanks in character data are ignored for comparison purposes by Adaptive Server Enterprise. The behavior of Adaptive Server Anywhere when comparing strings is controlled the -b command-line switch that is set when creating the database.
Case sensitivity By default, Adaptive Server Anywhere databases are created as case insensitive, while Adaptive Server Enterprise databases are created as case sensitive. Comparisons are carried out with the same attention to case as the database they are operating on. You can control the case sensitivity of Adaptive Server Anywhere databases when creating the database.

Subqueries in search conditions

Subqueries that return exactly one column and either zero or one row can be used in any SQL statement wherever a column name could be used, including in the middle of an expression.

For example, expressions can be compared to subqueries in comparison conditions (see Comparison conditions) as long as the subquery does not return more than one row. If the subquery (which must have one column) returns one row, then the value of that row is compared to the expression. If a subquery returns no rows, its value is NULL.

Subqueries that return exactly one column and any number of rows can be used in IN conditions, ANY conditions, and ALL conditions. Subqueries that return any number of columns and rows can be used in EXISTS conditions. These conditions are discussed in the following sections.

ALL or ANY conditions

The syntax for ANY conditions is

... expression compare ANY ( subquery )

where compare is a comparison operator.

For example, an ANY condition with an equality operator:

... expression = ANY ( subquery )

is TRUE if expression is equal to any of the values in the result of the subquery, and FALSE is the expression is not NULL and does not equal any of the columns of the subquery. The ANY condition is UNKNOWN if expression is the NULL value, unless the result of the subquery has no rows, in which case the condition is always FALSE.

The keyword SOME can be used instead of ANY.

Compatibility

ANY and ALL subqueries are compatible between Adaptive Server Enterprise and Adaptive Server Anywhere. Only Adaptive Server Anywhere supports SOME as a synonym for ANY.

BETWEEN conditions

The syntax for BETWEEN conditions is as follows:

... expr [ NOT ] BETWEEN start-expr AND end-expr

The BETWEEN condition can evaluate as TRUE, FALSE, or UNKNOWN. Without the NOT keyword, the condition evaluates as TRUE if expr is between start-expr and end-expr. The NOT keyword reverses the meaning of the condition but leaves UNKNOWN unchanged.

The BETWEEN conditions is equivalent to a combination of two inequalities:

expr >= start-expr AND expr <= end-expr

Compatibility

The BETWEEN condition is compatible between Adaptive Server Anywhere and Adaptive Server Enterprise.

LIKE conditions

The syntax for LIKE conditions is as follows:

... expression [NOT] LIKE pattern [ESCAPE escape-expr]

The LIKE condition can evaluate as TRUE, FALSE, or UNKNOWN.

Without the NOT keyword, the condition evaluates as TRUE if expression matches the pattern. If either expression or pattern is the NULL value, this condition is UNKNOWN. The NOT keyword reverses the meaning of the condition, but leaves UNKNOWN unchanged.

The pattern may contain any number of wild cards. The wild cards are:

Wild card

Matches

_ (underscore)

Any one character

% (percent)

Any string of zero or more characters

[]

Any single character in the specified range or set

[^]

Any single character not in the specified range or set

Wild card	Matches
_ (underscore)	Any one character
% (percent)	Any string of zero or more characters
[]	Any single character in the specified range or set
[^]	Any single character not in the specified range or set

All other characters must match exactly.

For example, the search condition

... name LIKE 'a%b_'

is TRUE for any row where name starts with the letter a and has the letter b as its second last character.

If an escape-expr is specified, it must evaluate to a single character. The character can precede a percent, an underscore, a left square bracket, or another escape character in the pattern to prevent the special character from having its special meaning. When escaped in this manner, a percent will match a percent, and an underscore will match an underscore.

All patterns of length 126 characters or less are supported. Patterns of length greater than 254 characters are not supported. Some patterns of length between 127 and 254 characters are supported, depending on the contents of the pattern.

Searching for one of a set of characters

A set of characters to look for is specified by listing the characters inside square brackets. For example, the following condition finds the strings smith and smyth:

... LIKE 'sm[iy]th'

Searching for one of a range of characters

A range of characters to look for is specified by giving the ends of the range inside square brackets, separated by a hyphen. For example, the following condition finds the strings bough and rough, but not tough:

... LIKE '[a-r]ough'

The range of characters [a-z] is interpreted as "greater than or equal to a, and less than or equal to z", where the greater than and less than operations are carried out within the collation of the database. For information on ordering of characters within a collation, see Database Collations and International Languages.

The lower end of the range must precede the higher end of the range. For example, a LIKE condition containing the expression [z-a] returns no rows, because no character matches the [z-a] range.

Unless the database is created as case-sensitive, the range of characters is case insensitive. For example, the following condition finds the strings Bough, rough, and TOUGH:

... LIKE '[a-z]ough'

If the database is created as a case-sensitive database, the search condition is case sensitive also.

Combining searches for ranges and sets

You can combine ranges and sets within a square bracket. For example, the following condition finds the strings bough, rough, and tough:

... LIKE '[a-rt]ough'

The bracket [a-mpqs-z] is interpreted as "exactly one character that is either in the range a to m inclusive, or is p, or is q, or is in the range s to z inclusive".

Searching for one character not in a range

The caret character (^) is used to specify a range of characters that is excluded from a search. For example, the following condition finds the string tough, but not the strings rough, or bough:

... LIKE '[^a-r]ough'

The caret negates the entire rest of the contents of the brackets. For example, the bracket [^a-mpqs-z] is interpreted as "exactly one character that is not in the range a to m inclusive, is not p, is not q, and is not in the range s to z inclusive".

Special cases of ranges and sets

Any single character in square brackets means that character. For example, [a] matches just the character a. [^] matches just the caret character, [%] matches just the percent character (the percent character does not act as a wild card in this context), and [_] matches just the underscore character. Also, [[] matches just the character [.

Other special cases are as follows:

The expression [a-] matches either of the characters a or -.
The expression [] is never matched and always returns no rows.
The expressions [ or [abp-q are ill-formed expressions, and give syntax errors.
You cannot use wild cards inside square brackets. The expression [a%b] finds one of a, %, or b.
You cannot use the caret character to negate ranges except as the first character in the bracket. The expression [a^b] finds one of a, ^, or b.

Compatibility

The ESCAPE clause is supported by Adaptive Server Anywhere only.

IN conditions

The syntax for IN conditions is as follows:

...expression [ NOT ] IN ( subquery )
| expression [ NOT ] IN ( expression )
| expression [ NOT ] IN (value-expr1 , value-expr2 [, value-expr3 ] ... )

Without the NOT keyword, the IN conditions is TRUE if expression equals any of the listed values, UNKNOWN if expression is the NULL value, and FALSE otherwise. The NOT keyword reverses the meaning of the condition, but leaves UNKNOWN unchanged.

Compatibility

IN conditions are compatible between Adaptive Server Enterprise and Adaptive Server Anywhere.

EXISTS conditions

The syntax for EXISTS conditions is as follows:

... EXISTS( subquery )

The EXISTS condition is TRUE if the subquery result contains at least one row, and FALSE if the subquery result does not contain any rows. The EXISTS condition cannot be UNKNOWN.

Compatibility

The EXISTS condition is compatible between Adaptive Server Enterprise and Adaptive Server Anywhere.

IS NULL conditions

The syntax for IS NULL conditions is as follows:

expression IS [ NOT ] NULL

Without the NOT keyword, the IS NULL condition is TRUE if the expression is the NULL value, and FALSE otherwise. The NOT keyword reverses the meaning of the condition.

Compatibility

The IS NULL condition is compatible between Adaptive Server Enterprise and Adaptive Server Anywhere.

Conditions with logical operators

Search conditions can be combined using AND, OR and NOT.

Conditions are combined using AND as follows:

... condition1 AND condition2

The combined condition is TRUE if both conditions are TRUE, FALSE if either condition is FALSE, and UNKNOWN otherwise.

Conditions are combined using OR as follows:

... condition1 OR condition2

The combined condition is TRUE if either condition is TRUE, FALSE if both conditions are FALSE, and UNKNOWN otherwise.

Compatibility

The AND and OR operators are compatible between Adaptive Server Anywhere and Adaptive Server Enterprise.

NOT conditions

The syntax for NOT conditions is as follows:

... NOT condition1

The NOT condition is TRUE if condition1 is FALSE, FALSE if condition1 is TRUE, and UNKNOWN if condition1 is UNKNOWN.

Truth value conditions

The syntax for truth value conditions is as follows:

... IS [ NOT ] truth-value

Without the NOT keyword, the condition is TRUE if the condition evaluates to the supplied truth-value, which must be one of TRUE, FALSE, or UNKNOWN. Otherwise, the value is FALSE. The NOT keyword reverses the meaning of the condition, but leaves UNKNOWN unchanged.

Compatibility

Truth-valued conditions are supported by Adaptive Server Anywhere only.

Three-valued logic

The following tables show how the AND, OR, NOT, and IS logical operators of SQL work in three-valued logic.

AND operator

AND

TRUE

FALSE

UNKNOWN

TRUE

TRUE

FALSE

UNKNOWN

FALSE

FALSE

FALSE

FALSE

UNKNOWN

UNKNOWN

FALSE

UNKNOWN

AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

OR operator

OR

TRUE

FALSE

UNKNOWN

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

UNKNOWN

UNKNOWN

TRUE

UNKNOWN

UNKNOWN

OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

NOT operator

TRUE

FALSE

UNKNOWN

FALSE

TRUE

UNKNOWN

TRUE	FALSE	UNKNOWN
FALSE	TRUE	UNKNOWN

IS operator

IS

TRUE

FALSE

UNKNOWN

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

TRUE

FALSE

UNKNOWN

FALSE

FALSE

TRUE

IS	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	FALSE
FALSE	FALSE	TRUE	FALSE
UNKNOWN	FALSE	FALSE	TRUE

User-supplied estimates

The Adaptive Server Anywhere query optimizer uses educated guesses to help decide the best strategy for executing a query. For each table in a potential execution plan, the optimizer must estimate the number of rows that will be part of the results. If you know that a condition has a success rate that differs from the optimizer rule, you can tell the database this information by using an estimate in the search condition.

The estimate is a percentage. It can be a positive integer or can contain fractional values.

Examples

The following query provides an estimate that one percent of the ship_date values will be later than 1994/06/30:
```
SELECT  ship_date
FROM  sales_order_items
WHERE ( ship_date > '1994/06/30', 1 )
ORDER BY ship_date DESC
```
The following query estimates that half a percent of the rows will satisfy the condition:
```
SELECT *
FROM customer c, sales_order o
WHERE (c.id = o.cust_id, 0.5)
```

Fractional values enable more accurate user estimates for joins, particularly for large tables.

Compatibility

Adaptive Server Enterprise does not support explicit estimates.

Search conditions

Function

Syntax

Parameters

Usage

Authorization

Side effects

See also

Description

Comparison conditions

Compatibility

Subqueries in search conditions

ALL or ANY conditions

Compatibility

BETWEEN conditions

Compatibility

LIKE conditions

Searching for one of a set of characters

Searching for one of a range of characters

Combining searches for ranges and sets

Searching for one character not in a range

Special cases of ranges and sets

Compatibility

IN conditions

Compatibility

EXISTS conditions

Compatibility

IS NULL conditions

Compatibility

Conditions with logical operators

Compatibility

NOT conditions

Truth value conditions

Compatibility

Three-valued logic

AND operator

OR operator

NOT operator

IS operator

User-supplied estimates

Examples

Compatibility