Оператор JOIN
Обзор
Оператор JOIN
обеспечивает выборку данных из двух таблиц и объединяет эти данные в один результирующий набор. То, какие строки исходных таблиц будут включены в результат и в каких комбинациях, зависит от типа JOIN
и явно указанного условия соединения.
В общем виде синтаксис JOIN
можно записать следующим образом:
FROM <left_table> <JOIN_type> <right_table> <join_condition>
где:
-
<JOIN_type>
— тип соединения. ADQM поддерживает все стандартные типы JOIN языка SQL, а также дополнительные специализированные типы соединения, которые могут быть полезны для выполнения различных аналитических запросов и анализа временных рядов.
Стандартные типы JOIN языка SQL:
Дополнительные типы JOIN, доступные в ADQM:
Если тип JOIN явно не указан, применяетсяINNER JOIN
. -
<join_condition>
— условие, по которому определяется, какие строки двух исходных таблиц соответствуют друг другу. Условие соединения (или несколько условий) должно быть указано с помощью выраженияON <expr_list>
илиUSING <column_list>
для всех типовJOIN
кромеCROSS JOIN
— подробнее в разделе Условие JOIN.
Настройка join_algorithm позволяет установить алгоритм выполнения запроса JOIN
. Можно указать несколько алгоритмов (тогда один из доступных алгоритмов будет выбираться для каждого конкретного запроса на основе его типа/строгости и движка таблицы) или разрешить системе выбирать и динамически изменять алгоритм во время выполнения запроса в зависимости от доступности ресурсов.
Чтобы воспроизвести приведенные ниже примеры и посмотреть, как различные типы JOIN
работают с данными, создайте предварительно две таблицы:
-
users
CREATE TABLE users (user_id Int32, name String, role_id Int32) ENGINE = MergeTree ORDER BY user_id;
INSERT INTO users VALUES (1, 'john', 10), (1, 'john', 20), (1, 'john', 30), (2, 'mary', 30), (2, 'mary', 40), (3, 'andrew', 40), (4, 'harry', 70), (5, 'ann', 50);
┌─user_id─┬─name───┬─role_id─┐ │ 1 │ john │ 10 │ │ 2 │ mike │ 20 │ │ 3 │ tom │ 30 │ │ 4 │ mary │ 30 │ │ 5 │ ada │ 40 │ │ 6 │ andrew │ 40 │ │ 7 │ harry │ 70 │ │ 8 │ ann │ 50 │ └─────────┴────────┴─────────┘
-
roles
CREATE TABLE roles (id Int32, title String) ENGINE = MergeTree ORDER BY id;
INSERT INTO roles VALUES (10, 'admin'), (20, 'owner'), (30, 'author'), (40, 'reviewer'), (50, 'editor'), (60, 'view only');
┌─id─┬─title──────┐ │ 10 │ admin │ │ 20 │ owner │ │ 30 │ author │ │ 40 │ reviewer │ │ 50 │ editor │ │ 60 │ view only │ └────┴────────────┘
Таблица users
содержит поле role_id
. Это поле будет ключом, который ссылается на поле id
в таблице roles
и служит для определения связей между таблицами.
Типы JOIN
INNER JOIN
Для каждой строки в левой таблице проверяется, содержит ли правая таблица строку, которая удовлетворяет условию JOIN
. Если соответствующая строка найдена, создается новая строка, содержащая столбцы из обеих таблиц, и добавляется в набор результатов. Если для строки найдено более одного совпадения по указанному условию, то в результирующую таблицу записываются все совпадения (создается декартово произведение для строк, совпадающих по условию соединения).
Ключевое слово INNER
в запросе можно опустить.
SELECT users.name AS user, roles.title AS role FROM users INNER JOIN roles ON users.role_id=roles.id;
Запрос соединяет таблицы users
и roles
и возвращает строки, для которых выполняется условие соединения по идентификатору роли, назначенной пользователю. Пользователь harry
не включен в результат, так как для идентификатора назначенной ему роли не найдена соответствующая строка в таблице roles
. В результат также не входит роль view only
, так как она не назначена ни одному пользователю.
┌─user───┬─role─────┐ │ john │ admin │ │ mike │ owner │ │ tom │ author │ │ mary │ author │ │ ada │ reviewer │ │ andrew │ reviewer │ │ ann │ editor │ └────────┴──────────┘
OUTER JOIN
Запросы LEFT/RIGHT/FULL OUTER JOIN
позволяют расширить INNER JOIN
, добавляя в результаты выборки строки из исходных таблиц, не имеющие соответствий по указанному условию соединения. Ключевое слово OUTER
в запросах можно опускать.
-
LEFT OUTER JOIN
сначала выполняет операциюINNER JOIN
. Затем каждая строка левой таблицы, которая не вошла в результатINNER JOIN
, добавляется к результатуLEFT OUTER JOIN
со значениями по умолчанию для соответствующих типов данных в столбцах правой таблицы. Таким образом, в объединенной таблице всегда есть по крайней мере одна строка для каждой строки левой таблицы.ПримерSELECT users.name AS user, roles.title AS role FROM users LEFT OUTER JOIN roles ON users.role_id=roles.id;
Результат содержит названия ролей для всех пользователей из таблицы
users
, в том числе включает строку с пользователемharry
, роль которого не была найдена в таблицеroles
по идентификатору роли. Столбецroles.title
с псевдонимомrole
для этой строки содержит пустую строку — значение по умолчанию для типаString
:┌─user───┬─role─────┐ │ john │ admin │ │ mike │ owner │ │ tom │ author │ │ mary │ author │ │ ada │ reviewer │ │ andrew │ reviewer │ │ harry │ │ │ ann │ editor │ └────────┴──────────┘
-
RIGHT OUTER JOIN
работает аналогичным образом, но добавляет к результатуINNER JOIN
строки из правой таблицы, которые не вошли в результатINNER JOIN
, со значениями по умолчанию в столбцах левой таблицы. В объединенной таблице всегда есть по крайней мере одна строка для каждой строки правой таблицы.ПримерSELECT users.name AS user, roles.title AS role FROM users RIGHT OUTER JOIN roles ON users.role_id=roles.id;
Запрос возвращает все совпадения строк таблиц
users
иroles
по идентификатору роли, а также строку для ролиview only
, которая не назначена ни одному пользователю (в столбцеuser
пустая строка):┌─user───┬─role─────┐ │ john │ admin │ │ mike │ owner │ │ tom │ author │ │ mary │ author │ │ ada │ reviewer │ │ andrew │ reviewer │ │ ann │ editor │ └────────┴──────────┘ ┌─user─┬─role──────┐ │ │ view only │ └──────┴───────────┘
-
FULL OUTER JOIN
комбинируетLEFT OUTER JOIN
иRIGHT OUTER JOIN
— возвращает результатINNER JOIN
, а также значения несовпадающих строк обеих таблиц, заполняя пустые ячейки значениями по умолчанию.ПримерSELECT users.name AS user, roles.title AS role FROM users FULL OUTER JOIN roles ON users.role_id=roles.id;
В результат включены все пользователи из таблицы
users
(в том числе пользовательharry
, роль которого не определена в таблицеroles
), а также рольview only
из таблицыroles
, которая не назначена ни одному пользователю:┌─user───┬─role─────┐ │ john │ admin │ │ mike │ owner │ │ tom │ author │ │ mary │ author │ │ ada │ reviewer │ │ andrew │ reviewer │ │ harry │ │ │ ann │ editor │ └────────┴──────────┘ ┌─user─┬─role──────┐ │ │ view only │ └──────┴───────────┘
Чтобы вместо значений по умолчанию в пустых ячейках использовать значения NULL (как в стандартном SQL), нужно изменить значение настройки join_use_nulls.
CROSS JOIN
Запрос CROSS JOIN
комбинирует каждую строку левой таблицы с каждой строкой правой таблицы (создает декартово произведение). Набор столбцов объединенной таблицы включает все столбцы левой таблицы, за которыми следуют все столбцы правой таблицы. Если в таблицах есть N
и M
строк соответственно, то в объединенной таблице будет N * M
строк.
CROSS JOIN
эквивалентен FROM <left_table> INNER JOIN <right_table> ON TRUE
или FROM <left_table>, <right_table>
.
SELECT * FROM users CROSS JOIN roles;
Результирующая таблица содержит все возможные комбинации строк из таблиц users
и roles
:
┌─user_id─┬─name───┬─role_id─┬─id─┬─title──────┐ │ 1 │ john │ 10 │ 10 │ admin │ │ 1 │ john │ 10 │ 20 │ owner │ │ 1 │ john │ 10 │ 30 │ author │ │ 1 │ john │ 10 │ 40 │ reviewer │ │ 1 │ john │ 10 │ 50 │ editor │ │ 1 │ john │ 10 │ 60 │ view only │ │ 2 │ mike │ 20 │ 10 │ admin │ │ 2 │ mike │ 20 │ 20 │ owner │ │ 2 │ mike │ 20 │ 30 │ author │ │ 2 │ mike │ 20 │ 40 │ reviewer │ │ 2 │ mike │ 20 │ 50 │ editor │ │ 2 │ mike │ 20 │ 60 │ view only │ │ 3 │ tom │ 30 │ 10 │ admin │ │ 3 │ tom │ 30 │ 20 │ owner │ │ 3 │ tom │ 30 │ 30 │ author │ │ 3 │ tom │ 30 │ 40 │ reviewer │ │ 3 │ tom │ 30 │ 50 │ editor │ │ 3 │ tom │ 30 │ 60 │ view only │ │ 4 │ mary │ 30 │ 10 │ admin │ │ 4 │ mary │ 30 │ 20 │ owner │ │ 4 │ mary │ 30 │ 30 │ author │ │ 4 │ mary │ 30 │ 40 │ reviewer │ │ 4 │ mary │ 30 │ 50 │ editor │ │ 4 │ mary │ 30 │ 60 │ view only │ │ 5 │ ada │ 40 │ 10 │ admin │ │ 5 │ ada │ 40 │ 20 │ owner │ │ 5 │ ada │ 40 │ 30 │ author │ │ 5 │ ada │ 40 │ 40 │ reviewer │ │ 5 │ ada │ 40 │ 50 │ editor │ │ 5 │ ada │ 40 │ 60 │ view only │ │ 6 │ andrew │ 40 │ 10 │ admin │ │ 6 │ andrew │ 40 │ 20 │ owner │ │ 6 │ andrew │ 40 │ 30 │ author │ │ 6 │ andrew │ 40 │ 40 │ reviewer │ │ 6 │ andrew │ 40 │ 50 │ editor │ │ 6 │ andrew │ 40 │ 60 │ view only │ │ 7 │ harry │ 70 │ 10 │ admin │ │ 7 │ harry │ 70 │ 20 │ owner │ │ 7 │ harry │ 70 │ 30 │ author │ │ 7 │ harry │ 70 │ 40 │ reviewer │ │ 7 │ harry │ 70 │ 50 │ editor │ │ 7 │ harry │ 70 │ 60 │ view only │ │ 8 │ ann │ 50 │ 10 │ admin │ │ 8 │ ann │ 50 │ 20 │ owner │ │ 8 │ ann │ 50 │ 30 │ author │ │ 8 │ ann │ 50 │ 40 │ reviewer │ │ 8 │ ann │ 50 │ 50 │ editor │ │ 8 │ ann │ 50 │ 60 │ view only │ └─────────┴────────┴─────────┴────┴────────────┘
LEFT/RIGHT SEMI JOIN
Запрос LEFT SEMI JOIN
возвращает значения столбцов для каждой строки из левой таблицы, у которой есть хотя бы одно совпадение по условию соединения в правой таблице. Если для строки найдено более одного совпадения, возвращается только первое найденное совпадение (декартово произведение отключено).
RIGHT SEMI JOIN
работает аналогично — возвращает значения столбцов для всех строк из правой таблицы, которые имеют хотя бы одно совпадение по условию соединения с левой таблицей. Если для строки найдено несколько совпадений, в результат добавляется только первое найденное совпадение.
SELECT roles.title AS role, users.name AS user FROM users RIGHT SEMI JOIN roles ON users.role_id=roles.id;
В результате выполнения запроса RIGHT SEMI JOIN
для каждой роли из правой таблицы roles
возвращается только один пользователь, которому эта роль назначена. Роль view only
, для которой не найдено ни одного совпадения в таблице users
, не включается в результирующую таблицу:
┌─role─────┬─user─┐ │ admin │ john │ │ owner │ mike │ │ author │ tom │ │ reviewer │ ada │ │ editor │ ann │ └──────────┴──────┘
LEFT/RIGHT ANTI JOIN
LEFT ANTI JOIN
возвращает значения столбцов для всех строк левой таблицы, не имеющих совпадений со строками правой таблицы по условию соединения. Аналогично работает RIGHT ANTI JOIN
— возвращает значения столбцов для строк правой таблицы, для которых не найдены соответствия в левой таблице.
SELECT users.name AS user, roles.title AS role FROM users LEFT ANTI JOIN roles ON users.role_id=roles.id;
В результате выполнения запроса LEFT ANTI JOIN
возвращаются данные только для пользователя harry
, роль которого не найдена в таблице roles
по указанному идентификатору:
┌─user──┬─role─┐ │ harry │ │ └───────┴──────┘
LEFT/RIGHT/INNER ANY JOIN
Запрос LEFT ANY JOIN
возвращает значения столбцов каждой строки левой таблицы, соединяя их либо со значениями столбцов соответствующей по условию соединения строки правой таблицы, либо со значениями по умолчанию в столбцах правой таблицы (если совпадения по условию нет). Если строка левой таблицы имеет более одного совпадения с правой таблицей, возвращаются значения столбцов только для первого найденного совпадения. Запрос RIGHT ANY JOIN
работает по аналогии для строк правой таблицы.
Иными словами, ключевое слово ANY
указывает на строгость операции JOIN
— декартово произведение для совпадающих по условию соединения строк исходных таблиц не создается, то есть в результат попадает только одно из найденных совпадений для каждой строки. По умолчанию применяется строгость ALL
— все совпадающие строки исходных таблиц соединяются и записываются в результирующую таблицу (см. примеры запросов LEFT/RIGHT OUTER JOIN и INNER JOIN выше). То есть LEFT/RIGHT ANY JOIN
соединяет таблицы как LEFT/RIGHT OUTER JOIN
, а INNER ANY JOIN
как INNER JOIN
, но с отключенным декартовым произведением.
Значение строгости по умолчанию можно регулировать с помощью настройки join_default_strictness.
SELECT users.name AS user, roles.title AS role FROM users RIGHT ANY JOIN roles ON users.role_id=roles.id;
Запрос RIGHT ANY JOIN
возвращает для каждой роли из правой таблицы roles
только одного пользователя (первого найденного), которому эта роль назначена, а также роль view only
, которая не назначена ни одному пользователю:
┌─user─┬─role─────┐ │ john │ admin │ │ mike │ owner │ │ tom │ author │ │ ada │ reviewer │ │ ann │ editor │ └──────┴──────────┘ ┌─user─┬─role──────┐ │ │ view only │ └──────┴───────────┘
ASOF JOIN
Запросы ASOF JOIN
и LEFT ASOF JOIN
применяются, когда необходимо объединять записи, которые не имеют точного совпадения. Если строка из левой таблицы не имеет точного соответствия с какой-либо строкой в правой таблице, то в качестве соответствующей принимается строка правой таблицы, наиболее близкая по дополнительному условию — условию ближайшего совпадения, которое указывается на основе специального столбца (типа Int, UInt, Float, Date, DateTime или Decimal) в таблицах.
Синтаксис запроса в общем виде:
-
ASOF JOIN … ON
:<left_table> [LEFT] ASOF JOIN <right_table> ON <exact_match_condition> AND <closest_match_condition>
где:
-
<exact_match_condition>
— условие для соединения по равенству (может быть задано несколько условий); -
<closest_match_condition>
— условие для соединения по ближайшему совпадению (должно быть задано одно такое условие). Условия для проверки на ближайшее совпадение поддерживают операторы>
,>=
,<
,<=
.
-
-
ASOF JOIN … USING
:<left_table> [LEFT] ASOF JOIN <right_table> USING <exact_match_column1>, ... <exact_match_columnN>, <closest_match_column>
где
-
<exact_match_column1>, … <exact_match_columnN>
— столбцы для соединения по равенству значений; -
<closest_match_column>
— столбец (последний в спискеUSING
) для соединения по ближайшему совпадению. Условие соединения:<left_table>.<closest_match_column> >= <right_table>.<closest_match_column>
.
-
Создайте две таблицы для тестовых примеров:
-
table1
CREATE TABLE table1 (id Int32, time DateTime, text String) ENGINE = MergeTree ORDER BY id;
INSERT INTO table1 VALUES (50, '2023-03-10 14:55:00', 'text1_0'), (50, '2023-03-10 15:00:00', 'text1_1'), (50, '2023-03-10 15:03:00', 'text1_2'), (50, '2023-03-10 15:10:00', 'text1_3'), (50, '2023-03-10 15:14:00', 'text1_4');
┌─id─┬────────────────time─┬─text────┐ │ 50 │ 2023-03-10 14:55:00 │ text1_0 │ │ 50 │ 2023-03-10 15:00:00 │ text1_1 │ │ 50 │ 2023-03-10 15:03:00 │ text1_2 │ │ 50 │ 2023-03-10 15:10:00 │ text1_3 │ │ 50 │ 2023-03-10 15:14:00 │ text1_4 │ └────┴─────────────────────┴─────────┘
-
table2
CREATE TABLE table2 (id Int32, time DateTime, text String) ENGINE = MergeTree ORDER BY id;
INSERT INTO table2 VALUES (50, '2023-03-10 15:00:00', 'text2_1'), (50, '2023-03-10 15:07:00', 'text2_2'), (50, '2023-03-10 15:11:00', 'text2_3'), (50, '2023-03-10 15:20:00', 'text2_4');
┌─id─┬────────────────time─┬─text────┐ │ 50 │ 2023-03-10 15:00:00 │ text2_1 │ │ 50 │ 2023-03-10 15:07:00 │ text2_2 │ │ 50 │ 2023-03-10 15:11:00 │ text2_3 │ │ 50 │ 2023-03-10 15:20:00 │ text2_4 │ └────┴─────────────────────┴─────────┘
Выполните запрос ASOF LEFT JOIN
:
SELECT
table1.text AS table1_text,
table1.time AS table1_time,
table2.text AS table2_text,
table2.time AS table2_time
FROM table1
ASOF LEFT JOIN table2 ON (table1.id = table2.id) AND (table1.time >= table2.time);
В результирующей выборке каждой строке таблицы table1
соответствует строка таблицы table2
с наиболее близким значением в столбце time
по условию table1.time >= table2.time
. Для первой строки (text1_0
) подходящей строки в таблице table2
не найдено, поэтому в столбцах table2_text
и table2_time
содержатся значения по умолчанию для соответствующих типов:
┌─table1_text─┬─────────table1_time─┬─table2_text─┬─────────table2_time─┐ │ text1_0 │ 2023-03-10 14:55:00 │ │ 1970-01-01 00:00:00 │ │ text1_1 │ 2023-03-10 15:00:00 │ text2_1 │ 2023-03-10 15:00:00 │ │ text1_2 │ 2023-03-10 15:03:00 │ text2_1 │ 2023-03-10 15:00:00 │ │ text1_3 │ 2023-03-10 15:10:00 │ text2_2 │ 2023-03-10 15:07:00 │ │ text1_4 │ 2023-03-10 15:14:00 │ text2_3 │ 2023-03-10 15:11:00 │ └─────────────┴─────────────────────┴─────────────┴─────────────────────┘
Выполните тот же ASOF LEFT JOIN
запрос с выражением USING
:
SELECT
table1.text AS table1_text,
table1.time AS table1_time,
table2.text AS table2_text,
table2.time AS table2_time
FROM table1
ASOF LEFT JOIN table2 USING (id, time);
В результате получается та же объединенная таблица:
┌─table1_text─┬─────────table1_time─┬─table2_text─┬─────────table2_time─┐ │ text1_0 │ 2023-03-10 14:55:00 │ │ 1970-01-01 00:00:00 │ │ text1_1 │ 2023-03-10 15:00:00 │ text2_1 │ 2023-03-10 15:00:00 │ │ text1_2 │ 2023-03-10 15:03:00 │ text2_1 │ 2023-03-10 15:00:00 │ │ text1_3 │ 2023-03-10 15:10:00 │ text2_2 │ 2023-03-10 15:07:00 │ │ text1_4 │ 2023-03-10 15:14:00 │ text2_3 │ 2023-03-10 15:11:00 │ └─────────────┴─────────────────────┴─────────────┴─────────────────────┘
Условие JOIN
JOIN
объединяет строки двух таблиц на основе связующих столбцов между ними — ключей соединения. Ключи, а также дополнительные условия для соединения, указываются для оператора JOIN
с помощью выражений ON
или USING
.
В запросах CROSS JOIN условие не указывается.
ON
ON
возвращает значение boolean. Строка из одной таблицы соответствует строке из другой таблицы, если выражение ON
возвращает true
.
Можно указать несколько условий, связанных операторами AND
и/или OR
— в этом случае, строки будут объединяться, когда выполнено все составное условие. Если условие не выполняется, то строки все равно могут попасть в результат в зависимости от типа JOIN
.
Условие, определяющее ключ соединения, задается как сравнение столбцов левой и правой таблицы через оператор равенства (=
). Прочие условия могут использовать другие логические операторы, и каждое отдельное условие задается только для столбца одной из таблиц.
В следующем запросе указаны два условия JOIN
:
-
users.role_id=roles.id
— ключ соединения; -
roles.id>20
— дополнительное условие для таблицыroles
.
SELECT
users.name AS user,
roles.title AS role,
roles.id AS role_id
FROM users
LEFT JOIN roles ON users.role_id = roles.id AND roles.id > 20;
Результат:
┌─user───┬─role─────┬─role_id─┐ │ john │ │ 0 │ │ mike │ │ 0 │ │ tom │ author │ 30 │ │ mary │ author │ 30 │ │ ada │ reviewer │ 40 │ │ andrew │ reviewer │ 40 │ │ harry │ │ 0 │ │ ann │ editor │ 50 │ └────────┴──────────┴─────────┘
USING
Если столбцы, по которым сопоставляются таблицы, имеют одинаковые имена в обеих таблицах, то вместо выражения ON
можно использовать USING
. В качестве параметра выражения USING
можно указать один или несколько столбцов (список имен столбцов, разделенных запятыми), равенство которых будет считаться условием для соединения.
Результат выражения JOIN
с условием USING
будет содержать один столбец для каждой из перечисленных пар столбцов в указанном порядке, за которым следуют все оставшиеся столбцы из левой и правой таблиц.
Создайте таблицы для примера:
-
test_table1
CREATE TABLE test_table1 (num Int32, name String) ENGINE = MergeTree ORDER BY num;
INSERT INTO test_table1 VALUES (1, 'a'), (2, 'b'), (3, 'c');
-
test_table2
CREATE TABLE test_table2 (num Int32, value String) ENGINE = MergeTree ORDER BY num;
INSERT INTO test_table2 VALUES (0, 'value1'), (1, 'value2'), (2, 'value3');
test_table1 test_table2 ┌─num─┬─name─┐ ┌─num─┬─value──┐ | 1 | a | │ 0 │ value1 │ | 2 | b | │ 1 │ value2 │ | 3 | c | │ 2 │ value3 │ └─────┴──────┘ └─────┴────────┘
Выполните запрос, используя выражение USING
для оператора JOIN
, чтобы объединить таблицы test_table2
и test_table2
по столбцу num
:
SELECT * FROM test_table1 INNER JOIN test_table2 USING num;
Результат:
┌─num─┬─name─┬─value──┐ │ 1 │ a │ value2 │ │ 2 │ b │ value3 │ └─────┴──────┴────────┘