User's Guide Part VI. SQL Anywhere Reference Chapter 40. Watcom-SQL Language ReferenceTo specify a search condition for a WHERE clause, a HAVING clause, a CHECK clause, a JOIN clause or an IF expression.
search condition:
expression compare expression
| expression compare ANY ( subquery )
| expression compare ALL ( subquery )
| expression IS [ NOT ] NULL
| expression [ NOT ] LIKE expression [ESCAPE expression]
| expression [ NOT BETWEEN expression AND expression
| expression [ NOT ] IN ( expression)
| expression [ NOT ] IN ( subquery )
| expression [ NOT] IN (value-expr1, value-expr2[, value-expr3] ... )
| EXISTS ( subquery )
| NOT condition
| condition AND condition
| condition OR condition
| ( condition )
| ( condition , estimate )
| condition IS [ NOT ] TRUE
| condition IS [ NOT ] FALSE
| condition IS [ NOT ] UNKNOWN
compare:
one of = > < >= <= <> != ~= !< !>
Anywhere.
Must be connected to the database.
None.
Conditions are used as 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.
Subqueries that return exactly one column and either zero or one row can be used in any SQL statement anywhere that 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 returning any number of columns and rows can be used in EXISTS conditions. These conditions are discussed in the following sections.
The syntax for comparison conditions is as follows:
... expression compare expression
where compare is a comparison operator. The following comparison operators are available in SQL Anywhere:
| 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. |
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, leaving UNKNOWN unchanged.
The BETWEEN conditions is equivalent to a combination of two inequalities:
expr >= start-expr AND expr <= end-expr
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, leaving 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 |
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.
Ranges and sets of characters can be given in LIKE search conditions using square brackets.
A set of characters to look for is specified by listing the characters inside the brackets. For example, the following condition finds the strings smith and smyth :
... LIKE 'sm[iy]th'
A range of characters to look for is specified by giving the ends of the range, 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 the chapter "Database Collations".
The lower end of the range must precede the higher end of the range. For example, any LIKE condition containing the expression [z-a] returns no rows, as no character matches the [z-a] range.
Unless the database was created as a case-insensitive database, 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.
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".
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".
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 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, leaving UNKNOWN unchanged.
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 expressionis 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 expressionis 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.
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.
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 as FALSE otherwise. The NOT keyword reverses the meaning of the condition.
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.
The result of a comparison is UNKNOWN if either value being compared is the NULL value. Rows satisfy a search condition if and only if the result of the condition is TRUE.
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.
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, leaving UNKNOWN unchanged.
The following tables show how the AND, OR, NOT, and IS logical operators of SQL work in three-valued logic.
| AND | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | FALSE | UNKNOWN |
| FALSE | FALSE | FALSE | FALSE |
| UNKNOWN | UNKNOWN | FALSE | UNKNOWN |
| OR | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| FALSE | TRUE | FALSE | UNKNOWN |
| UNKNOWN | TRUE | UNKNOWN | UNKNOWN |
| NOT | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| FALSE | TRUE | UNKNOWN |
| IS | TRUE | FALSE | UNKNOWN |
|---|---|---|---|
| TRUE | TRUE | FALSE | FALSE |
| FALSE | FALSE | TRUE | FALSE |
| UNKNOWN | FALSE | FALSE | TRUE |