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.
- 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.
- 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.
- 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
.
- 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.
- 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.
- 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