SQL Search Condition

Constructs a conditional expression which evaluates to logical true or logical false.

Syntax

search-condition

Syntax Elements

search-condition =
boolean-term {OR search-condition}

boolean-term =
boolean-factor {AND boolean-term}

boolean-factor =
{NOT} boolean-primary

boolean-primary =
predicate || (search-condition)

predicate =
     between-predicate
||   comparison-predicate
||   exists-predicate
||   in-predicate
||   like-predicate
||   null-predicate
||   quantified-predicate

Predicate Syntax

between-predicate =
expression {NOT} BETWEEN expression AND expression

expression
Refer to the topic SQL Expression.

comparison-predicate =
expression comparison-operator {expression || (sub-query)}

comparison-operator =
< || > || <= || >= || = || <>

sub-query =
SELECT statement (without an ORDER BY clause)
Refer to the description of the SELECT statement for full definition.

exists-predicate =
EXISTS ( sub-query )

in-predicate =
expression {NOT} IN [(value {,value}?) || (sub-query)]

value =
literal || USER || dynamic-parameter

literal =
Refer to the topic SQL Expression.

dynamic-parameter =
?

like-predicate =
expression {NOT} LIKE pattern-value

pattern-value =
character-string-literal || dynamic-parameter || USER

character-string-literal =
Refer to the topic SQL Expression.

Note

In a pattern-value, the percent character (%) matches zero or more of any character; the underscore character (_) matches one character. To match either of these special characters explicitly, remove its special status with a preceding '\'.

quantified-predicate =
expression comparison-operator [ALL || ANY] (sub-query)

null-predicate =
column-name IS {NOT} NULL

column-name =
{[table-name || correlation-name].}column-identifier

table-name =
{owner-name.}table-identifier

owner-name =
The SQL user-id of the creator of the table.

table-identifier =
Any user-defined name. See Comments.

correlation-name =
Any user-defined name. See Comments.

column-identifier =
Any user-defined name. See Comments.

Comments

User defined names can be any name, except a Reserved Word, starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

Examples

Search Condition

(DEPTNO = 90020) OR (SALARY > 3000)

Searches for persons whose department number is 90020 or their salary is greater than 3000.

X <= Y AND Y <= Z
Y BETWEEN X AND Z

Both these conditions search for rows with Y between X and Z.

LIKE Condition

PART_NAME LIKE 'C%'

Searches for rows where the part name begins with letter C.