Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 35 ยท Querying and Functions

Joins, Subqueries and Dictionaries

Learn ClickHouse join types and algorithms, ASOF and ANY joins, IN subqueries, and why dictionaries with dictGet are often faster than joining small lookup tables.

ClickHouse supports JOIN, but it is built differently from a classic database. The right-hand table is loaded into memory (a hash table) for most join algorithms, and the engine has no cost-based optimizer that reorders joins as aggressively as PostgreSQL does. The result is a set of habits: put the small table on the right, filter early, prefer IN or dictionaries for lookups, and denormalise data that is joined constantly. This lesson covers the join types, the habits and the alternatives.

ClickHouse cannot run on this site. ClickHouse-specific joins (ASOF, ANY, SEMI, ANTI) and dictionaries are read-only. The runnable blocks use standard SQL joins and a correlated lookup in SQLite to show the equivalent results.

Join types

SQL
SELECT o.order_id, c.name, o.amount
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;
Join Behaviour
INNER JOIN Only matching rows
LEFT [OUTER] JOIN All left rows, right columns default if no match
RIGHT, FULL OUTER JOIN Symmetric variants
CROSS JOIN Cartesian product
ANY LEFT JOIN At most one match per left row (cheaper, avoids duplicates)
LEFT SEMI JOIN Left rows that have a match (no right columns)
LEFT ANTI JOIN Left rows with no match
ASOF JOIN Match the closest earlier row (by time)

Unlike standard SQL, an unmatched row from a LEFT JOIN gets the column's default value (0, ''), not NULL, unless join_use_nulls = 1. Many teams enable that setting to avoid mistaking a default for real data.

SQL Stand-in: INNER vs LEFT JOIN (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

customer_id = 9 has no customer and Cy has no orders; an inner join would drop both, the left join keeps Cy with zero. A LEFT ANTI JOIN answers "customers with no orders" directly; in standard SQL that is WHERE NOT EXISTS:

SQL Stand-in: anti join with NOT EXISTS (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

The golden rules for joins

  1. Put the smaller table on the right. The right side is built in memory. Check with SETTINGS join_algorithm and watch max_memory_usage.

  2. Filter and aggregate before joining. Use subqueries so the join sees fewer rows:

    SQL
    SELECT c.name, s.total
    FROM (SELECT customer_id, sum(amount) AS total
          FROM orders WHERE order_date >= '2026-01-01'
          GROUP BY customer_id) AS s
    INNER JOIN customers AS c USING (customer_id);
    
  3. Alias and qualify columns, especially with SELECT * and USING.

  4. Choose an algorithm when the right table is large: hash (default, in memory), parallel_hash, partial_merge and grace_hash (spill to disk), full_sorting_merge (when both sides are sorted by the join key), direct (key-value tables and dictionaries). Recent versions pick for you with join_algorithm = 'auto'.

  5. Denormalise columns you always need together: store country in the events table rather than joining to a users table on every query.

ASOF JOIN

For time series, you often need "the last known value at or before this moment", such as the exchange rate in effect when a trade happened:

SQL
SELECT t.ts, t.amount, r.rate
FROM trades AS t
ASOF LEFT JOIN rates AS r
    ON t.currency = r.currency AND t.ts >= r.ts;

Equality conditions group the rows; the single inequality finds the closest match. In standard SQL this needs a correlated subquery or a window trick:

SQL Stand-in: latest rate at or before each trade (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Subqueries and IN

IN with a subquery is often faster than a join because ClickHouse builds a hash set, not a table with payload columns, and it can use the primary key:

SQL
SELECT count()
FROM page_views
WHERE user_id IN (SELECT user_id FROM users WHERE plan = 'pro');

For distributed tables, GLOBAL IN and GLOBAL JOIN first compute the right side once on the initiating server and send it to every shard. Without GLOBAL, each shard runs the subquery against its own local data, which can give wrong answers if the subquery table is also sharded. (More in the cluster chapter.)

Scalar subqueries and CTEs also work:

SQL
WITH (SELECT max(ts) FROM page_views) AS newest
SELECT count() FROM page_views WHERE ts > newest - INTERVAL 1 HOUR;
SQL Stand-in: IN subquery (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Dictionaries: fast in-memory lookups

A dictionary is a key-value structure kept in memory (refreshed on a schedule) and filled from a table, file, URL or another database. You look values up with dictGet, which is much cheaper than a join for small to medium reference data such as countries, product names or IP ranges.

SQL
CREATE DICTIONARY country_names
(
    code String,
    name String
)
PRIMARY KEY code
SOURCE(CLICKHOUSE(TABLE 'countries'))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);        -- reload every 5 to 10 minutes

SELECT dictGet('country_names', 'name', country) AS country_name,
       count() AS views
FROM page_views
GROUP BY country_name;

Layouts include FLAT (small integer keys), HASHED, COMPLEX_KEY_HASHED (composite keys), RANGE_HASHED (values valid for date ranges, such as price lists) and IP_TRIE (IP network lookups). dictGetOrDefault handles missing keys. Dictionaries also back JOIN ... with the direct algorithm.

The same lookup in standard SQL is a join to a small table:

SQL Stand-in: lookup table (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Join, dictionary or denormalise?

flowchart TD
    A["Need extra columns from another table"] --> B{"Is the other side small<br/>reference data?"}
    B -->|"Yes"| C["Dictionary + dictGet"]
    B -->|"No, large and changing"| D{"Joined on nearly every query?"}
    D -->|"Yes"| E["Denormalise into the main table"]
    D -->|"No"| F["JOIN: small table on the right,<br/>filter first"]

Match the tool to the shape of the data.

Common beginner mistakes

  • Joining a huge table on the right and running out of memory.
  • Forgetting that unmatched rows get default values, not NULL.
  • Joining before filtering, so the join sees billions of rows.
  • Using IN or JOIN against a distributed table without GLOBAL.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which table should usually be on the right side of a ClickHouse JOIN?

    Most algorithms build a hash table from the right side, so keep it small.
  2. 2. What does an unmatched LEFT JOIN row contain in the right columns by default?

    ClickHouse fills defaults (0, empty string) unless join_use_nulls is enabled.
  3. 3. When is a dictionary the better choice than a join?

    dictGet reads an in-memory key-value structure and avoids building a join each time.
  4. 4. What does ASOF JOIN do?

    ASOF joins find the nearest match on an ordered column, typical for time series.

Summary

  • ClickHouse joins build the right-hand table in memory: keep it small, filter and aggregate first.
  • Unmatched rows get default values unless join_use_nulls = 1.
  • Special joins (ANY, SEMI, ANTI, ASOF) express common patterns cheaply.
  • IN subqueries are efficient; use GLOBAL with distributed tables.
  • Dictionaries with dictGet are the fast way to do lookups; denormalise data joined on every query.

Next: back to the ClickHouse track