Оператор JOIN
JOIN объединяет данные из двух таблиц в соответствии с правилами заданного типа JOIN. В общем виде синтаксис JOIN можно записать следующим образом:
<таблица1> <тип_JOIN> <таблица2> [ <условие_join> ]Операторы JOIN, обрабатывающие совпадения строк, имеют следующий синтаксис:
<таблица1> { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <таблица2> ON <выражение_boolean>
<таблица1> { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <таблица2> USING ( <список_столбцов_join> )
<таблица1> NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN <таблица2>INNER и OUTER являются необязательными опциями во всех формах. Значение по умолчанию — INNER. Однако, если модификатор LEFT, RIGHT или FULL указан без INNER или OUTER, PostgreSQL выполняет запрос как OUTER JOIN.
Условие JOIN определяет, какие строки двух исходных таблиц соответствуют друг другу. Выражения ON и USING задают условие JOIN. Кроме того, ключевое слово NATURAL неявно устанавливает условие JOIN.
Чтобы посмотреть, как различные типы JOIN работают с данными, создадим две таблицы:
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR (100),
cover_id INT
);
INSERT INTO books (title, cover_id) VALUES
('Hyperion', 1),
('1984', 2),
('War and Peace', 3),
('The Time Machine',3),
('Mrs. Dalloway',5 );
CREATE TABLE covers (
id SERIAL PRIMARY KEY,
name VARCHAR (50)
);
INSERT INTO covers (name) VALUES
('Paperback'),
('Hardcover'),
('Hardcover with dust jacket'),
('Leatherette cover');Таблица books содержит поле cover_id. Это поле является внешним ключом, который ссылается на первичный ключ в таблице covers и служит для определения связей между таблицами.
CROSS JOIN
Результирующая таблица CROSS JOIN содержит все возможные комбинации строк из двух таблиц (декартово произведение). Набор столбцов объединенной таблицы включает все столбцы первой таблицы, за которыми следуют все столбцы второй таблицы. Если в таблицах есть N и M строк соответственно, то в объединенной таблице будет N * M строк. CROSS JOIN эквивалентен FROM table1 INNER JOIN table2 ON TRUE.
Пример:
SELECT * FROM books CROSS JOIN covers;Результат:
id | title | cover_id | id | name ----+------------------+----------+----+---------------------------- 1 | Hyperion | 1 | 1 | Paperback 2 | 1984 | 2 | 1 | Paperback 3 | War and Peace | 3 | 1 | Paperback 4 | The Time Machine | 3 | 1 | Paperback 5 | Mrs. Dalloway | 5 | 1 | Paperback 1 | Hyperion | 1 | 2 | Hardcover 2 | 1984 | 2 | 2 | Hardcover 3 | War and Peace | 3 | 2 | Hardcover 4 | The Time Machine | 3 | 2 | Hardcover 5 | Mrs. Dalloway | 5 | 2 | Hardcover 1 | Hyperion | 1 | 3 | Hardcover with dust jacket 2 | 1984 | 2 | 3 | Hardcover with dust jacket 3 | War and Peace | 3 | 3 | Hardcover with dust jacket 4 | The Time Machine | 3 | 3 | Hardcover with dust jacket 5 | Mrs. Dalloway | 5 | 3 | Hardcover with dust jacket 1 | Hyperion | 1 | 4 | Leatherette cover 2 | 1984 | 2 | 4 | Leatherette cover 3 | War and Peace | 3 | 4 | Leatherette cover 4 | The Time Machine | 3 | 4 | Leatherette cover 5 | Mrs. Dalloway | 5 | 4 | Leatherette cover (20 rows)
INNER JOIN
INNER JOIN рассматривает каждую строку в первой таблице и проверяет, содержит ли вторая таблица строку, которая удовлетворяет условию JOIN. Если соответствующая строка найдена, INNER JOIN создает новую строку, содержащую столбцы из обеих таблиц, и добавляет её в набор результатов.
Пример:
SELECT books.id, books.title, covers.name cover FROM books
INNER JOIN covers ON books.cover_id = covers.id;Результат содержит поля id, title и cover для каждой книги. Книга Mrs. Dalloway не включена в результат, так как для её значения cover_id (4) не найдена соответствующая строка в covers:
id | title | cover ----+------------------+---------------------------- 1 | Hyperion | Paperback 2 | 1984 | Hardcover 3 | War and Peace | Hardcover with dust jacket 4 | The Time Machine | Hardcover with dust jacket
LEFT OUTER JOIN
LEFT OUTER JOIN сначала выполняет операцию INNER JOIN. После этого каждая строка первой таблицы, которая исключена из результата INNER JOIN, добавляется к результату LEFT OUTER JOIN с null-значениями в столбцах второй таблицы. В объединенной таблице всегда есть по крайней мере одна строка для каждой строки первой таблицы.
Пример:
SELECT books.id, books.title, covers.name cover FROM books
LEFT OUTER JOIN covers ON books.cover_id = covers.id;Результат содержит строку с книгой Mrs. Dalloway, в которой столбец covers.name с псевдонимом cover имеет null-значение:
id | title | cover ----+------------------+---------------------------- 1 | Hyperion | Paperback 2 | 1984 | Hardcover 3 | War and Peace | Hardcover with dust jacket 4 | The Time Machine | Hardcover with dust jacket 5 | Mrs. Dalloway |
RIGHT OUTER JOIN
RIGHT OUTER JOIN сначала выполняет операцию INNER JOIN. После этого каждая строка второй таблицы, которая исключена из результата INNER JOIN, добавляется к результату RIGHT OUTER JOIN с null-значениями в столбцах первой таблицы. В объединенной таблице всегда есть одна строка для каждой строки второй таблицы.
Пример:
SELECT books.id, books.title, covers.name cover FROM books
RIGHT OUTER JOIN covers ON books.cover_id = covers.id;Результат содержит строку со значением Leatherette cover, в которой столбцы books.id и books.title имеют null-значения:
id | title | cover
----+------------------+----------------------------
1 | Hyperion | Paperback
2 | 1984 | Hardcover
3 | War and Peace | Hardcover with dust jacket
4 | The Time Machine | Hardcover with dust jacket
| | Leatherette cover
FULL OUTER JOIN
FULL OUTER JOIN сначала выполняет операцию INNER JOIN. После этого каждая строка первой таблицы, исключенная из результата INNER JOIN, добавляется к результату FULL OUTER JOIN с null-значениями в столбцах второй таблицы. Затем каждая строка второй таблицы, исключенная из результата INNER JOIN, добавляется к результату FULL OUTER JOIN с null-значениями в столбцах первой таблицы.
Пример:
SELECT books.id, books.title, covers.name cover FROM books
FULL OUTER JOIN covers ON books.cover_id = covers.id;Результат:
id | title | cover
----+------------------+----------------------------
1 | Hyperion | Paperback
2 | 1984 | Hardcover
3 | War and Peace | Hardcover with dust jacket
4 | The Time Machine | Hardcover with dust jacket
5 | Mrs. Dalloway |
| | Leatherette cover
Условия JOIN
Условие JOIN определяет, какие строки из двух исходных таблиц совпадают. Условие может быть задано с помощью выражений ON, USING или NATURAL.
ON
ON возвращает значение boolean. Строка из первой таблицы соответствует строке из второй таблицы, если выражение ON возвращает true.
Создадим две таблицы для тестовых примеров:
CREATE TABLE table1(
num INT PRIMARY KEY,
name VARCHAR (25)
);
INSERT INTO table1 (num, name) VALUES
(1, 'a'),
(2, 'b'),
(3, 'c');
CREATE TABLE table2(
num INT PRIMARY KEY,
value VARCHAR (25)
);
INSERT INTO table2 (num, value) VALUES
(1, 'value1'),
(3, 'value2'),
(5, 'value3');таблица1:
num | name -----+------ 1 | a 2 | b 3 | c
таблица2:
num | value -----+------- 1 | value1 3 | value2 5 | value3
Выполним следующий запрос с условием ON:
SELECT * FROM table1 INNER JOIN table2 ON table1.num = table2.num;Результат:
num | name | num | value -----+------+-----+-------- 1 | a | 1 | value1 3 | c | 3 | value2
Условие JOIN также может содержать дополнительные выражения, которые не относятся непосредственно к JOIN.
Пример:
SELECT * FROM table1 INNER JOIN table2 ON table1.num = table2.num AND table2.value = 'value1';Результат:
num | name | num | value -----+------+-----+-------- 1 | a | 1 | value1
USING
Вы можете использовать USING, когда столбцы в обеих таблицах имеют одинаковые имена. USING принимает в качестве параметра список имен столбцов, разделенных запятыми, и создает условие JOIN, включающее сравнение на равенство для каждого столбца. Например, объединение table1 и table2 с помощью USING (field1, field2) создаст условие JOIN ON table1.field1 = table2.field1 AND table1.field2 = table2.field2.
Результат выражения JOIN с условием USING будет содержать один столбец для каждой из перечисленных пар столбцов в указанном порядке, за которым следуют все оставшиеся столбцы из первой и второй таблиц.
Пример:
SELECT * FROM table1 INNER JOIN table2 USING (num);Результат:
num | name | value -----+------+-------- 1 | a | value1 3 | c | value2
NATURAL
Выражение NATURAL создает список USING. Он включает все совпадающие имена столбцов, содержащиеся в обеих входных таблицах. Как и в случае с USING, результат выражения JOIN с условием, заданным NATURAL, содержит один столбец для каждой из перечисленных пар столбцов в указанном порядке, за которым следуют все оставшиеся столбцы из table1 и table2.
Пример:
SELECT * FROM table1 NATURAL INNER JOIN table2;Результат:
num | name | value -----+------+-------- 1 | a | value1 3 | c | value2
Если в таблицах нет столбцов с совпадающими именами, NATURAL JOIN выдаст тот же результат, что и CROSS JOIN.
Комбинирование операторов JOIN
Операторы JOIN всех типов можно объединять или вкладывать один в другой. По умолчанию операторы JOIN выполняются слева направо. Можно заключить JOIN в круглые скобки, чтобы указать порядок выполнения.
Создадим table3 для тестовых примеров в дополнение к table1 и table2:
CREATE TABLE table3(
num INT PRIMARY KEY,
name VARCHAR (25)
);
INSERT INTO table3 (id, value) VALUES
(1, 'value1'),
(2, 'value4');table1 table2 table3 num | name num | value id | value -----+------ -----+------ -----+------ 1 | a 1 | value1 1 | value1 2 | b 3 | value2 2 | value4 3 | c 5 | value3
Выполним запрос JOIN с круглыми скобками и без них и сравним результаты.
Выполним запрос без круглых скобок:
SELECT * FROM table1 CROSS JOIN table2 INNER JOIN table3 USING(value);Сначала PostgreSQL выполнит выражение SELECT * FROM table1 CROSS JOIN table2. Его результат:
num | name | num | value -----+------+-----+-------- 1 | a | 1 | value1 1 | a | 3 | value2 1 | a | 5 | value3 2 | b | 1 | value1 2 | b | 3 | value2 2 | b | 5 | value3 3 | c | 1 | value1 3 | c | 3 | value2 3 | c | 5 | value3
После этого PostgreSQL выполнит INNER JOIN для результирующей таблицы CROSS JOIN и table3. Поскольку USING содержит поле value, это поле будет первым столбцом результирующей таблицы.
Результат:
value | num | name | num | id --------+-----+------+-----+---- value1 | 1 | a | 1 | 1 value1 | 2 | b | 1 | 1 value1 | 3 | c | 1 | 1
Добавим скобки в запрос:
SELECT * FROM table1 CROSS JOIN (table2 INNER JOIN table3 USING(value));Сначала PostgreSQL выполнит выражение SELECT * FROM table2 INNER JOIN table3 USING(value). Его результат:
value | num | id --------+-----+---- value1 | 1 | 1
После этого PostgreSQL выполнит CROSS JOIN для table1 и результирующей таблицы INNER JOIN.
Результат:
num | name | value | num | id -----+------+--------+-----+---- 1 | a | value1 | 1 | 1 2 | b | value1 | 1 | 1 3 | c | value1 | 1 | 1