7.2.1.1. Соединённые таблицы

Соединённая таблица — это таблица, производная от двух других (реальных или в свою очередь производных) таблиц, полученая в соответствии с правилами определённого типа соединения. Доступные типы соединения: inner (внутреннее), outer (внешнее) и cross (перекрёсное).

Типы соединений

Перекрёстное соединение (cross join)

T1 CROSS JOIN T2

Для каждой комбинации строк из T1 и T2 (декартово произведение) соединённая таблица будет содержать строки, состоящие из всех колонок таблицы T1, за которым следуют все колонки из таблицы T2. Если таблицы имеют соответственно N и M строк, соединённая таблица будет иметь N * M строк.

FROM T1 CROSS JOIN T2 эквивалентно FROM T1, T2. Также это эквивалентно FROM T1 INNER JOIN T2 ON TRUE (см. ниже).

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

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON логическое_выражение

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( список_колонок_для_соединения )

T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

Слова INNER и OUTER являются необязательными во всех формах. INNER применяется по умолчанию; LEFT, RIGHT иFULL неявно указывают на внешнее (outer) соединение.

Условие соединения задаётся в предложениях ON или USING, или неявно, с помощью слова NATURAL. Условие соединения определяет какие строки из двух исходных таблиц будут считаться "совпавшими", как ниже описывается в деталях.

Предложение ON является наиболее обычным способом задать условие соединения: в нём указывается логическое выражение того же типа, что используется в предложении WHERE. Пара строк из T1 и T2 считается совпавшей, если для неё, выражение, заданное в ON принимает значение истина.

USING имеет более краткую форму: в ней указывается разделённый запятыми список имён колонок, которые должны быть общими в соединяемых таблицах и условия соединения, задавая равенство каждой из этих пар колонок. Таким образом, вывод JOIN USING будет состоять из одной колонки для каждой, являющейся равной, пары входных колонок, за которыми следует остальные колонки из каждой таблицы. Так, USING (a, b, c)эквивалентно ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) с тем исключением, что если ON используется там, где в результате будут две колонки a, b и c, то USING там, где будет только одна из них (и они будут выданы первыми, если используется SELECT *).

Наконец, NATURAL — это краткая форма USING: она формирует список USING, содержащий все имена колонок, которые есть в обеих входных таблицах. Как и с USING, эти колонки появляются в выходной таблице только один раз. Если общих колонок нет, NATURAL ведёт себя как CROSS JOIN.

Возможные типы квалифицированных соединений:

INNER JOIN

Для каждой строки R1 из таблицы T1, соединённая таблица будет иметь строку для каждой строки в T2, которая удовлетворяет условию соединения с R1.

LEFT OUTER JOIN

Сперва выполняется INNER JOIN. Затем, для каждой строки в T1, которая не удовлетворяет условию соединения с любой строкой в T2, добавляется соединённая строка с значениями NULL в колонках T2. Таким образом, соединённая таблица всегда имеет по крайней мере одну строку для каждой строки из T1.

RIGHT OUTER JOIN

Сперва выполняется INNER JOIN. Затем, для каждой строки в T2, которая не удовлетворяет условию соединения с любой строкой в T1, добавляется соединенная строка с значениями NULL в колонках T1. Этот тип соединения является обратным по отношению к LEFT JOIN: результирующая таблица всегда будет иметь хотя бы одну строку для каждой строки из T2.

FULL OUTER JOIN

Сперва выполняется INNER JOIN. Затем, для каждой строки в T1, которая не удовлетворяет условию соединения с любой строкой в T2, добавляется соединенная строка с значениями NULL в колонках T2. Также, для каждой строки T2, которая не удовлетворяет условию соединения с любой строкой в T1, добавляется соединённая строка с значениями NULL в колонках T1.

Соединения всех типов могуть быть сцеплены вместе или скомпанованы: одна или обе таблицы T1 и T2 могут быть соединяющимися таблицами. Для управления порядком выполнения соединений, вокруг JOIN могут использоваться круглые скобки. В отсутствие скобок, предложения JOIN компануются слева направо.

Чтобы проиллюстрировать всё вышеизложенное, предположим, что у нас есть две таблицы: t1:

num | name

-----+------

1 | a

2 | b

3 | c

и t2:

num | value

-----+-------

1 | xxx

3 | yyy

5 | zzz

далее мы выполним разные виды соединений:

=> SELECT * FROM t1 CROSS JOIN t2;

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

1 | a | 3 | yyy

1 | a | 5 | zzz

2 | b | 1 | xxx

2 | b | 3 | yyy

2 | b | 5 | zzz

3 | c | 1 | xxx

3 | c | 3 | yyy

3 | c | 5 | zzz

(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

3 | c | 3 | yyy

(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);

num | name | value

-----+------+-------

1 | a | xxx

3 | c | yyy

(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;

num | name | value

-----+------+-------

1 | a | xxx

3 | c | yyy

(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

2 | b | |

3 | c | 3 | yyy

(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);

num | name | value

-----+------+-------

1 | a | xxx

2 | b |

3 | c | yyy

(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

3 | c | 3 | yyy

| | 5 | zzz

(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

2 | b | |

3 | c | 3 | yyy

| | 5 | zzz

(4 rows)

Условие соединения, которое задаётся в ON, также может содержать условия, которые напрямую не относятся к соединению. Это может оказаться полезным для некоторых запросов, но нуждается в осторожном использовании. Например:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

2 | b | |

3 | c | |

(3 rows)

Обратите внимание, что если поместить ограничение в предложение WHERE, то вы получите другой результат:

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';

num | name | num | value

-----+------+-----+-------

1 | a | 1 | xxx

(1 row)

Это потому, что ограничение помещённое в предложение ON обрабатывается перед соединением, в то время как ограничение, помещённое в предложение WHERE обрабатывается после соединения.