Syntax for SQL Condition Types

A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or unknown.

Conditions have several forms. The sections that follow show the syntax for each form of condition. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.

Compound conditions

{ (condition)
| NOT condition
| condition { AND | OR } condition
}

EQUALS_PATH condition

EQUALS_PATH
    (column, path_string [, correlation_integer ])

EXISTS condition

EXISTS (subquery)

Floating-point conditions

expr IS [ NOT ] { NAN | INFINITE }

Group comparison condition

{ expr
     { = | != | ^= | <> | > | < | >= | <= }
     { ANY | SOME | ALL }
     ({ expression_list | subquery })
| expr
  [, expr ]...
  { = | != | ^= | <> }
  { ANY | SOME | ALL }
  ({ expression_list
     [, expression_list ]...
   | subquery
   }
  )
}

where !=, ^=, and <> test for inequality

IN conditions

{ expr [ NOT ] IN ({ expression_list | subquery })
| ( expr [, expr ]... )
    [ NOT ] IN ({ expression_list [, expression_list ]...
                | subquery
                }
               )
  )
}

IS A SET conditions

nested_table IS [ NOT ] A SET

IS ANY condition

[ dimension_column IS ] ANY

IS EMPTY conditions

nested_table IS [ NOT ] EMPTY

IS OF TYPE conditions

expr IS [ NOT ] OF [ TYPE ]
   ([ ONLY ] [ schema. ] type
      [, [ ONLY ] [ schema. ] type ]...
   )

IS PRESENT condition

cell_reference IS PRESENT

LIKE condition

char1 [ NOT ] { LIKE | LIKEC | LIKE2 | LIKE4 }
  char2 [ ESCAPE esc_char ]

Logical conditions

{ NOT | AND | OR }

MEMBER condition

expr [ NOT ] MEMBER [ OF ] nested_table

NULL conditions

expr IS [ NOT ] NULL

Range conditions

expr [ NOT ] BETWEEN expr AND expr

REGEXP_LIKE condition

REGEXP_LIKE(source_char, pattern
            [, match_param ]
           )

Simple comparison condition

{ expr 
  { = | != | ^= | <> | > | < | >= | <= }
  expr
| (expr [, expr ]...)
  { = | != | ^= | <> }
  (subquery)
}

where !=, ^=, and <> test for inequality

SUBMULTISET conditions

nested_table1
[ NOT ] SUBMULTISET [ OF ]
nested_table2

UNDER_PATH condition

UNDER_PATH (column [, levels ], path_string
             [, correlation_integer ]
           )