How to Handle NULLs in ClickHouse

How do I handle NULL values in ClickHouse?

ClickHouse offers nullable data types and functions like ifNull, nullIf, coalesce, and isNull to replace, detect, or avoid NULL values during queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What functions handle NULLs in ClickHouse?

Use ifNull(expr, alt) to replace NULL with a fallback, nullIf(expr, target) to return NULL when two expressions match, coalesce(v1, v2, …) to pick the first non-NULL, and isNull(expr) to test for NULL. All accept any compatible type, including Nullable columns.

When should I use ifNull()?

ifNull() shines when you always want a single fallback value. Example: default missing total_amount in Orders to 0 during revenue aggregation to avoid NULL-propagated sums.

How does coalesce() differ from ifNull()?

coalesce() scans multiple alternatives, returning the first non-NULL. It is ideal when you have cascading defaults, such as preferring Orders.total_amount, then OrderItems.quantity*Products.price, then 0.

How do I detect NULLs efficiently?

Use isNull() in WHERE filters: WHERE isNull(email). ClickHouse optimizes this with low-level bitmap indexes, so there is no performance penalty.

Can I avoid NULLs at insert time?

Define columns as Nullable(Type) only when NULLs are expected. For mandatory fields like Orders.order_date, keep them non-nullable. Supply a default value with DEFAULT in table definition to prevent accidental NULL inserts.

Best practices for NULL handling

Prefer explicit defaults over broad NULL usage, convert columns to Nullable only when business logic requires it, and always coalesce or ifNull before arithmetic to keep aggregates correct.

Example: cleaning customer emails

Replace NULL or empty strings in Customers.email using coalesce(NULLIF(email,''), 'no-email@example.com') so downstream tools receive a valid placeholder.

Performance tips

Functions like assumeNotNull(expr) temporarily treat a Nullable column as non-nullable, skipping NULL checks in tight loops. Combine with arrayFilter or sumIf for micro-optimizations.

Why How to Handle NULLs in ClickHouse is important

How to Handle NULLs in ClickHouse Example Usage


-- Fill NULL totals and aggregate revenue per month
SELECT toStartOfMonth(order_date)   AS month,
       sum(ifNull(total_amount, 0)) AS revenue
FROM Orders
GROUP BY month
ORDER BY month;

How to Handle NULLs in ClickHouse Syntax


-- Replace NULL totals with zero
SELECT ifNull(total_amount, 0) AS safe_total
FROM Orders;

-- Return NULL if customer has no orders on a given date
SELECT nullIf(count(*), 0) AS orders_made
FROM Orders
WHERE customer_id = 42 AND order_date = today();

-- Pick the first non-NULL price source
SELECT coalesce(Products.price, OrderItems.quantity * 10, 0) AS final_price
FROM Products
LEFT JOIN OrderItems USING(product_id);

-- Detect missing emails
SELECT id, name
FROM Customers
WHERE isNull(email);

-- Speed up by assuming non-NULL after filter
SELECT sum(assumeNotNull(total_amount))
FROM Orders
WHERE isNull(total_amount) = 0;

Common Mistakes

Using Nullable columns everywhere. This slows compression and query speed. Fix by defining Nullable only when the business truly allows missing data and supply DEFAULTs otherwise.
Forgetting to coalesce before arithmetic. NULLs propagate and turn entire sums or averages into NULL. Always wrap numeric columns with ifNull() or coalesce() before calculations.