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.

SQLQR512BETWEEN condition

expr1 [ NOT ] BETWEEN expr2 AND expr3

SQLQR513Compound conditions

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

SQLQR514EQUALS_PATH condition

EQUALS_PATH
    (column, path_string [, correlation_integer ])

SQLQR515EXISTS condition

EXISTS (subquery)

SQLQR516Floating-point conditions

expr IS [ NOT ] { NAN | INFINITE }

SQLQR517Group comparison conditions

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

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

SQLQR518IN condition

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

SQLQR519IS A SET condition

nested_table IS [ NOT ] A SET

SQLQR520IS ANY condition

[ dimension_column IS ] ANY

SQLQR521IS EMPTY condition

nested_table IS [ NOT ] EMPTY

SQLQR522IS OF type condition

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

SQLQR523IS PRESENT condition

cell_reference IS PRESENT

SQLQR524LIKE condition

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

SQLQR525Logical conditions

{ NOT | AND | OR }

SQLQR526MEMBER condition

expr [ NOT ] MEMBER [ OF ] nested_table

SQLQR527Null conditions

expr IS [ NOT ] NULL

SQLQR528REGEXP_LIKE condition

REGEXP_LIKE(source_char, pattern
            [, match_param ]
           )

SQLQR529Simple comparison conditions

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

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

SQLQR530SUBMULTISET condition

nested_table1
[ NOT ] SUBMULTISET [ OF ]
nested_table2

SQLQR531UNDER_PATH condition

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