ClickHouse JOIN Types in TypeScript — LEFT, RIGHT, INNER, and ARRAY JOIN

ClickHouse supports the full set of SQL JOIN types — INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS — plus its own ARRAY JOIN which is a different concept entirely. The syntax looks familiar but the execution model is not like Postgres. Understanding how ClickHouse handles JOINs changes which ones you reach for.

How ClickHouse Executes JOINs

The important difference: ClickHouse loads the right-side table into memory in a hash table. The left side is streamed; the right side must fit in memory. This means:

• Keep the smaller table on the right
• For large right-side tables, memory becomes the constraint, not IO
• ClickHouse is not optimised for JOINs the way a row-oriented OLTP database is — its strengths are aggregations over many rows in a single table, not multi-table relational queries

For production analytics workloads where JOINs are frequent or the joined tables are large, the standard alternatives are Dictionaries (for lookup tables), materialized views that pre-join data at write time, or just denormalizing into a single wide table.

With that said — JOINs work, and for moderate data sizes they're fine.

INNER JOIN

Both sides must have a matching row. Rows without a match on either side are excluded.

Use INNER JOIN when you need strict referential integrity in the result — only rows where the join condition is satisfied on both sides. In analytics, this often means "orders placed by known users" with orphaned orders excluded.

LEFT JOIN

All rows from the left table, with matching rows from the right. If there's no match on the right, the right-side columns come back as NULL (or default values for non-nullable types in ClickHouse).

LEFT JOIN is the most common JOIN in analytics: get all events, optionally enriched with user metadata where available. Anonymous events or events with no matching user record still appear in results.

RIGHT JOIN

All rows from the right table, with matching rows from the left. This is the mirror of LEFT JOIN.

In practice, you almost never need RIGHT JOIN — you can always rewrite it as a LEFT JOIN by swapping the tables:

The LEFT JOIN version is preferred because it's more readable (the "primary" table is first) and because it keeps the larger table on the left where ClickHouse streams it rather than loading it into memory.

FULL OUTER JOIN

All rows from both tables. Rows without a match on either side are included with NULLs on the missing side.

Useful for set comparisons — finding users in either cohort, or users that appear in one but not the other.

ARRAY JOIN — Not a Relational Join

ARRAY JOIN is a ClickHouse-specific feature that is fundamentally different from relational joins. It's not joining two tables — it flattens an array column into multiple rows, one row per array element.

If a page_views row has tags = ['analytics', 'dashboard', 'beta'], ARRAY JOIN produces three output rows from that one input row, each with a different tag value. This is used for event properties stored as arrays, multi-value attributes, and similar denormalized structures common in ClickHouse schemas.

See ClickHouse ARRAY JOIN in TypeScript for a full treatment of this pattern.

JOINs in TypeScript with hypequery

hypequery is focused on typed query composition, but for more complex JOIN shapes it is reasonable to use raw SQL:

The raw escape hatch gives you full SQL control with parameterized queries. The type parameter tells TypeScript what the result rows look like.

For single-table queries — which cover the majority of ClickHouse analytics workloads — hypequery's typed query builder handles the common path well: WHERE, GROUP BY, ORDER BY, aggregations, and time-series bucketing. When a query needs ClickHouse-specific join or PREWHERE syntax, use raw SQL directly.

Performance Summary

| Join Type | When to Use | Watch Out For | |-----------|-------------|---------------| | INNER JOIN | Both sides must match | Excludes non-matching rows silently | | LEFT JOIN | All left rows, optional right match | Right side loaded into memory | | RIGHT JOIN | Rarely — rewrite as LEFT JOIN | Less readable, same memory constraint | | FULL OUTER JOIN | Set union/difference queries | Both sides must fit in memory | | ARRAY JOIN | Flattening array columns | Not a table join — different concept |

For large-scale production use, consider Dictionaries for dimension lookups and denormalized schemas to avoid JOINs in hot query paths.

---

Related: ClickHouse Query Builder · ClickHouse TypeScript Guide · ClickHouse ARRAY JOIN in TypeScript