1 Predicates A predicate specifies a condition that SQL evaluates as true, false, or unknown. Predicates are also called conditional expressions. You can specify several different types of predicates with different conditional operators. The different types of predicates are: o Basic o BETWEEN o Complex o CONTAINING o EXISTS o IN o IS NULL o LIKE o MATCHING o Quantified o SINGLE o STARTING WITH o UNIQUE When you compare character value expressions, if automatic translation has not been enabled, character sets of those value expressions must be identical. Some predicates have a specific behavior when used with the DEC Multinational Character Set (MCS). The following list describes multinational character set behavior that applies to predicates: o The character ñ is always treated as different from the character n, in keeping with the practices of the Spanish language. In a similar manner, the character ç is treated the same as the character c, in keeping with the practices of the French language. o The character ü is treated the same as the character u for many languages, but is sorted between the characters x and z (with the ys) for Danish, Norwegian, and Finnish languages. The following diagram shows the syntax for predicates: (B)0predicate =    qwqwqqqqqqqqwqwq> basic-predicate qqqqqqqqqqqqqqqqwqwqq>   x mq> NOT qj tq> between-predicate qqqqqqqqqqqqqqu x   x tq> containing-predicate qqqqqqqqqqqu x   x tq> exists-predicate qqqqqqqqqqqqqqqu x   x tq> in-predicate qqqqqqqqqqqqqqqqqqqu x   x tq> is-null-predicate qqqqqqqqqqqqqqu x   x tq> like-predicate qqqqqqqqqqqqqqqqqu x  x tq> matching-predicate qqqqqqqqqqqqqu x  x tq> quantified-predicate qqqqqqqqqqqu x   x tq> single-predicate qqqqqqqqqqqqqqqu x  x tq> unique-predicate qqqqqqqqqqqqqqqu x  x tq> starting-with-predicate qqqqqqqqu x   x mq> (predicate) qqqqqqqqqqqqqqqqqqqqj x   mqqqqqqqqqqqqqqqqqwq AND 'A' 'a' > 'Z' 'a' < 'z' 'A' < 'z' 'A' < 'Z' 2 Basic_Predicate A basic predicate compares two values. (B)0basic-predicate =    qqqq> value-expr qqwq> = qqwq> value-expr qqqq>   tq> <> qu   tq> ^= qu   tq> != qu   tq> < qqu   tq> <= qu   tq> > qqu   mq> >= qj    See the Value_Expressions HELP topic for details on value expressions. 2 BETWEEN_Predicate A BETWEEN predicate compares a value with a range of values. (B)0between-predicate =    qq> value-expr qwqqqqqqqqwq> BETWEEN qwqqqqqqqqqqqqqqqqwqk  mq> NOT qj tq> ASYMMETRIC qqu x   mq> SYMMETRIC qqqj x  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mq> value-expr AND value-expr qqqqqqqqqqqqqqqqqqqqqqqqqqq> See the Value_Expressions HELP topic for details on value expressions. ASYMMETRIC is the default. The BETWEEN predicate is a simpler way of representing conditions that can be represented using other conditional operators: value1 BETWEEN value2 AND value3 Using the BETWEEN predicate is the same as using the following complex predicate: value1 >= value2 AND value1 <= value3 ASYMMETRIC By default, the BETWEEN predicate depends on the ordering of the values. i.e. the first value expression needed to be less than or equal to the second value expression and was evaluated as equivalent to: V0 following example. SQL> select a from t where a between asymmetric 2 and 4; A 2 3 4 3 rows selected The following query returns zero matches because the value expressions are out of order. SQL> select a from t where a between asymmetric 4 and 2; 0 rows selected SYMMETRIC This alternate format for BETWEEN allows simpler comparision of unordered value expressions, as can be seen in these examples which return the same results. This comparision is equivalent to: (V0 For example: SQL> select a from t where a between symmetric 2 and 4; A 2 3 4 3 rows selected SQL> select a from t where a between symmetric 4 and 2; A 2 3 4 3 rows selected Note that NOT BETWEEN operation also changes when using SYMMETRIC This first query using ASYMMETRIC returns all values not in the specified range. SQL> select a from t where a not between asymmetric 2 and 4; A 1 5 2 rows selected In this next query the range values is out of order and the BETWEEN predicate returns an empty set of matches, and therefore NOT BETWEEN returns all rows in the example table. SQL> select a from t where a not between asymmetric 4 and 2; A 1 2 3 4 5 5 rows selected Contrast this to SYMMETRIC which returns the same set of values for either ordering of values: SQL> select a from t where a not between symmetric 2 and 4; A 1 5 2 rows selected SQL> select a from t where a not between symmetric 4 and 2; A 1 5 2 rows selected 2 Complex_Predicate A complex predicate combines any number of predicates with the Boolean operators AND, OR, and NOT. Boolean operators are also called logical operators. (B)0complex-predicate =    qwqwqqqqqqqqwqwqq> complex-predicate qwqqqwqqqqqqqqqqq>   x mq> NOT qj mqq> predicate qqqqqqqqqj tq> AND qwqk   x mq> OR qqj x   mqqqqqqqqqqqqqqqqqqqqqqqqq value-expr qwqqqqqqqwq> CONTAINING qq> value-expr qq>   m> NOT qj    The CONTAINING predicate is not case sensitive. The CONTAINING predicate is sensitive to diacritical markings used in any Multinational Character Set. Therefore, a matches a, but neither matches á, à, ä, Á, À, Â and so on. In Spanish, ch and ll are treated as if they were unique single characters. If you use a collating sequence, the CONTAINING predicate will not be sensitive to diacritical markings used in any Multinational Character Set. 2 EXISTS_Predicate An EXISTS predicate tests whether or not the result table specified in a column select expression is empty. (B)0exists-predicate =    qqq> EXISTS ( select-expr ) qqqqqqq>  If the result table specified in the select expression has one or more rows, SQL evaluates the EXISTS predicate as true. Otherwise, the predicate is false. An EXISTS predicate cannot be unknown. Because it only checks for the existence of rows, an EXISTS predicate does not require that the result table from its column select expression be a single column wide (see Column_Select_ Expressions for details on column select expressions). For EXISTS predicates, an asterisk (*) wildcard in the column select expression can refer to a multicolumn table . 2 IN_Predicate An IN predicate compares a value with another value or a collection of values. (B)0in-predicate =    qq> value-expr wqqqqqqqqwq> IN wq> value-expr qqqqqqqqqqqqqqqqqqwq>   mq> NOT qj mq> ( qwwq> value-expr qqwwq> ) j   xmq> select-expr qqjx   mqqqqqqqqqq , value-expr qqq> IS qwqqqqqqqqwq> NULL qqq>   mq> NOT qj  See the Value_Expressions HELP topic for details on value expressions. SQL never evaluates an IS NULL predicate as unknown; it is always true or false. If the value expression is null, SQL evaluates the predicate as true. If the value expression is not null, the predicate is false. Use an IS NULL predicate to retrieve rows with null values in particular columns. An IS NULL predicate is the only way to construct a query that includes rows in a result table by testing whether or not particular columns in the rows have null values. Other constructions such as NOT LIKE or <> (not equal) do not include rows with null values in their result tables. 2 LIKE_Predicate A LIKE predicate searches character string literals for pattern matches. The LIKE predicate is case sensitive; it considers uppercase and lowercase forms of the same character to be different characters. Because the LIKE predicate is case sensitive, searches for uppercase characters do not include lowercase characters in any Multinational Character Set. The reverse is also true. For example, LIKE "Ç" will retrieve a different set of records than LIKE "ç". The LIKE predicate is sensitive to diacritical markings used in any Multinational Character Set. Therefore, a matches a, but neither matches á, à, ä, Á, À, Â and so on. In Spanish, ch and ll are treated as if they are unique single letters. For example, if a domain is defined with the collating sequence SPANISH, then LIKE "c%" will not retrieve the word char but will retrieve the word cat. The LIKE predicate has this form: (B)0like-predicate =    qqq> value-expr qwqqqqqqwq> LIKE qqqqq> <pattern> k   m> NOT j x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   mqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqq>   x tq> ESCAPE <escape-character> qu x   x mq> IGNORE CASE qqqqqqqqqqqqqqqj x   mqqqqqqqqqqqqqqqq char-value-expr qqqq>  (B)0escape-character =    qqqq> char-value-expr qqqqqqqqqqqq>    SQL interprets the value-expr argument as a character string and compares it to the pattern. The pattern must be a value expression with a text data type. Within the pattern, the percent sign (%), underscore (_), and escape characters have special meaning. o The percent sign represents any string of characters, including no characters at all. The percent sign is a wildcard character. o The underscore represents any single character. o An escape character causes SQL to interpret a wildcard character as itself to search for character strings containing the wildcard character. The value of the escape character must be 1 character in length. The following table explains the valid sequences allowed for escape characters. Table 7 Escape Character Sequences Character in Pattern Character Matched % any string _ any character escape-character % % escape-character _ _ escape-character escape-character escape-character You can only specify the percent sign, underscore, or the escape-character itself. Any other character is invalid and an error is returned. All other characters represent themselves. 2 MATCHING_Predicate A MATCHING predicate searches character string literals for pattern matches. The pattern string accepts the following pattern characters: o * Matches any string of zero or more characters o % Matches any single character (B)0matching-predicate =    qqq> value-expr qwqqqqqqwq> MATCHING qqqqqq> <pattern>  m> NOT j    (B)0pattern =    qqqq> char-value-expr qqqq>  Usage Notes o If either of the expressions is null, the result is null. o MATCHING predicate is not case sensitive; it considers uppercase and lowercase forms of the same character to be a match. o The MATCHING predicate is not sensitive to diacritical markings used in the DEC Multinational Character Set. The following example shows the use of the MATCHING clause. SQL> select last_name cont> from employees cont> where last_name matching '%on*'; LAST_NAME Connolly Lonergan 2 rows selected SQL> 2 Quantified_Predicate A quantified predicate compares a value with a collection of values. It has the same form as a basic predicate except that the second operand must be a column select expression preceded by an ALL, ANY, or SOME comparison operator. (B)0quantified-predicate =    qq> value-expr qqwq> = qqwqwq> ALL qqwq> ( col-select-expr ) qq>   tq> <> qu tq> ANY qqu   tq> ^= qu mq> SOME qj   tq> != qu  tq> < qqu tq> <= qu  tq> > qqu   mq> >= qj  The following table describes the value of the result based on the comparison of values for the quantified predicate. Table 8 Quantified Predicate Result Table Comparison of Values Result ALL Quantifier If all comparisons are True True If any comparisons are False False If no comparisons are True made Otherwise Unknown SOME and ANY Quantifiers If any comparisons are True True If all comparisons are False False If no comparisons are True made Otherwise Unknown 2 SINGLE_Predicate The SINGLE predicate tests whether or not the result table specified in the column select expression has exactly one row. If it has exactly one row, SQL evaluates the SINGLE predicate as true. If the result table has zero rows or more than one row, the predicate is false. SQL evaluates the NOT SINGLE predicate as true if the result table specified in the select expression has zero rows or more than one row. The SINGLE and NOT SINGLE predicates cannot be unknown. The SINGLE predicate has the following form: (B)0single-predicate =    qqq> SINGLE qq> ( select-expr ) qqqqq> Because it checks only for the existence of rows, a SINGLE predicate does not require that the result table from its column select expression be a single column wide (see Column_Select_ Expressions for details on column select expressions). For SINGLE predicates, an asterisk (*) wildcard in the column select expression can refer to a multicolumn table (as in the following example). 2 STARTING_WITH_Predicate The STARTING WITH predicate tests whether or not the first characters of the first value expression match those specified in the second value expression. The STARTING WITH predicate has the following form: (B)0starting-with-predicate =    qq> value-expr qwqqqqqqwq> STARTING WITH qq> value-expr qq>   m NOT qj    Because the STARTING WITH predicate is case sensitive, it searches for uppercase characters and does not include lowercase characters for the DEC Multinational Character Set; the reverse is also true. For example, STARTING WITH 'Ç' retrieves a set of records different from those retrieved by STARTING WITH 'ç'. The STARTING WITH predicate is sensitive to diacritical markings used in any Multinational Character Set. Therefore, a matches a, but neither matches á, à, ä, Á, À, Â and so on. In Spanish, ch and ll are treated as if they were unique single characters. For example, if a domain is defined with the collating sequence SPANISH, then STARTING WITH 'c' does not retrieve the word char, but retrieves the word cat. 2 UNIQUE_Predicate The UNIQUE predicate is used to determine if duplicate rows exist in the result table of a column select expression. Note that the UNIQUE predicate (in compliance with the SQL language standard) ignores rows with a NULL column value and ensures uniqueness for the other column values. Contrast this with the SINGLE predicate, which considers a single column value of NULL as a match for any other NULL value in the same column. (B)0unique-predicate =    qqq> UNIQUE qqq> ( col-select-expr ) qqq>    If any two rows in the expression are equal to one another, the UNIQUE predicate evaluates to false. The following example determines those cities in which one and only one employee from the EMPLOYEES database lives. SQL> SELECT E.LAST_NAME, E.CITY FROM EMPLOYEES E cont> WHERE UNIQUE cont> (SELECT * FROM EMPLOYEES EMP cont> WHERE EMP.CITY=E.CITY); LAST_NAME CITY Harrison Boston Smith Bristol McElroy Cambridge Kilpatrick Marlow Sciacca Munsonville Vormelker Rochester Dement Sanbornton Babbin Sanbornville Keisling Twin Mountain Ziemke Winnisquam Johnston Wolfeboro 11 rows selected