Logical functions

The following functions perform logical operations on arguments of arbitrary numeric types. They return either 0 or 1 as UInt8 or in some cases NULL.

Zero as an argument is considered false, non-zero values are considered true.

and

Calculates the logical conjunction of two or more values.

Setting short_circuit_function_evaluation controls whether short-circuit evaluation is used. If enabled, val_i is evaluated only if (val_1 AND val_2 AND ... AND val_{i-1}) is true. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query SELECT and(number = 2, intDiv(1, number)) FROM numbers(5).

Syntax

and(val1, val2...)

Alias: The AND operator.

Arguments

  • val1, val2, ...: List of at least two values. Int, UInt, Float or Nullable.

Returned value

  • 0, if at least one argument evaluates to false,
  • NULL, if no argument evaluates to false and at least one argument is NULL,
  • 1, otherwise.

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

Calculates the logical disjunction of two or more values.

Setting short_circuit_function_evaluation controls whether short-circuit evaluation is used. If enabled, val_i is evaluated only if ((NOT val_1) AND (NOT val_2) AND ... AND (NOT val_{i-1})) is true. For example, with short-circuit evaluation, no division-by-zero exception is thrown when executing the query SELECT or(number = 0, intDiv(1, number) != 0) FROM numbers(5).

Syntax

or(val1, val2...)

Alias: The OR operator.

Arguments

  • val1, val2, ...: List of at least two values. Int, UInt, Float or Nullable.

Returned value

  • 1, if at least one argument evaluates to true,
  • 0, if all arguments evaluate to false,
  • NULL, if all arguments evaluate to 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

Calculates the logical negation of a value.

Syntax

not(val)

Alias: The Negation operator.

Arguments

  • val: The value. Int, UInt, Float or Nullable.

Returned value

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

Type: UInt8 or Nullable(UInt8).

Example

SELECT NOT(1)

Result:

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

xor

Calculates the logical exclusive disjunction of two or more values. For more than two input values, the function first xor-s the first two values, then xor-s the result with the third value etc.

Syntax

xor(val1, val2...)

Arguments

  • val1, val2, ...: List of at least two values. Int, UInt, Float or Nullable.

Returned value

  • 1, for two values: if one of the values evaluates to false and other doesn't,
  • 0, for two values: if both values evaluate to false or to both true,
  • NULL, if at least one of the inputs is NULL

Type: UInt8 or Nullable(UInt8).

Example

SELECT xor(0, 1, 1)

Result:

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