Logical functions

The functions below 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

Introduced in: v1.1

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). Zero as an argument is considered false, non-zero values are considered true.

Syntax

and(val1, val2[, ...])

Arguments

• val1, val2[, ...] — List of at least two values. Nullable((U)Int*) or Nullable(Float*)

Returned value

Returns:

• 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 Nullable(UInt8)

Examples

Basic usage

SELECT and(0, 1, -2);

0

With NULL

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

ᴺᵁᴸᴸ

not

Introduced in: v1.1

Calculates the logical negation of a value. Zero as an argument is considered false, non-zero values are considered true.

Syntax

not(val)

Arguments

• val — The value. (U)Int* or Float*

Returned value

Returns:

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

Examples

Basic usage

SELECT NOT(1);

0

or

Introduced in: v1.1

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). Zero as an argument is considered false, non-zero values are considered true.

Syntax

or(val1, val2[, ...])

Arguments

• val1, val2[, ...] — List of at least two values. Nullable((U)Int*) or Nullable(Float*)

Returned value

Returns:

• 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 Nullable(UInt8)

Examples

Basic usage

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

1

With NULL

SELECT or(0, NULL);

ᴺᵁᴸᴸ

xor

Introduced in: v1.1

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. Zero as an argument is considered false, non-zero values are considered true.

Syntax

xor(val1, val2[, ...])

Arguments

• val1, val2[, ...] — List of at least two values. Nullable((U)Int*) or Nullable(Float*)

Returned value

Returns:

• 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. Nullable(UInt8)

Examples

Basic usage

SELECT xor(0, 1, 1);

0