Handling NULLs in ORDER BY and MergeTree Sorting

ClickHouse has two distinct behaviors that both involve NULL and sorting: how NULL values are positioned when you sort query results with ORDER BY, and whether a Nullable column is even allowed in a table's MergeTree sort key. These are easy to conflate but they follow different rules.

This guide covers both: the default NULLS LAST ordering (and how to override it), how NaN interacts with NULL during sorts, and the rules, costs, and gotchas of putting Nullable columns in a MergeTree ORDER BY / PRIMARY KEY.

NULL Position in Query-Level ORDER BY

When you sort the result of a query, ClickHouse places NULLs deterministically. The default is equivalent to NULLS LAST:

Regular values first, then NaN, then NULL.

You can override this per sort expression with the NULLS FIRST or NULLS LAST modifier:

-- Default: values, then NaN, then NULL
SELECT * FROM t ORDER BY y;

-- Explicit NULLS LAST (same as default)
SELECT * FROM t ORDER BY y NULLS LAST;

-- NULL first, then NaN, then other values
SELECT * FROM t ORDER BY y NULLS FIRST;

The modifier attaches to a single sort key, so you can mix policies in a multi-column ORDER BY:

SELECT * FROM t
ORDER BY
    a ASC NULLS FIRST,
    b DESC NULLS LAST;

How NaN Fits In

For floating-point columns, NaN is handled separately from both real values and NULL. Regardless of ASC or DESC, NaN sorts to the end of the numeric values — in ascending order it behaves as if it were larger than every number, and in descending order as if it were smaller. The NULLS FIRST / NULLS LAST modifier then positions NULL relative to that ordered block. The full default order is therefore: real values, then NaN, then NULL.

This query-level behavior applies to any Nullable expression in the result set and does not require any special table settings — it works on ordinary columns, function outputs, and joined data alike.

Nullable Columns in the MergeTree Sort Key

The table's ORDER BY clause (which also defines the primary index) is a different matter. By default, ClickHouse rejects Nullable columns in the sorting key:

CREATE TABLE t
(
    a Nullable(UInt32),
    b UInt32
)
ENGINE = MergeTree
ORDER BY (a, b);
-- DB::Exception: Sorting key cannot contain nullable columns

To allow it, you must opt in with allow_nullable_key:

CREATE TABLE t
(
    a Nullable(UInt32),
    b Nullable(UInt32),
    cnt UInt32
)
ENGINE = MergeTree
ORDER BY (a, b)
SETTINGS allow_nullable_key = 1;

The official documentation and the Altinity Knowledge Base both mark this as not recommended for general use — enable it only when you genuinely cannot model the column otherwise, and test on a recent ClickHouse version. When NULLs are present in the sort key, the NULLS LAST principle governs their position within the sorted data.

For background on the table-level ORDER BY and how it drives the sparse primary index, see MergeTree table engine, CREATE TABLE in ClickHouse, and primary keys and indexing.

How ClickHouse Stores Nullable Data

A Nullable(T) column is not stored as a single column. ClickHouse maintains:

1. A values subcolumn holding T (with a placeholder/default in the NULL slots), and
2. A separate null map — a UInt8 subcolumn where 1 marks a NULL.

Every read of a Nullable column touches both subcolumns, and every comparison must consult the null map. When such a column sits in the sort key, this extra indirection happens during merges and primary-index lookups as well, which is the core reason Nullable sort keys are discouraged.

You can inspect the null map directly with the .null subcolumn:

SELECT a, a.null AS is_null
FROM t
ORDER BY a;

The isNull() and isNotNull() functions are the portable way to test for NULL in filters:

SELECT count() FROM t WHERE isNotNull(a);

Avoiding Nullable in Sort Keys: Sentinel Values

In most analytics schemas you do not need Nullable at all. ClickHouse's own best-practice guidance is to prefer a non-nullable column with a sentinel DEFAULT value, which keeps the column eligible for the sort key with no null-map overhead:

-- Instead of this:
CREATE TABLE events
(
    user_id Nullable(UInt64),
    ts      DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, ts)
SETTINGS allow_nullable_key = 1;

-- Prefer this:
CREATE TABLE events
(
    user_id UInt64 DEFAULT 0,   -- 0 means "unknown"
    ts      DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, ts);

Common sentinels are 0 for integers, '' for strings, and an out-of-range date for timestamps. For low-cardinality categorical data, LowCardinality(String) with an empty-string sentinel is a strong alternative — see LowCardinality types. The trade-off is that your application must interpret the sentinel as "missing," but you gain a smaller, faster table.

Comparison: Query ORDER BY vs. Sort-Key ORDER BY

Common Issues

1. "Sorting key cannot contain nullable columns" — You put a Nullable column in the table's ORDER BY/PRIMARY KEY without SETTINGS allow_nullable_key = 1. Either add the setting (and accept the cost) or, preferably, switch to a non-nullable column with a sentinel default.

2. NULLs appear where you didn't expect — Remember the default is NULLS LAST. If you migrated from a database that defaults to NULLS FIRST (or vice versa), add the explicit modifier to your ORDER BY.

3. NaN treated like NULL — They are different. NaN is a real floating-point value that always sorts to the end of the numeric block; only actual NULLs are moved by NULLS FIRST/NULLS LAST. Use isNaN() to test for NaN and isNull() for NULL.

4. Can't insert NULL into a non-nullable column — If you removed Nullable in favor of a sentinel, inserts of literal NULL will fail. See Cannot insert NULL in ordinary column.

5. Confusing this with JOIN NULLs — How a JOIN produces NULLs for non-matching rows is controlled separately by join_use_nulls; see the join_use_nulls setting.

Best Practices

1. Avoid Nullable in sort keys. Model "missing" with a sentinel DEFAULT value so the column stays in the primary key without null-map overhead.

2. Be explicit about NULL position. Add NULLS FIRST/NULLS LAST to result-set ORDER BY clauses when the placement matters, rather than relying on the default.

3. Keep Nullable for genuinely optional columns only, and keep those columns out of the ORDER BY/PRIMARY KEY whenever possible.

4. Reserve allow_nullable_key = 1 for cases you've measured. It is off by default for good reason; enable it consciously and verify behavior on your ClickHouse version.

5. Test NULL and NaN separately. Use isNull()/isNotNull() and isNaN() so your filters and ordering do exactly what you intend.

How Pulse Helps

Pulse runs continuous health checks against your ClickHouse clusters and flags schema choices that hurt performance — including Nullable columns sitting in MergeTree sort keys, which inflate storage and slow merges and primary-index lookups. When ordering or NULL-handling produces surprising query results, Pulse correlates the schema, settings, and query patterns so you can see why, and recommends sentinel-based modeling where it pays off. Learn more at pulse.support.

Frequently Asked Questions

Q: What is ClickHouse's default NULL ordering in ORDER BY?

NULLS LAST. The full default order is real values first, then NaN, then NULL. Use NULLS FIRST to move NULLs to the top.

Q: Does NULLS FIRST / NULLS LAST work with both ASC and DESC?

Yes. The modifier only controls where NULLs go; ASC/DESC orders the non-NULL values. NaN is always adjacent to NULL — with NULLS LAST the order is values, NaN, NULL; with NULLS FIRST the order is NULL, NaN, then values.

Q: Can I use a Nullable column in a MergeTree primary key or ORDER BY?

Only if you create the table with SETTINGS allow_nullable_key = 1. Without it, ClickHouse raises "Sorting key cannot contain nullable columns." It's allowed but discouraged for general use.

Q: Why are Nullable sort keys discouraged?

A Nullable column adds a separate UInt8 null-map subcolumn that must be read and checked on every operation — including merges and primary-index lookups. A non-nullable column with a sentinel DEFAULT avoids that cost while staying in the sort key.

Q: Is NaN the same as NULL for sorting?

No. NaN is a real floating-point value that always sorts to the end of the numeric values; NULL is the absence of a value and is positioned by NULLS FIRST/NULLS LAST. Test them with isNaN() and isNull() respectively.

Q: How should I represent "missing" without Nullable?

Use a sentinel default: 0 for integers, '' for strings, an out-of-range date for timestamps, or LowCardinality(String) with an empty string. Your application interprets the sentinel as missing, and the column remains efficient in the sort key.