[NOT ]
|
predicate | |||||||
(search-condition) | ||||||||
search-condition |
AND
|
search-condition | ||||||
OR
|
This document covers the following topics:
A search-condition
can consist of a simple
predicate
or multiple
search-conditions
. Multiple
search-conditions
are combined with the
Boolean operators AND
, OR
and NOT
, and can contain
parentheses if required to indicate a desired order of evaluation.
DEFINE DATA LOCAL 01 NAME (A20) 01 AGE (I2) END-DEFINE ... SELECT * INTO NAME, AGE FROM SQL-PERSONNEL WHERE AGE = 32 AND NAME > 'K' END-SELECT ...
scalar-expression comparison | |||||||||||||||
scalar-expression [NOT ]
BETWEEN
scalar-expression
AND
scalar-expression
|
|||||||||||||||
scalar-expression
IS [NOT ] DISTINCT FROM
scalar-expression
|
|||||||||||||||
column-reference [NOT ]
LIKE
|
[ESCAPE
atom] |
||||||||||||||
column-referenceIS [NOT ] NULL
|
|||||||||||||||
scalar-expression [NOT ]
IN
|
subquery | ||||||||||||||
,... |
|||||||||||||||
scalar-expression comparison |
|
subquery | |||||||||||||
EXISTS
subquery |
|||||||||||||||
XMLEXISTS
(xquery-expression-constant{BY
REF |PASSING
xquery-argument,...})
|
A predicate
specifies a condition that can be
"true", "false" or "unknown".
In a search-condition
, a
predicate
can consist of a simple or complex
comparison operation or other kinds of conditions.
SELECT NAME, AGE INTO VIEW PERS FROM SQL-PERSONNEL WHERE AGE BETWEEN 20 AND 30 0R AGE IN ( 32, 34, 36 ) AND NAME LIKE '%er' ...
Note:
The percent sign (%) may conflict with Natural
terminal commands. If so, you must define a terminal command control
character different from %
; see Changing the Terminal Command
Control Character in the Terminal Commands
documentation.
The individual predicates are explained in the following topics (for further information on predicates, please refer to the relevant literature). According to the syntax above, they are called as follows:
scalar-expression | comparison | scalar-expression | ||||
row-value-expression | comparison | row-value-expression |
A comparison predicate compares two values or a set of values with another set of values.
In the syntax diagram above, comparison
can be one of the
following operators:
=
|
equal to |
<
|
less than |
>
|
greater than |
<=
|
less than or equal to |
>=
|
greater than or equal to |
<>
|
not equal to |
See information on scalar-expression
.
(select-expression) |
A subquery
is a
select-expression
that is nested inside
another such expression.
DEFINE DATA LOCAL 1 #NAME (A20) 1 #PERSNR (I4) END-DEFINE ... SELECT NAME, PERSNR INTO #NAME, #PERSNR FROM SQL-PERSONNEL WHERE PERSNR IN ( SELECT PERSNR FROM SQL-AUTOMOBILES WHERE COLOR = 'black' ) ... END-SELECT
For further information, see Select Expressions.
scalar-expression [NOT ] BETWEEN
scalar-expression
AND
scalar-expression
|
A BETWEEN
predicate compares a value with a range of values.
See information on scalar-expression
.
scalar-expression
IS [NOT ] DISTINCT FROM
scalar-expression
|
||
row-value-expression
IS [NOT ] DISTINCT FROM
row-value-expression
|
A DISTINCT
predicate compares a value with another value or a set of
values with another set of values.
column-reference [NOT ]
LIKE
|
atom | [ESCAPE
atom]
|
||
special-register |
A LIKE
predicate searches for strings that have a certain pattern.
See information on column-reference
, atom
and special-register
.
column-reference | IS [NOT ] NULL |
||
ISNULL |
|||
NOTNULL |
A NULL
predicate tests for null values.
If the compiler option DB2ARRY
is set to ON
, it is possible
to specify an Natural array or an fixed index range of an array as atom. The Natural
SQL compiler will then decompose the array or fixed index range into a list of scalar
host variables.
See information on column-reference
.
scalar-expression
[NOT ] IN
|
subquery | ||||
row-value-expression | |||||
row-value-expression [NOT ] IN
subquery |
An IN
predicate compares a value or a set of values with a collection of
values.
See information on scalar-expression
, atom
and special-register
.
See information on subquery
.
scalar-expression | comparison |
|
subquery | ||||
row-value-expression | = |
|
subquery | ||||
row-value-expression | <> |
ALL |
subquery |
A quantified predicate compares a value or a set of values with a collection of values.
See information on scalar-expression
, comparison
and subquery
.
EXISTS
subquery
|
An EXISTS
predicate tests for the existence of certain rows.
The EXISTS
predicate evaluates to true only if the result of evaluating
the subquery
is not empty; that is, if there
exists at least one record (row) in the FROM
table of the
subquery
satisfying the search condition of
the WHERE
clause of this
subquery
.
Example of EXISTS
:
DEFINE DATA LOCAL 1 #NAME (A20) END-DEFINE ... SELECT NAME INTO #NAME FROM SQL-PERSONNEL WHERE EXISTS ( SELECT * FROM SQL-EMPLOYEES WHERE PERSNR > 1000 AND NAME < 'L' ) ... END-SELECT ...
See information on subquery
.
XMLEXISTS
( xquery-expression-constant
|
BY REF |
) |
||
PASSING
xquery-argument,...
|
xquery-context-item-expression | ||
xquery-context-item-expression
AS
identifier |
The XMLEXISTS
predicate belongs to the Natural SQL Extended Set.
The XMLEXISTS
predicate tests whether an XPATH
expression
returns a sequence of one or more items. For further information, see the IBM
Db2 XML Guide.