Logical functions

The following functions perform logical operations on arguments, treating 0 as false and any non-zero value as true. They return 0 (false) or 1 (true) as a UInt8, or NULL in certain cases.

and

Calculates the logical conjunction (AND) of multiple input values. It returns true if all values are true, otherwise false or NULL.

The behavior of and can be affected by the short_circuit_function_evaluation setting. When enabled, subsequent arguments are only evaluated if necessary to determine the final result, potentially preventing errors from expressions that would otherwise fail.

Syntax

and(val1, val2...)

Alias: The AND operator.

Arguments

  • val1, val2, ...: Numeric values (Int, UInt, Float) or Nullable. At least two values are required.

Returns

  • 0 (false) if any argument evaluates to false.
  • NULL if no argument is false but at least one argument is NULL.
  • 1 (true) otherwise (all arguments are true).

Type: UInt8 or Nullable(UInt8).

Example

SELECT and(0, 1, -2)

Result:

┌─and(0, 1, -2)─┐
│             0 │
└───────────────┘

With NULL:

SELECT and(NULL, 1, 10, -2)

Result:

┌─and(NULL, 1, 10, -2)─┐
│                 ᴺᵁᴸᴸ │
└──────────────────────┘

or

Computes the logical disjunction (OR) of multiple input values. It returns true if at least one value is true, otherwise false or NULL.

The short_circuit_function_evaluation setting can influence how or evaluates its arguments. If enabled, arguments are processed only until a true value is found, which can optimize performance or prevent errors from unneeded evaluations.

Syntax

or(val1, val2...)

Alias: The OR operator.

Arguments

  • val1, val2, ...: Numeric values (Int, UInt, Float) or Nullable. At least two values are required.

Returns

  • 1 (true) if any argument evaluates to true.
  • 0 (false) if all arguments evaluate to false.
  • NULL if all arguments are false and at least one argument is NULL.

Type: UInt8 or Nullable(UInt8).

Example

SELECT or(1, 0, 0, 2, NULL)

Result:

┌─or(1, 0, 0, 2, NULL)─┐
│                    1 │
└──────────────────────┘

With NULL:

SELECT or(0, NULL)

Result:

┌─or(0, NULL)─┐
│        ᴺᵁᴸᴸ │
└─────────────┘

not

Inverts the logical state of a single input value. It returns true if the input is false, and false if the input is true.

Syntax

not(val)

Alias: The Negation operator.

Arguments

  • val: A numeric value (Int, UInt, Float) or Nullable.

Returns

  • 1 (true) if val evaluates to false.
  • 0 (false) if val evaluates to true.
  • NULL if val is NULL.

Type: UInt8 or Nullable(UInt8).

Example

SELECT NOT(1)

Result:

┌─not(1)─┐
│      0 │
└────────┘

xor

Computes the logical exclusive disjunction (XOR) of two or more values. For multiple inputs, it applies XOR sequentially.

Syntax

xor(val1, val2...)

Arguments

  • val1, val2, ...: Numeric values (Int, UInt, Float) or Nullable. At least two values are required.

Returns

  • For two values: 1 (true) if exactly one value is true.
  • For two values: 0 (false) if both values are true or both are false.
  • NULL if any input is NULL.

Type: UInt8 or Nullable(UInt8).

Example

SELECT xor(0, 1, 1)

Result:

┌─xor(0, 1, 1)─┐
│            0 │
└──────────────┘
Updated