Logical functions

Logical functions perform boolean operations on one or more values. They are crucial for:

• Conditional processing
• Filtering data
• Implementing complex logical conditions in queries

Key features:

1. Support for various data types:

   • Integers
   • Floating-point numbers
   • Nullable values

2. Flexible syntax options:

   • Functional syntax (e.g., and(a, b))
   • Operator syntax (e.g., a AND b)

These functions enable you to create readable and expressive logical expressions in your queries, enhancing both clarity and functionality.

​

ClickHouse function reference

Logical functions perform boolean operations on one or more values.

​

and

Calculates the logical conjunction of two or more values.

Syntax:

and(val1, val2, ...)

Alternatively, you can use the AND operator:

val1 AND val2 AND ...

Alias:

• The AND operator

Arguments:

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

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

Return type: UInt8 or Nullable(UInt8)

Example:

In this example, should_celebrate is true only when it’s Taco Tuesday, there’s guacamole, and more than 3 tacos are available.

SELECT
  and(
    isTacoTuesday,
    hasGuacamole,
    numberOfTacos > 3
  ) AS should_celebrate
FROM
  taco_party_schedule;

Result:

| should_celebrate |
|------------------|
| 1                |
| 0                |
| NULL             |

​

or

Calculates the logical disjunction of two or more values.

Syntax:

or(val1, val2, ...)

Alias:

• The OR operator

Arguments:

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

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

Return type: UInt8 or Nullable(UInt8).

Example:

SELECT
  or(
    hasCarnitas,
    hasChicken,
    hasBeef
  ) AS hasProteinOption
FROM
  taco_ingredients;

Result:

| hasProteinOption |
|------------------|
| 1                |

In this example, hasProteinOption is true if at least one protein option (carnitas, chicken, or beef) is available.

​

not

Calculates the logical negation of a value.

Syntax:

not(val)

Alias:

• The Negation operator !

Arguments:

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

Returns:

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

Return type: UInt8 or Nullable(UInt8).

Example:

SELECT
  taco_type,
  not(isSpicy) AS isMild
FROM
  taco_menu;

Result:

| taco_type | isMild |
|-----------|--------|
| Jalapeño  | 0      |
| Veggie    | 1      |

In this example, isMild is the logical negation of isSpicy.

​

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, and so on.

Syntax:

xor(val1, val2, ...)

Arguments:

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

Returns:

• For two values:
  • 1 if one of the values evaluates to false and the other does not
  • 0 if both values evaluate to false or both true
• NULL if at least one of the inputs is NULL

Return type: UInt8 or Nullable(UInt8).

Example:

SELECT
  xor(hasSalsa, hasGuacamole, hasSourCream) AS hasUniqueTopping
FROM
  taco_toppings;

Result:

| hasUniqueTopping |
|------------------|
| 1                |

In this example, hasUniqueTopping is true if exactly one of salsa, guacamole, or sour cream is present on the taco.

​

not

Calculates the logical negation of a value.

Syntax:

not(val)

Alternatively, you can use the negation operator:

NOT val

Arguments:

• val (Int, UInt, Float, or Nullable): The value to negate.

Returns:

• 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) AS not_true,
    not(0) AS not_false,
    not(NULL) AS not_null,
    not(isHot('jalapeño')) AS not_spicy
FROM taco_ingredients;

Result:

| not_true | not_false | not_null | not_spicy |
|----------|-----------|----------|-----------|
| 0        | 1         | NULL     | 0         |

In this example:

• not(1) returns 0 because 1 is considered true
• not(0) returns 1 because 0 is considered false
• not(NULL) returns NULL
• not(isHot('jalapeño')) returns 0, assuming jalapeños are considered hot