Выражение `JOIN`

Соединения (JOINS) — это фундаментальная реляционная операция, используемая для горизонтального соединения двух таблиц или отношений. Отношения называются левая и правая стороны соединения в зависимости от того, как они записаны в выражении JOIN. Каждая строка результата содержит столбцы из обоих отношений.

В соединении используется правило для сопоставления пар строк из каждого отношения. Часто это предикат, но могут быть указаны и другие правила.

Синтаксис

Могут использоваться следующие варианты синтаксиса:

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
[ ... ]

SELECT ...
FROM <object_ref1> [
                     {
                       | NATURAL [ { LEFT | RIGHT | FULL } [ OUTER ] ]
                       | CROSS
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Параметры

<object_ref1> и <object_ref2>
Каждый <object_ref> представляет собой таблицу или источник данных, подобный таблице.

JOIN
Ключевое слово JOIN, указывающее, что таблицы должны быть соединены. JOIN комбинируется с другими ключевыми словами (например, INNER или OUTER), чтобы указать тип соединения.

ON <condition>
Булево выражение, которое определяет условия соединения.
Подробное описание см. в разделе Условное соединение.

USING <column_list>
Список столбцов, по которым производится соединение. Столбцы должны быть одноименными в соединяемых таблицах.
Подробное описание см. в разделе Условное соединение.

`OUTER JOIN` — внешнее соединение

Строки, не имеющие совпадений, могут быть возвращены, если указано соединение OUTER. Внешние соединения могут иметь один из типов:

LEFT (Все строки из левого отношения появляются хотя бы один раз)
RIGHT (Все строки из правого отношения встречаются хотя бы один раз)
FULL (Все строки из обоих отношений появляются хотя бы один раз)

Соединение, которое не является внешним (OUTER), является внутренним (INNER) — возвращаются только те строки, которые удовлетворяют условию.

Если возвращается непарная строка, атрибуты другой таблицы устанавливаются в значение NULL.

`CROSS JOIN` — перекрёстное соединение (декартово произведение)

Простейшим типом соединения является CROSS JOIN. Для этого типа соединения нет никаких условий, и он просто возвращает все возможные пары.

Вернем все пары строк:

SELECT a.*, b.*
FROM a
CROSS JOIN b;

Это эквивалентно запросу, в котором просто опущено условие JOIN:

SELECT a.*, b.*
FROM a, b;

Условное соединение

Большинство соединений задается предикатом, который соединяет атрибуты с одной стороны с атрибутами с другой стороны. Условия могут быть явно указаны с помощью операторов ON и WHERE.

Покажем использование условных соединений на примере двух таблиц — capitals со странами и их столицами и population со странами и их населением (в миллионах).

CREATE TABLE capitals (cap_country VARCHAR, capital VARCHAR);
CREATE TABLE population (pop_country VARCHAR, population_mil BIGINT);
INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');
INSERT INTO population VALUES
    ('Russia', 143),
    ('Spain', 48),
    ('Brazil', 211);

Выведем страны с их столицей и населением, используя JOIN с оператором ON:

SELECT *
FROM capitals
JOIN population ON (cap_country = pop_country);

+-------------+---------+-------------+----------------+
| cap_country | capital | pop_country | population_mil |
+-------------+---------+-------------+----------------+
| Russia      | Moscow  | Russia      | 143            |
+-------------+---------+-------------+----------------+
| Spain       | Madrid  | Spain       | 48             |
+-------------+---------+-------------+----------------+

Теперь выведем те же данные, используя JOIN с оператором WHERE:

SELECT t1.*, t2.*
FROM capitals t1, population t2
WHERE t1.cap_country = t2.pop_country

+-------------+---------+-------------+----------------+
| cap_country | capital | pop_country | population_mil |
+-------------+---------+-------------+----------------+
| Russia      | Moscow  | Russia      | 143            |
+-------------+---------+-------------+----------------+
| Spain       | Madrid  | Spain       | 48             |
+-------------+---------+-------------+----------------+

С оператором WHERE можно использовать не только равенство, но и другие предикаты.

Если имена столбцов в двух таблицах одинаковы и значения в них должны быть равны, то можно использовать более простой синтаксис USING.

Зададим те же таблицы, но с одноименными столбцами country:

CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
CREATE TABLE population (country VARCHAR, population_mil BIGINT);
INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');
INSERT INTO population VALUES
    ('Russia', 143),
    ('Spain', 48),
    ('Brazil', 211);

Теперь выведем страны с их столицей и населением через более простой запрос с USING:

SELECT *
FROM capitals
JOIN population USING (country);

+---------+---------+----------------+
| country | capital | population_mil |
+---------+---------+----------------+
| Russia  | Moscow  | 143            |
+---------+---------+----------------+
| Spain   | Madrid  | 48             |
+---------+---------+----------------+

`NATURAL JOIN` — естественное соединение

Естественные соединения объединяют две таблицы на основе столбцов с одинаковыми именами.

Покажем использование такого типа соединения на примере той же пары таблиц с одноименными столбцами country.

Чтобы объединить таблицы по их общему столбцу, выполним команду:

SELECT *
FROM capitals
NATURAL JOIN population;

+---------+---------+---------+----------------+
| country | capital | country | population_mil |
+---------+---------+---------+----------------+
| Russia  | Moscow  | Russia  | 143            |
+---------+---------+---------+----------------+
| Spain   | Madrid  | Spain   | 48             |
+---------+---------+---------+----------------+

Обратите внимание, что в результат были включены только строки, в которых в обеих таблицах присутствовал один и тот же атрибут country.

Мы можем выполнить аналогичный запрос с помощью выражения JOIN с ключевым словом USING:

SELECT *
FROM capitals
JOIN population
USING (country);

+---------+---------+----------------+
| country | capital | population_mil |
+---------+---------+----------------+
| Russia  | Moscow  | 143            |
+---------+---------+----------------+
| Spain   | Madrid  | 48             |
+---------+---------+----------------+

Обратите внимание, что в таком случае одноименные колонки из разных таблиц "схлопнутся" в одну.

`SEMI JOIN` и `ANTI JOIN` — полусоединение и антисоединение

Полусоединения возвращают строки из левой таблицы, которые имеют хотя бы одно совпадение в правой таблице.

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

При использовании полу- или антисоединений результат никогда не будет содержать больше строк, чем в левой таблице. Полусоединения обеспечивают ту же логику, что и оператор IN. Антисоединения обеспечивают ту же логику, что и оператор NOT IN, за исключением того, что антисоединения игнорируют значения NULL из правой таблицы.

Пример полусоединения

Выведем список таких пар "страна-столица" из таблицы capitals, для которых название страны присутствует в таблице population:

SELECT *
FROM capitals
SEMI JOIN population
    USING (country);

+---------+---------+
| country | capital |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

Этот запрос эквивалентен следующему:

SELECT *
FROM capitals
WHERE country IN (SELECT country FROM population);

+---------+---------+
| country | capital |
+---------+---------+
| Russia  | Moscow  |
+---------+---------+
| Spain   | Madrid  |
+---------+---------+

Пример антисоединения

Выведем список таких пар "страна-столица" из таблицы capitals, для которых названия страны нет в таблице population:

SELECT *
FROM capitals
ANTI JOIN population
    USING (country);

+---------+---------+
| country | capital |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| France  | Paris   |
+---------+---------+

Этот запрос эквивалентен следующему:

SELECT *
FROM capitals
WHERE country NOT IN
    (SELECT country FROM population WHERE country IS NOT NULL);

+---------+---------+
| country | capital |
+---------+---------+
| Italy   | Rome    |
+---------+---------+
| France  | Paris   |
+---------+---------+

`LATERAL` — латеральное соединение

Ключевое слово LATERAL позволяет подзапросам в выражении FROM ссылаться на предыдущие подзапросы.

SELECT *
FROM range(5) t1(num), LATERAL (SELECT num*-1) t2(num_neg);

+-----+---------+
| num | num_neg |
+-----+---------+
| 0   | 0       |
+-----+---------+
| 1   | -1      |
+-----+---------+
| 4   | -4      |
+-----+---------+
| 3   | -3      |
+-----+---------+
| 2   | -2      |
+-----+---------+

Запрос SELECT возвращает строки в произвольном порядке, если в запросе порядок не задан явно, например, через Выражение ORDER BY.

Латеральные соединения являются обобщением коррелированных подзапросов, поскольку они могут возвращать несколько значений для одного входного значения, а не одно значение.

SELECT *
FROM
    range(1,4) t1(num),
    LATERAL (SELECT num*-1 UNION ALL SELECT num + 10) t2(from_num);

+-----+----------+
| num | from_num |
+-----+----------+
| 2   | -2       |
+-----+----------+
| 1   | -1       |
+-----+----------+
| 3   | -3       |
+-----+----------+
| 2   | 12       |
+-----+----------+
| 3   | 13       |
+-----+----------+
| 1   | 11       |
+-----+----------+

Возможно, будет полезно представить LATERAL как цикл, в котором мы перебираем строки первого подзапроса и используем их в качестве входных данных для второго (LATERAL) подзапроса. В приведенных выше примерах мы выполняем итерации по таблице t1 и ссылаемся на ее столбец num из определения таблицы t2. Значения из таблицы t2 образуют второй столбец в результирующей таблице.

Замкнутое соединение (Self-Join)

Tengri позволяет использовать замкнутое соединение (соединение таблицы с самой собой) для всех типов соединений. Обратите внимание, что таблицы должны быть указаны через псевдонимы, использование одного и того же имени таблицы без псевдонимов приведет к ошибке:

CREATE TABLE t (x INTEGER);
SELECT * FROM t JOIN t USING(x);

Binder Error:
Duplicate alias "t" in query!

Добавление псевдонимов позволяет успешно обработать запрос:

CREATE TABLE t (num INTEGER);
SELECT * FROM t t1 JOIN t t2 USING(num);

+-----+
| num |
+-----+

Выражение JOIN