FirebirdSQL logo
 COMMENTSОператоры процедурного SQL (PSQL) 
Соединения именованными столбцами

Эквисоединения часто сравнивают столбцы, которые имеют одно и то же имя в обеих таблицах.Для таких соединений мы можем использовать второй тип явных соединений, называемый соединением именованными столбцами (Named Columns Joins). Соединение именованными столбцами осуществляются с помощью предложения USING, в котором перечисляются только имена столбцов.

Note

Соединения именованными столбцами доступны только в диалекте 3.

Таким образом, следующий пример:

SELECT *
FROM flotsam f
  JOIN jetsam j
    ON f.sea = j.sea AND f.ship = j.ship

можно переписать так:

SELECT *
FROM flotsam
JOIN jetsam USING (sea, ship)

что значительно короче.Результирующий набор несколько отличается, по крайней мере, при использовании "SELECT *":

  • Результат соединения с явным условием соединения в предложении ON будет содержать каждый из столбцов SEA и `SHIP дважды: один раз для таблицы FLOTSAM и один раз для таблицы JETSAM. Очевидно, что они будут иметь они и те же значения;

  • Результат соединения именованными столбцами, с помощью предложения USING, будет содержать эти столбцы один раз.

Если вы хотите получить в результате соединения именованными столбцами все столбцы, перепишите запрос следующим образом:

SELECT f.*, j.*
FROM flotsam f
JOIN jetsam j USING (sea, ship)

Для внешних (OUTER) соединений именованными столбцами, существуют дополнительные нюансы, при использовании “SELECT *” или неполного имени столбца.Если столбец строки из одного источника не имеет совпадений со столбцом строки из другого источника, но все равно должен быть включён результат из-за инструкций LEFT, RIGHT или FULL, то объединяемый столбец получит не NULL значение.Это достаточно справедливо, но теперь вы не можете сказать из какого набора левого, правого или обоих пришло это значение.Это особенно обманывает, когда значения пришли из правой части набора данных, потому что “*” всегда отображает для комбинированных столбцов значения из левой части набора данных, даже если используется RIGHT соединение.

Является ли это проблемой, зависит от ситуации.Если это так, используйте “f.*, j.*” подход, продемонстрированный выше, где f и j имена или алиасы двух источников.Или лучше вообще избегать “*” в серьёзных запросах и перечислять все имена столбцов для соединяемых множеств.Такой подход имеет дополнительное преимущество, заставляя вас думать, о том какие данные вы хотите получить и откуда.

Вся ответственность за совместимость типов столбцов между соединяемыми источниками, имена которых перечислены в предложении USING, лежит на вас.Если типы совместимы, но не равны, то Firebird преобразует их в тип с более широким диапазоном значений перед сравнением.Кроме того, это будет типом данных объединённого столбца, который появится в результирующем наборе, если используются “SELECT *” или неполное имя столбца.Полные имена столбцов всегда будут сохранять свой первоначальный тип данных.

Tip

Если при соединении именованными столбцами вы используете столбцы соединения в условии отбора WHERE, то всегда используйте уточнённые имена столбцов.В противном случае индекс по этому столбцу не будет задействован.

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE x = 0;
PLAN JOIN (A NATURAL, B INDEX (RDB$2))

однако

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE a.x = 0; -- или 'b.x'
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

SELECT 1 FROM t1 a JOIN t2 b USING(x) WHERE b.x = 0;
PLAN JOIN (A INDEX (RDB$1), B INDEX (RDB$2))

Дело в том, неуточнённый столбец в данном случае неявно заменяется на COALESCE(a.x, b.x).Этот хитрый трюк применяется для устранения неоднозначности имён столбцов, но он же мешает применению индекса.

Естественные соединения (NATURAL JOIN)

Взяв за основу соединения именованными столбцами, следующим шагом будет естественное соединение, которое выполняет эквисоединение по всем одноименным столбцам правой и левой таблицы.Типы данных этих столбцов должны быть совместимыми.

Note

Естественные соединения доступны только в диалекте 3.
Синтаксис
<natural-join> ::= NATURAL [<join-type>] JOIN <source>

<join-type> ::= INNER | {LEFT | RIGHT | FULL} [OUTER]

Даны две таблицы:

CREATE TABLE TA (
    a BIGINT,
    s VARCHAR(12),
    ins_date DATE
);

CREATE TABLE TB (
    a BIGINT,
    descr VARCHAR(12),
    x FLOAT,
    ins_date DATE
);

Естественное соединение таблиц TA и TB будет происходить по столбцам a и ins_date и два следующих оператора дадут один и тот же результат:

SELECT *
FROM TA
NATURAL JOIN TB;

SELECT *
FROM TA
JOIN TB USING (a, ins_date);

Как и все соединения, естественные соединения являются внутренними соединениями по умолчанию, но вы можете превратить их во внешние соединения, указав LEFT, RIGHT или FULL перед ключевым словом JOIN.

Important
Внимание

Если в двух исходных таблицах не будут найдены одноименные столбцы, то будет выполнен CROSS JOIN.