Logical Functions

Performs logical operations on arguments of any numeric types, but returns a UInt8 number equal to 0, 1 or `NULL` in some cases.

Zero as an argument is considered `false`, while any non-zero value is considered `true`.

andβ

Calculates the result of the logical conjunction between two or more values. Corresponds to Logical AND Operator.

Syntax

``and(val1, val2...)``

You can use the short_circuit_function_evaluation setting to calculate the `and` function according to a short scheme. If this setting is enabled, `vali` is evaluated only on rows where `(val1 AND val2 AND ... AND val{i-1})` is true. For example, an exception about division by zero is not thrown when executing the query `SELECT and(number = 2, intDiv(1, number)) FROM numbers(10)`.

Arguments

Returned value

• `0`, if there is at least one zero value argument.
• `NULL`, if there are no zero values arguments and there is at least one `NULL` argument.
• `1`, otherwise.

Type: UInt8 or Nullable(UInt8).

Example

Query:

``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 result of the logical disjunction between two or more values. Corresponds to Logical OR Operator.

Syntax

``or(val1, val2...)``

You can use the short_circuit_function_evaluation setting to calculate the `or` function according to a short scheme. If this setting is enabled, `vali` is evaluated only on rows where `((NOT val1) AND (NOT val2) AND ... AND (NOT val{i-1}))` is true. For example, an exception about division by zero is not thrown when executing the query `SELECT or(number = 0, intDiv(1, number) != 0) FROM numbers(10)`.

Arguments

Returned value

• `1`, if there is at least one non-zero value.
• `0`, if there are only zero values.
• `NULL`, if there are only zero values and `NULL`.

Type: UInt8 or Nullable(UInt8).

Example

Query:

``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 result of the logical negation of the value. Corresponds to Logical Negation Operator.

Syntax

``not(val);``

Arguments

Returned value

• `1`, if the `val` is `0`.
• `0`, if the `val` is a non-zero value.
• `NULL`, if the `val` is a `NULL` value.

Type: UInt8 or Nullable(UInt8).

Example

Query:

``SELECT NOT(1);``

Result:

``ββnot(1)βββ      0 βββββββββββ``

xorβ

Calculates the result of the logical exclusive disjunction between two or more values. For more than two values the function works as if it calculates `XOR` of the first two values and then uses the result with the next value to calculate `XOR` and so on.

Syntax

``xor(val1, val2...)``

Arguments

Returned value

• `1`, for two values: if one of the values is zero and other is not.
• `0`, for two values: if both values are zero or non-zero at the same time.
• `NULL`, if there is at least one `NULL` value.

Type: UInt8 or Nullable(UInt8).

Example

Query:

``SELECT xor(0, 1, 1);``

Result:

``ββxor(0, 1, 1)βββ            0 βββββββββββββββββ``