FirebirdSQL logo
 COMMENTSОператоры процедурного SQL (PSQL) 

Внутренние (INNER) и внешние (OUTER) соединения

Соединение всегда соединяет строки из двух наборов данных (которые обычно называются “левый” и “правый”). По умолчанию, только строки, удовлетворяющие условию соединения (то есть, которым соответствует хотя бы одна строка из другого набора строк согласно применяемому условию) попадают в результирующий набор данных.Такой тип соединения называется внутренним (INNER JOIN). Поскольку внутреннее соединение является типом соединения по умолчнию, то ключевое слово INNER можно опустить.

Предположим, у нас есть 2 таблицы:

Таблица А
ID S

87

Just some text

35

Silence

Таблица B
CODE X

-23

56.7735

87

416.0

Если мы соединим эти таблицы с помощью вот такого запроса:

SELECT *
FROM A
JOIN B ON A.id = B.code

то результат будет:

ID S CODE X

87

Just some text

87

416.0

То есть, первая строка таблицы А была соединена со второй строкой таблицы B, потому что вместе они удовлетворяют условию соединения “A.id = B.code”. Другие строки не имеют соответствия и поэтому не включаются в соединение.Помните, что умолчанию соединение всегда внутреннее (INNER).

Мы можем сделать это явным, указав тип соединения:

SELECT *
FROM A
INNER JOIN B ON A.id = B.code

но обычно слово INNER опускается.

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

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

ID S CODE X

87

Just some text

87

416.0

87

Just some text

87

-1.0

-23

Don’t know

-23

56.7735

-23

Still don’t know

-23

56.7735

-23

I give up

-23

56.7735

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

Внешнее левое соединение (LEFT OUTER) включает все записи из левого набора данных, и те записи из правого набора, которые удовлетворяют условию соединения.

Внешнее правое соединение (RIGHT OUTER) включает все записи из правого набора данных и те записи из левого набора данных, которые удовлетворяют условию соединения.

Полное внешнее соединение (FULL OUTER) включает все записи из обоих наборов данных.

Во всех внешних соединениях, “дыры” (то есть поля набора данных, в которых нет соответствующей записи) заполняются NULL.

Для обозначения внешнего соединения используются ключевые слова LEFT, RIGHT или FULL с необязательным ключевым словом OUTER.

Рассмотрим различные внешние соединения на примере запросов с указанными выше таблицами A и B:

SELECT *
FROM A
LEFT OUTER JOIN B ON A.id = B.code

то же самое

SELECT *
FROM A
LEFT JOIN B ON A.id = B.code
ID S CODE X

87

Just some text

87

416.0

235

Silence

<null>

<null>

SELECT *
FROM A
RIGHT OUTER JOIN B ON A.id = B.code

то же самое

SELECT *
FROM A
RIGHT JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

SELECT *
FROM A
FULL OUTER JOIN B ON A.id = B.code

то же самое

SELECT *
FROM A
FULL JOIN B ON A.id = B.code
ID S CODE X

<null>

<null>

-23

56.7735

87

Just some text

87

416.0

235

Silence

<null>

<null>

Квалифицированные соеденения

Синтаксис квалифицированного соединения требует указания условия соединения записей.Это условие указывается явно в предложении ON или неявно при помощи предложения USING.

Синтаксис
<qualified-join> ::= [<join-type>] JOIN <source> <join-condition>

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

<join-condition> ::= ON <condition> | USING (<column-list>)
Соединения с явными условиями

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

Довольно часто, это условие — проверка на равенство (или ряд проверок на равенство объединённых оператором AND) использующая оператор "=". Такие соединения называются эквисоединениями.(Примеры в главе Внутренние (INNER) и внешние (OUTER) соединения были эквисоединениями).

Примеры соединений с явными условиями:

/*
 * Выборка всех заказчиков из города Детройт, которые
 * сделали покупку.
 */
SELECT *
FROM customers c
JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Тоже самое, но включает в выборку заказчиков, которые
 * не совершали покупки.
 */
SELECT *
FROM customers c
LEFT JOIN sales s ON s.cust_id = c.id
WHERE c.city = 'Detroit'

/*
 * Для каждого мужчины выбрать женщин, которые выше него.
 * Мужчины, для которых такой женщины не существуют,
 * не будут выключены в выборку.
 */
SELECT
    m.fullname AS man,
    f.fullname AS woman
FROM males m
JOIN females f ON f.height > m.height

/*
 * Выборка всех учеников, их класса и наставника.
 * Ученики без наставника буду включены в выборку.
 * Ученики без класса не будут включены в выборку.
 */
SELECT
    p.firstname,
    p.middlename,
    p.lastname,
    c.name,
    m.name
FROM pupils p
JOIN classes c ON c.id = p.class
LEFT JOIN mentors m ON m.id = p.mentor
Соединения именованными столбцами

Эквисоединения часто сравнивают столбцы, которые имеют одно и то же имя в обеих таблицах.Для таких соединений мы можем использовать второй тип явных соединений, называемый соединением именованными столбцами (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).Этот хитрый трюк применяется для устранения неоднозначности имён столбцов, но он же мешает применению индекса.