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)0[m[1mpredicate = [m
[1m [m
[1mqwqwqqqqqqqqwqwq> basic-predicate qqqqqqqqqqqqqqqqwqwqq> [m
[1m x mq> [1;4mNOT[m[1m qj tq> between-predicate qqqqqqqqqqqqqqu x [m
[1m x tq> containing-predicate qqqqqqqqqqqu x [m
[1m x tq> exists-predicate qqqqqqqqqqqqqqqu x [m
[1m x tq> in-predicate qqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> is-null-predicate qqqqqqqqqqqqqqu x [m
[1m x tq> like-predicate qqqqqqqqqqqqqqqqqu x [m
[1mx[m [1mtq>[m [1mmatching-predicate[m [1mqqqqqqqqqqqqqu[m [1mx[m
[1m x tq> quantified-predicate qqqqqqqqqqqu x [m
[1m x tq> single-predicate qqqqqqqqqqqqqqqu x [m
[1mx[m [1mtq>[m [1munique-predicate qqqqqqqqqqqqqqqu[m [1mx[m
[1m x tq> starting-with-predicate qqqqqqqqu x [m
[1m x mq> (predicate) qqqqqqqqqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqqqwq [1;4mAND[m[1m <qqwqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqq[m [1;4mOR[m [1m<qqj[m
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'
1 – Basic Predicate
A basic predicate compares two values.
(B)0[m[1mbasic-predicate = [m
[1m [m
[1mqqqq> value-expr qqwq> = qqwq> value-expr qqqq> [m
[1m tq> <> qu [m
[1m tq> ^= qu [m
[1m tq> != qu [m
[1m tq> < qqu [m
[1m tq> <= qu [m
[1m tq> > qqu [m
[1m mq> >= qj [m
[1m [m
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)0[m[1mbetween-predicate = [m
[1m [m
[1mqq> value-expr qwqqqqqqqqwq> [1;4mBETWEEN[m[1m qwqqqqqqqqqqqqqqqqwqk[m
[1m mq> [1;4mNOT[m[1m qj tq> [1;4mASYMMETRIC[m[1m qqu x [m
[1m mq> [1;4mSYMMETRIC[m[1m qqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmq>[m [1mvalue-expr [1;4mAND[m[1m value-expr qqqqqqqqqqqqqqqqqqqqqqqqqqq>[m
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
3 – 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)0[m[1mcomplex-predicate = [m
[1m [m
[1mqwqwqqqqqqqqwqwqq> complex-predicate qwqqqwqqqqqqqqqqq> [m
[1m x mq> [1;4mNOT[m[1m qj mqq> predicate qqqqqqqqqj tq> [1;4mAND[m[1m qwqk [m
[1m x mq> [1;4mOR[m[1m qqj x [m
[1m mqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
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.
4 – 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.
(B)0[m[1mcontaining-predicate = [m
[1m [m
[1mqqq> value-expr qwqqqqqqqwq> [1;4mCONTAINING[m[1m qq> value-expr qq> [m
[1m m> [1;4mNOT[m[1m qj [m
[1m [m
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.
5 – EXISTS Predicate
An EXISTS predicate tests whether or not the result table
specified in a column select expression is empty.
(B)0[m[1mexists-predicate = [m
[1m [m
[1mqqq> [1;4mEXISTS[m[1m ( select-expr ) qqqqqqq> [m
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 .
6 – IN Predicate
An IN predicate compares a value with another value or a
collection of values.
(B)0[m[1min-predicate = [m
[1m [m
[1mqq> value-expr wqqqqqqqqwq> [1;4mIN[m[1m wq> value-expr qqqqqqqqqqqqqqqqqqwq> [m
[1m mq> [1;4mNOT[m[1m qj mq> ( qwwq> value-expr qqwwq> ) j [m
[1m xmq> select-expr qqjx [m
[1m mqqqqqqqqqq , <qqqqqj [m
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)
7 – IS NULL Predicate
An IS NULL predicate tests for null values in value expressions.
(B)0[m[1mis-null-predicate = [m
[1m [m
[1mqqqq> value-expr qqq> [1;4mIS[m[1m qwqqqqqqqqwq> [1;4mNULL[m[1m qqq> [m
[1m mq> [1;4mNOT[m[1m qj [m
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.
8 – 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)0[m[1mlike-predicate = [m
[1m [m
[1mqqq> value-expr qwqqqqqqwq> [1;4mLIKE[m[1m qqqqq> <pattern> k [m
[1m m> [1;4mNOT[m[1m j x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqq> [m
[1m x tq> ESCAPE <escape-character> qu x [m
[1m x mq> IGNORE CASE qqqqqqqqqqqqqqqj x [m
[1m mqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqj [m
(B)0[m[1mpattern = [m
[1m [m
[1mqqqq> char-value-expr qqqq> [m
(B)0[m[1mescape-character = [m
[1m [m
[1mqqqq> char-value-expr qqqqqqqqqqqq> [m
[1m [m
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.
9 – 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)0[m[1mmatching-predicate = [m
[1m [m
[1mqqq> value-expr qwqqqqqqwq> [1;4mMATCHING[m [1mqqqqqq>[m [1m<pattern>[m
[1m m> [1;4mNOT[m[1m j [m
[1m [m
(B)0[m[1mpattern = [m
[1m [m
[1mqqqq> char-value-expr qqqq> [m
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>
10 – 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)0[m[1mquantified-predicate = [m
[1m [m
[1mqq> value-expr qqwq> = qqwqwq> [1;4mALL[m[1m qqwq> ( col-select-expr ) qq> [m
[1m tq> <> qu tq> [1;4mANY[m[1m qqu [m
[1m tq> ^[m= [1mqu mq> [1;4mSOME[m[1m qj [m
[1m tq> != qu [m
[1mtq>[m [1m<[m [1mqqu[m
[1mtq>[m [1m<= qu[m
[1m tq> > qqu [m
[1m mq> >= qj [m
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
11 – 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)0[m[1msingle-predicate = [m
[1m [m
[1mqqq> [1;4mSINGLE[m[1m qq> ( select-expr ) qqqqq>[m
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).
12 – 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)0[m[1mstarting-with-predicate = [m
[1m [m
[1mqq> value-expr qwqqqqqqwq> [1;4mSTARTING[m[1m [1;4mWITH[m[1m qq> value-expr qq> [m
[1m m [1;4mNOT[m[1m qj [m
[1m [m
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.
13 – 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)0[m[1munique-predicate = [m
[1m [m
[1mqqq> [1;4mUNIQUE[m[1m qqq> ( col-select-expr ) qqq> [m
[1m [m
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