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: predicate = -+-+--------+-+-> basic-predicate ----------------+-+--> | +-> NOT -+ +-> between-predicate --------------+ | | +-> containing-predicate -----------+ | | +-> exists-predicate ---------------+ | | +-> in-predicate -------------------+ | | +-> is-null-predicate --------------+ | | +-> like-predicate -----------------+ | | +-> matching-predicate -------------+ | | +-> quantified-predicate -----------+ | | +-> single-predicate ---------------+ | | +-> unique-predicate ---------------+ | | +-> starting-with-predicate --------+ | | +-> (predicate) --------------------+ | +-----------------+- AND <--+----------------------+ +-- OR <--+ NOTE Except for the IS NULL, EXISTS, and SINGLE operators, if either operand in a predicate is null, the value of the predicate is unknown. You cannot use a value of the LIST OF BYTE VARYING data type for either operand in a comparison predicate. For more information, see the LIST_OF_BYTE_VARYING HELP topic. When you use the DEC_MCS or ASCII character set, SQL compares character string literals according to the ASCII collating sequence. Therefore, it considers lowercase letters to have a greater value than uppercase letters, and considers the letters near the beginning of the alphabet to have a lesser value than those near the end. 'a' > 'A' 'a' > 'Z' 'a' < 'z' 'A' < 'z' 'A' < 'Z' 2 Basic_Predicate A basic predicate compares two values. basic-predicate = ----> value-expr --+-> = --+-> value-expr ----> +-> <> -+ +-> ^= -+ +-> != -+ +-> < --+ +-> <= -+ +-> > --+ +-> >= -+ 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. between-predicate = --> value-expr -+--------+-> BETWEEN -+----------------+-+ +-> NOT -+ +-> ASYMMETRIC --+ | +-> SYMMETRIC ---+ | +-------------------------------------------------------+ +-> value-expr AND value-expr ---------------------------> 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. complex-predicate = -+-+--------+-+--> complex-predicate -+---+-----------> | +-> NOT -+ +--> predicate ---------+ +-> AND -+-+ | +-> OR --+ | +-------------------------<-------------------------+ When nesting predicates, you must enclose them in parentheses. SQL evaluates parts of a complex predicate in this order: 1. Predicates enclosed in parentheses If there are nested predicates in parentheses, the innermost predicate is evaluated first. 2. Predicates preceded by NOT 3. Predicates combined with AND 4. Predicates combined with OR The following tables summarize how SQL evaluates predicates combined with Boolean operators. Such tables are often called truth tables. Table 4 Boolean Operator: AND A B A AND B True False False True True True False False False False True False True Unknown Unknown False Unknown False Unknown True Unknown Unknown False False Unknown Unknown Unknown Table 5 Boolean Operator: OR A B A OR B True False True True True True False False False False True True True Unknown True False Unknown Unknown Unknown True True Unknown False Unknown Unknown Unknown Unknown Table 6 Boolean Operator: NOT A NOT A True False False True Unknown Unknown NOTE The fact that NOT A is evaluated as unknown when A is unknown can be confusing in queries that refer to tables with null values. It means that a NOT predicate is not necessarily evaluated as true for all rows of a column for which the same predicate without NOT is evaluated as false. In other words, the result of a query that contains NOT A is not necessarily the complement of the result of the same query that contains only A. 2 CONTAINING_Predicate A CONTAINING predicate tests whether or not the string expression specified in the second value expression is contained within the string expression specified by the first. containing-predicate = ---> value-expr -+-------+-> CONTAINING --> value-expr --> +> NOT -+ 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. exists-predicate = ---> EXISTS ( select-expr ) -------> 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. in-predicate = --> value-expr +--------+-> IN +-> value-expr ------------------+-> +-> NOT -+ +-> ( -++-> value-expr --++-> ) + |+-> select-expr --+| +---------- , <-----+ All forms of the IN predicates can be represented using other conditional operators. o value-expr IN value-expr is the same as value-expr IN (value-expr) which is the same as the basic predicate value-expr = value-expr (as long as the value expression on the right is not a host structure that expands to more than one parameter) o value-expr IN (value-expr1, value-expr2, value-expr3) is the same as the complex predicate value-expr = value-expr1 OR value-expr = value-expr2 OR value-expr = value-expr3 (in this case, any of the value expressions on the right can be a host structure that expands to more than one parameter) o value-expr IN (col-select-expr1, val-expr2, col-select-expr3) is the same as the quantified predicate value-expr = ANY (col-select-expr1) OR value-expr = val-expr2 OR value-expr = ANY (col-select-expr3) (in this case, any of the value expressions on the right can be a host structure that expands to more than one parameter) 2 IS_NULL_Predicate An IS NULL predicate tests for null values in value expressions. is-null-predicate = ----> value-expr ---> IS -+--------+-> NULL ---> +-> NOT -+ 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: like-predicate = ---> value-expr -+------+-> LIKE -----> + +> NOT + | +--------------------------------------------+ +-+-+------------------------------+-+---> | +-> ESCAPE -+ | | +-> IGNORE CASE ---------------+ | +----------------<-----------------+ pattern = ----> char-value-expr ----> escape-character = ----> char-value-expr ------------> 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 matching-predicate = ---> value-expr -+------+-> MATCHING ------> +> NOT + pattern = ----> char-value-expr ----> 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. quantified-predicate = --> value-expr --+-> = --+-+-> ALL --+-> ( col-select-expr ) --> +-> <> -+ +-> ANY --+ +-> ^= -+ +-> SOME -+ +-> != -+ +-> < --+ +-> <= -+ +-> > --+ +-> >= -+ 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: single-predicate = ---> SINGLE --> ( select-expr ) -----> 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: starting-with-predicate = --> value-expr -+------+-> STARTING WITH --> value-expr --> + NOT -+ 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. unique-predicate = ---> UNIQUE ---> ( col-select-expr ) ---> 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