In ClickHouse, what do the ANY, ALL and ASOF JOIN strictness modes mean?

## Strictness is a keyword, not a hint In ANSI SQL a join's type (`INNER`, `LEFT`, …) is all you specify, and matching is always all-to-all. ClickHouse adds an orthogonal **strictness** keyword that controls how many right-hand matches a left row may consume: ```sql SELECT ... FROM left ANY LEFT JOIN right ON ... SELECT ... FROM left ALL INNER JOIN right ON ... SELECT ... FROM left ASOF LEFT JOIN right ON ... ``` Omitting it uses the `join_default_strictness` setting, whose default is `ALL` — ANSI behaviour. Older deployments occasionally set it to `ANY`, so an unqualified join can mean different things on different clusters; writing the strictness explicitly is a good habit. ## ALL `ALL` is ordinary SQL: a left row matching three right rows produces three result rows. Fan-out is possible and, when the right side is not unique on the join key, this is where duplicated sums come from. Nothing surprising here — it is the baseline the other modes deviate from. ## ANY `ANY` stops at the first match: one left row yields at most one result row. Which right row you get is **unspecified** — do not build logic on it. It is meant for lookups where the right side is effectively unique (a dimension keyed by id, a config table) and you want a guarantee that the join cannot multiply rows. It is cheaper than `ALL` because the join can stop probing after the first hit. The risk is exactly the same as its benefit: if the right side is *not* unique, `ANY` silently picks a row and your result is non-deterministic between runs. Use it when uniqueness is a property of the data you can state, not as a shortcut for deduplicating a messy right side. ## ASOF `ASOF JOIN` is the mode with no ANSI equivalent and the one interviews love. It matches on zero or more equality conditions **plus exactly one inequality, which must come last in the `ON` clause**: ```sql SELECT t.ts, t.symbol, t.qty, q.price FROM trades AS t ASOF LEFT JOIN quotes AS q ON t.symbol = q.symbol AND t.ts >= q.ts ``` For each trade this returns the single most recent quote for that symbol at or before the trade's timestamp. The inequality may be `>=`, `>`, `<=` or `<`; the direction decides whether you get the closest preceding or the closest following row. `ASOF LEFT JOIN` keeps trades with no qualifying quote; plain `ASOF JOIN` drops them. Writing the same thing portably means a correlated subquery or a window function over a union of both tables, both of which are dramatically more expensive. This is the query shape that makes ClickHouse attractive for time-series joins. ## SEMI and ANTI `SEMI LEFT JOIN` returns left rows that have at least one match, once each, without right-hand columns fanning out; `ANTI LEFT JOIN` returns left rows with no match. They are the explicit forms of `EXISTS` / `NOT EXISTS` semantics as a join strictness. ## The NULL surprise Separate from strictness, and the thing that actually breaks people's queries: `join_use_nulls` defaults to **0**. With that default, a `LEFT JOIN` whose right side does not match fills the right-hand columns with the **type's default value** — `0` for numbers, `''` for strings, `[]` for arrays — not `NULL`. ```sql -- with defaults, unmatched right-hand amount is 0, not NULL SELECT u.id, o.amount FROM users AS u ALL LEFT JOIN orders AS o ON o.user_id = u.id; ``` So `WHERE o.amount IS NULL` finds nothing, and `count(o.amount)` counts unmatched rows too. Setting `join_use_nulls = 1` restores ANSI behaviour by making the right-hand columns `Nullable`, at the cost of the `Nullable` wrapper's extra byte per value and some function-compatibility friction. Either choice is defensible; not knowing which one is in force is not. ## Practical notes ClickHouse builds its hash table from the **right** table, so the right side should be the smaller one — there is no automatic reordering to rescue you from putting a billion-row table on the right. In distributed queries, `GLOBAL JOIN` sends the right side to every shard once instead of each shard joining against its local slice. ## What interviewers probe They want the three modes distinguished cleanly, the `ASOF` inequality-must-be-last rule, and ideally the `join_use_nulls` default — because it is the reason a migrated ANSI query returns wrong numbers rather than an error.