Clickhouse - merge with nulls

merge_with_nulls.sql

/* Outer Merges with Nulls in ClickHouse
 * 
 * By default, outer merges in clickhouse will produce _interesting_ defualt
 * values for empty cells in an outer join. This query demonstrates how to
 * configure clickhouse to use nulls for missing values, as in the SQL
 * standard.
 *
 * See: https://clickhouse.com/docs/en/operations/settings/settings#join_use_nulls
 *
 */

SELECT
  *
FROM left
FULL OUTER JOIN right
ON left.key = right.key
ORDER BY COALESE(left.value, right.value)
SETTINGS join_use_nulls=1;