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.

BETWEEN condition

expr1 [ NOT ] BETWEEN expr2 AND expr3

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 conditions

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

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

IN condition

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

IS A SET condition

nested_table IS [ NOT ] A SET

IS ANY condition

[ dimension_column IS ] ANY

IS EMPTY condition

nested_table IS [ NOT ] EMPTY

IS JSON condition

expr IS [ NOT ] JSON [ FORMAT JSON ] [ STRICT | LAX ]
[ { WITH | WITHOUT } UNIQUE KEYS ]

IS OF type condition

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

IS PRESENT condition

cell_reference IS PRESENT

JSON_EXISTS condition

JSON_EXISTS( expr [ FORMAT JSON ], JSON_path_expression
             [ JSON_exists_on_error_clause ] )

JSON_TEXTCONTAINS condition

JSON_TEXTCONTAINS( column, JSON_path_expression, string )

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

REGEXP_LIKE condition

REGEXP_LIKE(source_char, pattern
            [, match_param ]
           )

Simple comparison conditions

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

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

SUBMULTISET condition

nested_table1
[ NOT ] SUBMULTISET [ OF ]
nested_table2

UNDER_PATH condition

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