Clickhouse поддерживает JOIN таблиц с несколькими нюансами. Синтаксис привычный:

SELECT * FROM table1 ANY|ALL INNER|LEFT JOIN table2 USING columns

Объединение таблиц

Нужно учесть, что колонки, по которым происходит объединение должны называться одинаково в двух таблицах. Пусть две таблицы carts и checkout имеют такую структуру:

carts:		cart_id | product_id | count | added_date | added_time
checkouts:	cart_id | product_id | paid_date | paid_time

# таблицы хранят данные о корзинах и оформленных заказах

Тогда запрос будет выглядеть так:

SELECT cart_id, product_id, added_time, paid_time
FROM carts
ANY LEFT JOIN checkouts
USING cart_id, product_id

# Колонки cart_id, product_id должны быть в таблицах carts и checkouts

В результате мы увидим все товары, добавленные в корзину, и дату покупки тех товаров, которые были куплены:

┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       1 │          1 │ 2018-03-24 11:11:36 │ 0000-00-00 00:00:00 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘
┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       1 │          2 │ 2018-03-24 11:11:41 │ 0000-00-00 00:00:00 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘
┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       2 │          1 │ 2018-03-24 11:11:46 │ 2018-03-24 11:13:27 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘
┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       3 │          3 │ 2018-03-24 11:11:50 │ 0000-00-00 00:00:00 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘

# Как видно, только один товар (корзина) был куплен

Подзапросы

Некоторые колонки в таблицах могут называться одинаково. Тогда стоит использовать подзапросы и псевдонимы (aliases):

SELECT cart_id, product_id, when_added, when_bought
FROM (
	SELECT cart_id, product_id, added_time as when_added FROM carts
)
ANY LEFT JOIN (
	SELECT cart_id, product_id, paid_time as when_bought FROM checkouts
)
USING cart_id, product_id

# Можно использовать псевдонимы и для колонок, которые используются в USING

Тип объединения LEFT|INNER

Тип LEFT сначала выберет все записи из левой таблицы, а затем добавит к ним найденные записи из правой. Это сработает так же, как и в примере выше.

Использование типа INNER приведет к тому, что в результате мы увидим только те записи, которые есть и в правой и в левой таблице:

SELECT cart_id, product_id, added_time, paid_time
FROM carts
ANY INNER JOIN checkouts
USING cart_id, product_id

В результате мы увидим только одну строку (т.к. в правой таблице всего одна запись):

┌─cart_id─┬─product_id─┬──────────added_time─┬───────────paid_time─┐
│       2 │          1 │ 2018-03-24 11:11:46 │ 2018-03-24 11:13:27 │
└─────────┴────────────┴─────────────────────┴─────────────────────┘

Строгость объединения ANY|ALL

Использование ALL приведет к тому, что для каждого ключа из левой таблицы будут выбраны все соответствующие записи из правой:

SELECT 
    product_id, 
    added_time, 
    paid_time
FROM checkouts 
ALL LEFT JOIN carts USING (product_id)

# Довольно бессмысленный запрос :)

В результате увидим все купленные товары со всеми датами добавления их в корзины:

┌─product_id─┬──────────added_time─┬───────────paid_time─┐
│          1 │ 2018-03-24 11:11:36 │ 2018-03-24 11:13:27 │
│          1 │ 2018-03-24 11:11:46 │ 2018-03-24 11:13:27 │
└────────────┴─────────────────────┴─────────────────────┘

# Для каждой записи из таблицы checkouts мы выбрали

Более популярным методом объединения при работе с аналитическими данными является ANY. Он выбирает первое попавшееся соответствие из правой таблицы:

SELECT 
    product_id, 
    added_time, 
    paid_time
FROM checkouts 
ANY LEFT JOIN carts USING (product_id)

В этот раз мы увидим только одну строку (т.к. она одна в левой таблице):

┌─product_id─┬──────────added_time─┬───────────paid_time─┐
│          1 │ 2018-03-24 11:11:36 │ 2018-03-24 11:13:27 │
└────────────┴─────────────────────┴─────────────────────┘

# Первая запись из правой таблицы, соответствующая записи в левой

Оптимизация JOIN

Clickhouse выполняет объединение до фильтрации WHERE. Т.е. сначала все результаты загружаются в память, а уже потом фильтруются, сортируются и группируются. Следовательно, подзапросы лучше использовать, когда нужно объединить только части таблиц. Например:

SELECT cart_id, product_id, added_time, paid_time
FROM (
	SELECT * FROM carts WHERE added_date = today()
)
ANY LEFT JOIN (
	SELECT * FROM checkouts
)
USING cart_id, product_id

# Производительный вариант объединения при фильтрации

Это быстрый вариант запроса. А такой запрос будет работать медленнее, т.к. выгрузит данные из всей таблицы в память перед объединением и последующей фильтрацией:

SELECT cart_id, product_id, added_time, paid_time
FROM carts
ANY LEFT JOIN checkouts
USING cart_id, product_id
WHERE added_time = today()

# Медленный вариант объединения при фильтрации

TL;DR

  • JOIN в Clickhouse бывает двух типов — INNER (показывает только записи, которые есть и в левой и в правой таблице) и LEFT (показывает все результаты из левой таблицы).
  • ANY позволяет выбрать только одну (первую) запись из правой таблицы, а ALL выгрузит все соответствующие записи из правой.
  • Для оптимизации объединений лучше использовать подзапросы с фильтрацией.