Оператор 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 работает аналогично — возвращает значения столбцов для всех строк из правой таблицы, которые имеют хотя бы одно совпадение по условию соединения с левой таблицей. Если для строки найдено несколько совпадений, в результат добавляется только первое найденное совпадение.

Пример 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 — возвращает значения столбцов для строк правой таблицы, для которых не найдены соответствия в левой таблице.

Пример LEFT 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.

Пример RIGHT ANY JOIN
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>.

Пример LEFT ASOF JOIN

 
Создайте две таблицы для тестовых примеров:

  • 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 │
└─────┴──────┴────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней