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
The not function treats any non-zero value as true and zero as false. This applies to all numeric types, including floating-point numbers.