🧠 ClickHouse LEFT JOINs: Why join_use_nulls Matters

🧠 Understanding join_use_nulls in ClickHouse

ClickHouse is famous for being blazing fast — but sometimes its SQL semantics can surprise you, especially around JOINs.

Here’s a simple example that shows how the join_use_nulls setting can completely change your results.

🧩 The Setup

Let’s create two tiny tables:

CREATE TABLE test.id_val(`id` UInt32, `val` UInt32) ENGINE = TinyLog; INSERT INTO test.id_val VALUES (1,11),(2,12),(3,13); CREATE TABLE test.id_val2(`id` UInt32, `val` UInt32) ENGINE = TinyLog; INSERT INTO test.id_val2 VALUES (1,21),(1,22),(3,23); 

We’ve got:

id_val: three rows with IDs 1, 2, and 3
id_val2: three rows, but ID 2 is missing and ID 1 appears twice

🧮 The Default JOIN

Let’s run a LEFT JOIN:

SELECT * FROM test.id_val LEFT JOIN test.id_val2 USING (id); 

Output:

┌─id─┬─val─┬─val_1─┐ │ 1 │ 11 │ 21 │ │ 1 │ 11 │ 22 │ │ 2 │ 12 │ 0 │ │ 3 │ 13 │ 23 │ └────┴─────┴───────┘ 

Notice something interesting?
When there’s no matching row for id=2, ClickHouse fills it with a default value — in this case, 0.

For numeric columns, the default is 0.
For strings, it would be an empty string ''.

🧩 Enter join_use_nulls

If you want standard SQL behavior, where missing values become NULL, use:

SELECT * FROM test.id_val LEFT JOIN test.id_val2 USING (id) SETTINGS join_use_nulls = 1; 

Now the output looks like this:

┌─id─┬─val─┬─val_1─┐ │ 1 │ 11 │ 21 │ │ 1 │ 11 │ 22 │ │ 2 │ 12 │ NULL │ │ 3 │ 13 │ 23 │ └────┴─────┴───────┘ 

That’s closer to what most SQL engines (like PostgreSQL or MySQL) would do.

⚙️ Why This Matters

This setting affects how ClickHouse represents non-matching rows in joins — which can have downstream effects on:

• Aggregations
  SELECT count(val_1) FROM ...
  will count 0s differently than NULLs.

• Exports & BI tools
  Some tools treat 0 as a valid number, but NULL as missing data.

• Data semantics
  A missing match isn’t always “zero” — it might mean “unknown”.

🚀 Performance Note

The default behavior (without join_use_nulls) is slightly faster and more memory-efficient because ClickHouse doesn’t have to track NULL bitmaps.

If you’re doing high-volume analytical joins and don’t care about SQL-standard nulls, keep it off.
If you need correctness and consistency with other systems, turn it on.

🔍 Check Your Settings

You can check the current value of this setting in your ClickHouse session:

SELECT name, value FROM system.settings WHERE name LIKE '%join_use_nulls%'; 

Or set it globally in your configuration file or session:

SET join_use_nulls = 1; 

📚 Reference

For more detail on ClickHouse’s Join engine and its settings (including join_use_nulls), see: ClickHouse docs — Join table engine

🗨️ Discussion

Have you ever been surprised by ClickHouse JOIN behavior?
Do you leave join_use_nulls off for performance, or always turn it on for clarity?