Logical Functions

Below 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

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

Result:

With NULL:

Result:

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

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

Result:

With NULL:

Result:

not

Calculates the logical negation of a value.

Syntax

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

Result:

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

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 does not,
• 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

Result: