What is ClickHouse ANSI SQL mode?

ClickHouse does not have a single "ANSI SQL mode" switch. Instead, several individual settings each move one piece of behavior closer to the SQL standard, and "ANSI SQL mode" is the informal name for turning the relevant ones on together. ClickHouse defaults favor analytical performance over strict standard compliance, so by default it fills non-matching JOIN rows with type defaults, returns 0 from aggregates over empty sets, and drops Nullable in some casts. This page explains the settings involved and when to enable them.

Why ClickHouse defaults differ from ANSI SQL

ClickHouse was designed for high-throughput analytics, and many of its defaults trade SQL-standard semantics for speed and simpler memory layouts. The most visible difference is NULL handling: instead of producing NULL for missing values, ClickHouse often uses the data type's default value (0, empty string, etc.), which avoids the overhead of Nullable columns. This is faster but surprises users coming from PostgreSQL, MySQL, or other standard-compliant engines. The ANSI-oriented settings below let you opt back into standard behavior where correctness matters more than raw performance.

Key ANSI SQL compatibility settings

Enable only the settings your workload actually needs. Each one can add Nullable overhead or change result shapes, so flipping all of them blindly can hurt performance without benefit.

join_use_nulls

This is the setting most often meant by "ANSI mode." In an outer JOIN, when no match is found, the SQL standard requires NULL in the unmatched columns. By default ClickHouse uses the column type's default value instead:

-- join_use_nulls = 0 (default): unmatched right-side value becomes 0
SELECT l.id, r.value
FROM left_tbl AS l
LEFT JOIN right_tbl AS r ON l.id = r.id;

-- Enable ANSI behavior per-query
SET join_use_nulls = 1;
SELECT l.id, r.value          -- r.value is now Nullable; unmatched rows are NULL
FROM left_tbl AS l
LEFT JOIN right_tbl AS r ON l.id = r.id;

See the dedicated join_use_nulls setting page for behavior across LEFT, RIGHT, and FULL joins, and JOIN performance for the cost of the extra Nullable columns.

cast_keep_nullable

By default, CAST strips the Nullable wrapper from the result type. If the column actually contains a NULL value at runtime, ClickHouse raises a Cannot convert NULL value to non-Nullable type exception. Enabling cast_keep_nullable = 1 keeps the result Nullable, so NULL values pass through safely:

SET cast_keep_nullable = 1;
SELECT CAST(nullable_col AS Int64);  -- result stays Nullable(Int64)

UNION behavior

In standard SQL, a bare UNION removes duplicates (equivalent to UNION DISTINCT). ClickHouse does not pick a default for you: with union_default_mode = '', a bare UNION raises an error and you must write UNION ALL or UNION DISTINCT explicitly. Set union_default_mode = 'DISTINCT' to make bare UNION behave like the standard:

SET union_default_mode = 'DISTINCT';
SELECT id FROM a
UNION                       -- now equivalent to UNION DISTINCT
SELECT id FROM b;

Best practices

1. Decide on a small, explicit set of compatibility settings for your application rather than enabling everything.
2. Set them at the user or profile level (see settings profiles) so behavior is consistent across all queries, instead of per-query SET that is easy to forget.
3. Prefer being explicit in SQL where you can — write UNION ALL/UNION DISTINCT and use assumeNotNull/ifNull — so query meaning does not depend on session settings.
4. Benchmark with the settings on. join_use_nulls and Nullable columns add per-value overhead; confirm the correctness gain is worth it on your data.
5. Review the broader list in useful settings to enable and settings to adjust from defaults.

Common issues

1. Mixing settings between writer and reader. For the Join table engine, join_use_nulls must match between table creation and SELECT, or results differ unexpectedly.
2. Assuming a single global "ANSI mode" flag. There is none; each behavior is its own setting and they were introduced in different versions.
3. Performance regressions from blanket enabling. Turning on every NULL-related setting forces Nullable types widely, increasing memory and CPU. Enable selectively.
4. Expecting NULL from aggregates without the setting. sum/avg over an empty set return 0/nan unless aggregate_functions_null_for_empty = 1.

How Pulse helps

Compatibility settings like join_use_nulls and cast_keep_nullable change query results, not just performance, so silent drift between environments or profiles can produce wrong numbers that are hard to trace. Pulse continuously monitors ClickHouse configuration and query behavior across your clusters, surfacing setting inconsistencies and the queries affected by them. With health checks and AI-assisted recommendations, Pulse helps you keep NULL and JOIN semantics consistent while watching the performance impact of the settings you enable.

Frequently Asked Questions

Q: Is there a single setting to enable full ANSI SQL mode in ClickHouse?
A: No. ClickHouse has no single ANSI mode switch. You enable individual settings such as join_use_nulls, cast_keep_nullable, union_default_mode, aggregate_functions_null_for_empty, and group_by_use_nulls as needed.

Q: Why does my LEFT JOIN return 0 instead of NULL for missing rows?
A: That is the default join_use_nulls = 0 behavior, which fills unmatched cells with the column type's default value. Set join_use_nulls = 1 for standard NULL behavior.

Q: Do these settings affect performance?
A: Yes. Most of them introduce or preserve Nullable columns, which add per-value overhead in storage, memory, and CPU. Enable only the ones your application requires and benchmark them.

Q: Should I set ANSI options per query or globally?
A: For consistency, set them at the user or settings-profile level so every query behaves the same. Per-query SET is fine for testing but easy to forget in production code.

Q: Why does a bare UNION raise an error in ClickHouse?
A: By default union_default_mode is empty, so ClickHouse refuses to guess. Write UNION ALL or UNION DISTINCT explicitly, or set union_default_mode = 'DISTINCT' to match the SQL standard.

Q: Does ClickHouse use three-valued logic for NULLs?
A: Yes, ClickHouse supports NULL and three-valued logic through Nullable types. The non-standard part is that defaults often avoid producing NULL for performance, which the ANSI settings above override.