Comparison functions

The comparison functions return 0 or 1 as UInt8.

The following types can be compared:

• Numbers
• Strings and fixed strings
• Dates
• Dates with times

Only values within the same group can be compared (e.g., UInt16 and UInt64) but not across groups (e.g., UInt16 and DateTime).

Strings are compared byte-by-byte. This may lead to unexpected results if one of the strings contains UTF-8 encoded multi-byte characters.

A string “S1” that has another string “S2” as a prefix is considered longer than “S2”.

​

ClickHouse function reference

​

equals

Compares two values for equality.

Syntax:

equals(a, b)

Alternatively, you can use the = or == operators:

a = b
a == b

Arguments:

• a (any): The first value to compare.
• b (any): The second value to compare.

Returns:

• 1 if the values are equal, 0 otherwise. [UInt8]

Example:

SELECT
  taco_id,
  taco_name,
  price,
  equals(price, 5.99) AS is_standard_price
FROM
  taco_menu;

Result:

| taco_id | taco_name     | price | is_standard_price |
|---------|---------------|-------|-------------------|
| 1       | Classic Beef  | 5.99  | 1                 |
| 2       | Veggie Delight| 4.99  | 0                 |
| 3       | Spicy Chicken | 5.99  | 1                 |

In this example, equals checks if each taco’s price is equal to the standard price of 5.99. The result is 1 for tacos with the standard price and 0 for others.

​

notEquals

Compares two values for inequality.

Syntax:

notEquals(a, b)

Alternatively, you can use the != or <> operators:

a != b
a <> b

Arguments:

• a (any): The first value to compare.
• b (any): The second value to compare.

Returns:

• 1 if the values are not equal, 0 if they are equal. [UInt8]

Example:

SELECT
  taco_id,
  taco_name,
  spice_level,
  notEquals(spice_level, 'Medium') AS not_medium_spice
FROM
  taco_menu;

Result:

| taco_id | taco_name     | spice_level | not_medium_spice  |
|---------|---------------|-------------|-------------------|
| 1       | Classic Beef  | Mild        | 1                 |
| 2       | Spicy Chicken | Hot         | 1                 |
| 3       | Veggie Delight| Medium      | 0                 |

In this example, notEquals checks if each taco’s spice level is different from ‘Medium’. The result is 1 for tacos with spice levels other than ‘Medium’ and 0 for Medium spice level.

​

less

Compares if the first value is less than the second value.

Syntax:

less(a, b)

Alternatively, you can use the < operator:

a < b

Arguments:

• a (any): The first value to compare.
• b (any): The second value to compare.

Returns:

• 1 if a is less than b, 0 otherwise. [UInt8]

Example:

SELECT
  taco_id,
  taco_name,
  price,
  less(price, 5.00) AS is_budget_friendly
FROM
  taco_menu;

Result:

| taco_id | taco_name     | price | is_budget_friendly  |
|---------|---------------|-------|---------------------|
| 1       | Classic Beef  | 5.99  | 0                   |
| 2       | Veggie Delight| 4.50  | 1                   |
| 3       | Spicy Chicken | 5.25  | 0                   |

In this example, less checks if each taco’s price is less than 5.00. The result is 1 for tacos priced under 5.00 and 0 for those 5.00 or more.

​

greater

Compares if the first value is greater than the second value.

Syntax:

greater(a, b)

Alternatively, you can use the > operator:

a > b

Arguments:

• a (any): The first value to compare.
• b (any): The second value to compare.

Returns:

• 1 if a is greater than b, 0 otherwise. [UInt8]

Example:

SELECT
  taco_id,
  taco_name,
  calories,
  greater(calories, 300) AS is_high_calorie
FROM
  taco_menu;

Result:

| taco_id | taco_name     | calories | is_high_calorie  |
|---------|---------------|----------|------------------|
| 1       | Classic Beef  | 350      | 1                |
| 2       | Veggie Delight| 250      | 0                |
| 3       | Spicy Chicken | 320      | 1                |

In this example, greater checks if each taco’s calorie count is greater than 300. The result is 1 for tacos with more than 300 calories and 0 for those with 300 calories or less.

​

lessOrEquals

Compares if the first value is less than or equal to the second value.

Syntax:

lessOrEquals(a, b)

Alternatively, you can use the <= operator:

a <= b

Arguments:

• a (any): The first value to compare.
• b (any): The second value to compare.

Returns:

• 1 if a is less than or equal to b, 0 otherwise. [UInt8]

Example:

SELECT
  taco_id,
  taco_name,
  prep_time,
  lessOrEquals(prep_time, 5) AS is_quick_prep
FROM
  taco_menu;

Result:

| taco_id | taco_name     | prep_time | is_quick_prep  |
|---------|---------------|-----------|----------------|
| 1       | Classic Beef  | 5         | 1              |
| 2       | Veggie Delight| 4         | 1              |
| 3       | Spicy Chicken | 6         | 0              |

In this example, lessOrEquals checks if each taco’s preparation time is less than or equal to 5 minutes. The result is 1 for tacos with prep time of 5 minutes or less and 0 for those taking more than 5 minutes.

​

greaterOrEquals

Compares if the first value is greater than or equal to the second value.

Syntax:

greaterOrEquals(a, b)

Alternatively, you can use the >= operator:

a >= b

Arguments:

• a (any): The first value to compare.
• b (any): The second value to compare.

Returns:

• 1 if a is greater than or equal to b, 0 otherwise. [UInt8]

Example:

SELECT
  taco_id,
  taco_name,
  rating,
  greaterOrEquals(rating, 4.5) AS is_highly_rated
FROM
  taco_reviews;

Result:

| taco_id | taco_name     | rating | is_highly_rated  |
|---------|---------------|--------|------------------|
| 1       | Classic Beef  | 4.7    | 1                |
| 2       | Veggie Delight| 4.3    | 0                |
| 3       | Spicy Chicken | 4.5    | 1                |

In this example, greaterOrEquals checks if each taco’s rating is greater than or equal to 4.5. The result is 1 for tacos with ratings of 4.5 or higher and 0 for those with ratings below 4.5.