ClickHouse Cheatsheet 13 — Joins and Subqueries

ClickHouse joins.

Types

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN
LEFT SEMI JOIN
LEFT ANTI JOIN
ANY LEFT JOIN              -- first match

SELECT e.user_id, u.name, count()
FROM events e
INNER JOIN users u ON e.user_id = u.id
GROUP BY e.user_id, u.name;

CH joins less optimized than Postgres. Right table loaded into memory.

SELECT dictGet('users_dict', 'name', user_id) AS name, count()
FROM events GROUP BY name;

Faster for small lookup tables.

SELECT * FROM events WHERE user_id IN (SELECT id FROM users WHERE active);

For “filter by other table.” Often faster than JOIN.

For Distributed tables:

SELECT * FROM events_dist WHERE user_id GLOBAL IN (SELECT id FROM users_dist WHERE active);

Broadcasts inner result to all shards.

SELECT e.*, p.price
FROM events e
ASOF LEFT JOIN prices p
ON e.symbol = p.symbol AND e.ts >= p.ts;

For time-series joins.

SELECT * FROM (
    SELECT user_id, count() AS c FROM events GROUP BY user_id
) WHERE c > 100;

CTEs (WITH) too.

SETTINGS join_algorithm = 'hash', max_bytes_in_join = 1000000000

For huge joins: partial_merge, direct.