In ClickHouse, what does ARRAY JOIN do to a row, and how does LEFT ARRAY JOIN differ?
answer
- one row in, many rows out
- empty arrays behave like an inner join
- the LEFT variant rescues the empty case
- several arrays in one clause zip by position
- expansion happens before GROUP BY
basics
~20 sARRAY JOIN expands each row into one row per element of the named array, so a row with five elements becomes five rows. Rows whose array is empty disappear. LEFT ARRAY JOIN keeps them, emitting one row with the element type's default value.
solid answer
~50 s`ARRAY JOIN arr AS x` is ClickHouse's unnesting clause: it replicates the row once per element of `arr`, exposing the element under the alias while every other column is repeated. It is not a join between tables despite the name. A row whose array is empty produces **no** output rows under plain `ARRAY JOIN`; `LEFT ARRAY JOIN` keeps that row once with the element set to the type's default (`0`, `''`, and so on), which is the behaviour you want when the array is optional and you must not lose the row. Listing several arrays in one clause — `ARRAY JOIN a, b` — traverses them **in parallel** by position, so they must be the same length; separate clauses would multiply. The `arrayJoin(arr)` function does the same expansion in expression position. Because expansion multiplies rows before grouping, prefer per-row array functions when you only need a computed property of the array.
code
sql · 12 lines-- one row per (event, tag); untagged events disappear
SELECT tag, count() AS n
FROM events
ARRAY JOIN tags AS tag
GROUP BY tag
ORDER BY n DESC;
-- keep untagged events, bucketed under the empty string
SELECT tag, count() AS n
FROM events
LEFT ARRAY JOIN tags AS tag
GROUP BY tag;go deeper
Be able to say that ARRAY JOIN turns one row into one row per array element, and that the alias exposes the element while other columns repeat.
Explain the empty-array fork between ARRAY JOIN and LEFT ARRAY JOIN, and why several arrays in one clause zip in parallel rather than multiply.
Show judgment about cost: filter parents before expanding, and answer per-row questions with array functions or the -Array combinator instead of unnesting a billion rows twenty-fold.
Own the modelling call. Decide when nested arrays with occasional unnesting beat a separate child table, and set the convention so dashboards do not silently drop rows with empty arrays.
## What the clause does `ARRAY JOIN` sits alongside `FROM` and expands array values into rows. Given a table ```sql CREATE TABLE events (id UInt64, tags Array(String)) ENGINE = MergeTree ORDER BY id; ``` the query ```sql SELECT id, tag FROM events ARRAY JOIN tags AS tag ``` emits one row per (event, tag) pair. Every non-array column is repeated for each element. The name is misleading: nothing is joined to another table, and no join key is involved. Think of it as the clause that turns a nested value into rows. ## Empty arrays: the behavioural fork The single most-asked detail is what happens to a row whose array is empty. Under plain `ARRAY JOIN`, zero elements means zero output rows, so the row **vanishes** — this is exactly like an inner join finding no match. `LEFT ARRAY JOIN` keeps the row once and fills the element alias with the element type's default: `''` for `String`, `0` for numeric types, an empty array for nested arrays. That difference silently changes totals. A query that counts events by tag with `ARRAY JOIN` reports fewer events than `SELECT count() FROM events`, because untagged events were dropped. If someone reports "my counts don't add up after unnesting", this is usually why. ```sql -- 3 rows with arrays of length 2, 0 and 5 SELECT count() FROM t ARRAY JOIN arr; -- 7 SELECT count() FROM t LEFT ARRAY JOIN arr; -- 8 ``` ## Several arrays at once Listing multiple arrays in one clause traverses them **in parallel**, pairing element *i* of each: ```sql SELECT id, name, price FROM baskets ARRAY JOIN item_names AS name, item_prices AS price ``` This requires the arrays to be the same length in every row and throws otherwise. It is the right way to unnest parallel arrays that together describe a list of records. Two *separate* `ARRAY JOIN` clauses instead produce the cross product of the two arrays, which is almost never what you want — a row with 3 names and 3 prices becomes 9 rows. A `Nested(...)` column is stored as a set of parallel arrays, so `ARRAY JOIN nested_col` unnests all of its subcolumns together in parallel — the same mechanism with nicer syntax. You can also unnest an expression, not just a stored column: `ARRAY JOIN arrayEnumerate(tags) AS idx` gives you the 1-based position of each element, which is how you recover ordering after expansion. ## The function form `arrayJoin(arr)` does the same expansion but in expression position — you can write it in the `SELECT` list or in a subquery. It is convenient for ad-hoc queries and for expanding a literal (`arrayJoin([1, 2, 3])` as a row generator). Two subtleties: using `arrayJoin` more than once in a query multiplies rows once per call, and the exact stage at which the expansion happens relative to other clauses is easier to reason about with the explicit `ARRAY JOIN` clause. For anything but a throwaway query, prefer the clause. ## Cost, and when not to unnest Expansion happens before `GROUP BY`, so it multiplies the number of rows the aggregation must process. If the average array holds 20 elements, unnesting turns a 1-billion-row scan into a 20-billion-row aggregation. Two rules follow: 1. **Filter first.** Put predicates on the parent row in `WHERE` where possible so fewer rows are expanded. Filtering the element itself must happen after the expansion. 2. **Don't unnest to compute a per-row property.** If the question is "how many tags does this event have" or "does any tag match", answer it with `length(tags)` or `arrayExists(t -> t = 'x', tags)` and never leave one row per event. Unnest only when the output genuinely needs one row per element, such as a count *per tag*. A third option for aggregate-over-elements is the `-Array` combinator: `uniqArray(tags)` computes distinct tags across all rows without expanding anything. ## What interviewers probe The expected answers are: it multiplies rows; empty arrays disappear unless you write `LEFT`; several arrays in one clause zip rather than multiply; and expansion before aggregation is the cost you must justify. A candidate who reaches for `ARRAY JOIN` for every array question, rather than asking whether the result needs one row per element, is showing portable-SQL instincts rather than ClickHouse ones.
- What is the difference between listing two arrays in one ARRAY JOIN clause and writing two separate clauses?One clause with two arrays traverses them in parallel, pairing element *i* of each and requiring equal lengths — that is how you unnest parallel arrays describing the same list of records. Two separate clauses nest, producing the cross product: 3 names and 3 prices become 9 rows instead of 3. The cross product is almost always a bug.
- How would you keep both the per-tag breakdown and the untagged events in one ClickHouse result?Use `LEFT ARRAY JOIN`, which emits one row with the element's default (an empty string for `Array(String)`) for rows whose array is empty. Group on the element and the empty-string bucket becomes your "untagged" group. With plain `ARRAY JOIN` those events are dropped entirely and the totals no longer reconcile with `count()` on the base table.
saying these in an interview costs you the question
- Thinks ARRAY JOIN joins the table to another table
- Assumes rows with empty arrays survive plain ARRAY JOIN
- Expects two arrays in one clause to cross-multiply
- Unnests just to count elements instead of using length()
- Ignores that expansion multiplies rows before GROUP BY