BOOLEAN

Vertica provides the standard SQL type BOOLEAN, which has two states: true and false. The third state in SQL boolean logic is unknown, which is represented by the NULL value.

Syntax

BOOLEAN

Semantics

Valid literal data values for input are:

Notes

• Do not confuse the BOOLEAN data type with Boolean Operators or the Boolean-predicate.

  The Boolean predicate retrieves rows where the value of an expression is true, false, or unknown (null).

  Syntax

  expression IS [NOT] TRUE

  expression IS [NOT] FALSE

  expression IS [NOT] UNKNOWN

  Semantics

  A Boolean predicate always return true or false, never a null value, even when the operand is null. A null input is treated as the value UNKNOWN.

  Notes

  • Do not confuse the boolean-predicate with Boolean Operators or the Boolean data type, which can have only two values: true and false.

    Syntax

    [ AND | OR | NOT ]

    Semantics

    SQL uses a three-valued Boolean logic where the null value represents "unknown."

    a	b	a AND b	a OR b
    TRUE	TRUE	TRUE	TRUE
    TRUE	FALSE	FALSE	TRUE
    TRUE	NULL	NULL	TRUE
    FALSE	FALSE	FALSE	FALSE
    FALSE	NULL	FALSE	NULL
    NULL	NULL	NULL	NULL

    a	NOT a
    TRUE	FALSE
    FALSE	TRUE
    NULL	NULL

    Notes

    • The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
    • Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
  • IS UNKNOWN and IS NOT UNKNOWN are effectively the same as the NULL-predicate, except that the input expression does not have to be a single column value. To check a single column value for NULL, use the NULL-predicate.

  Syntax

  [ AND | OR | NOT ]

  Semantics

  SQL uses a three-valued Boolean logic where the null value represents "unknown."

  a	b	a AND b	a OR b
  TRUE	TRUE	TRUE	TRUE
  TRUE	FALSE	FALSE	TRUE
  TRUE	NULL	NULL	TRUE
  FALSE	FALSE	FALSE	FALSE
  FALSE	NULL	FALSE	NULL
  NULL	NULL	NULL	NULL

  a	NOT a
  TRUE	FALSE
  FALSE	TRUE
  NULL	NULL

  Notes

  • The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
  • Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
• The keywords TRUE and FALSE are preferred (and SQL-compliant).
• All other values must be enclosed in single quotes.
• Boolean values are output using the letters t and f.