1. Home
  2. Knowledge Base
  3. ChistaDATA
  4. Compatibility of the CASE expression in ClickHouse
  1. Home
  2. Knowledge Base
  3. ClickHouse
  4. Compatibility of the CASE expression in ClickHouse
  1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Compatibility of the CASE expression in ClickHouse

Compatibility of the CASE expression in ClickHouse

ClickHouse, an open-source columnar database management system, offers several ways to write case expressions. Case expressions perform conditional branching and return different results based on specified conditions. This blog post will explore five ways of writing case expressions in ClickHouse.

 

  1. Simple Case Expression:
clickhouse-dbnode :) SELECT CASE WHEN number = 0 THEN 'Customers'
WHEN number = 1 THEN 'Products'
WHEN number = 2 THEN 'Orders'
WHEN number = 3 THEN 'Reviews' ELSE '-' END AS ecommerce FROM numbers(6)

SELECT multiIf(number = 0, 'Customers', number = 1, 'Products', number = 2, 'Orders', number = 3, 'Reviews', '-') AS ecommerce
FROM numbers(6)

Query id: 3343d652-87ed-4920-8220-31769a8531a6

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ -         │
│ -         │
└───────────┘

6 rows in set. Elapsed: 0.002 sec.

In this approach, the case expression is written using the CASE WHEN Syntax. It checks each condition individually and returns the corresponding result when a condition evaluates to true. If none of the conditions match, the ELSE part is executed.

  1. Searched Case Expression:
clickhouse-dbnode :) SELECT CASE number WHEN 0 THEN 'Customers'
WHEN 1 THEN 'Products'
WHEN 2 THEN 'Orders'
WHEN 3 THEN 'Reviews'
ELSE '-' END
AS ecommerce FROM numbers(6)

SELECT caseWithExpression(number, 0, 'Customers', 1, 'Products', 2, 'Orders', 3, 'Reviews', '-') AS ecommerce
FROM numbers(6)

Query id: d0962699-d26a-4498-a335-a76cde8eaba4

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ -         │
│ -         │
└───────────┘

6 rows in set. Elapsed: 0.002 sec.

The searched case expression is similar to the simple case expression, but it compares the expression (number in this case) directly with each value, and the conditions are evaluated based on equality.

  1. Simple Case Expression without ELSE:
clickhouse-dbnode :) SELECT CASE WHEN number = 0 THEN 'Customers'
WHEN number = 1 THEN 'Products'
WHEN number = 2 THEN 'Orders'
WHEN number = 3 THEN 'Reviews'
END
AS ecommerce FROM numbers(6)

SELECT multiIf(number = 0, 'Customers', number = 1, 'Products', number = 2, 'Orders', number = 3, 'Reviews', NULL) AS ecommerce
FROM numbers(6)

Query id: 7062117a-3e06-4c1a-9a14-d724286ffc78

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
└───────────┘

6 rows in set. Elapsed: 0.002 sec.

This variation of the simple case expression omits the ELSE part. When no conditions are satisfied, it returns NULL instead of a default value. The output, ᴺᵁᴸᴸ represents NULL.

  1. Searched Case Expression without ELSE:
clickhouse-dbnode :) SELECT CASE number WHEN 0 THEN 'Customers'
WHEN 1 THEN 'Products'
WHEN 2 THEN 'Orders'
WHEN 3 THEN 'Reviews'
END
AS ecommerce FROM numbers(6)

SELECT caseWithExpression(number, 0, 'Customers', 1, 'Products', 2, 'Orders', 3, 'Reviews', NULL) AS ecommerce
FROM numbers(6)

Query id: 581c3eec-0066-4d64-bea5-5abd0dcea0ad

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
└───────────┘

6 rows in set. Elapsed: 0.002 sec.

Similar to the previous case expression, this version omits the ELSE part. It also returns NULL when none of the conditions are satisfied.

  1. multiIf Function:
clickhouse-dbnode :) SELECT multiIf(number = 0, 'Customers', number = 1, 'Products', number = 2, 'Orders', number = 3, 'Reviews', '-') AS ecommerce
FROM numbers(6)

SELECT multiIf(number = 0, 'Customers', number = 1, 'Products', number = 2, 'Orders', number = 3, 'Reviews', '-') AS ecommerce
FROM numbers(6)

Query id: f359afaf-73e3-4e10-890b-c26c697a731a

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ -         │
│ -         │
└───────────┘

6 rows in set. Elapsed: 0.002 sec

The multiIf The function allows you to specify multiple conditions and corresponding results concisely. It evaluates each condition in order and returns the corresponding result when the condition evaluates to true. If none of the conditions match, the last argument ('-' in this case) is returned.

  1. Transform Function:
clickhouse-dbnode :) SELECT transform(number, [0, 1, 2, 3], ['Customers', 'Products', 'Orders', 'Reviews'], '-') AS ecommerce FROM numbers(6)

SELECT transform(number, [0, 1, 2, 3], ['Customers', 'Products', 'Orders', 'Reviews'], '-') AS ecommerce
FROM numbers(6)

Query id: 9c8f4c37-f0e1-4118-9308-f90006924841

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ -         │
│ -         │
└───────────┘

6 rows in set. Elapsed: 0.002 sec.

clickhouse-dbnode :)

The transform function is another way to achieve similar functionality. It takes the input expression (number) and compares it with the keys in the provided array ([1, 2]). When a match is found, it returns the corresponding value from the values array (['Hello', 'World']). If no match is found, it returns the last argument ('-').

 

Conclusion:

ClickHouse offers several ways to write case expressions based on different syntax preferences and requirements. Whether you prefer the traditional CASE WHEN syntax or the compact multiIf and transform functions, you can choose the approach that suits your needs. Understanding these methods lets you write flexible and concise conditional expressions in ClickHouse.

References : https://presentations.clickhouse.com/release_23.5/#4

Was this article helpful?

Related Articles

CHISTADATA IS COMMITTED TO OPEN SOURCE SOFTWARE AND BUILDING HIGH PERFORMANCE COLUMNSTORES

In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.