Данная статья относится к циклу статей, посвященных PostgreSQL. В предыдущей статье мы говорили о псевдонимах в PostgreSQL. Теперь же мы углубимся в изучение объединений PostgreSQL.
Объединения в PostgreSQL используются для объединения столбцов из одной (самообъединение) или нескольких таблиц на основе значений общих столбцов между таблицами. Общие столбцы обычно представляют собой столбцы первичного ключа первой таблицы и столбцы внешнего ключа второй таблицы.
PostgreSQL поддерживает inner join, left join, right join, full outer join, cross join, natural join и особое объединение именуемое self-join.
Настройка примеров таблиц
Предположим, что у вас есть две таблицы basket_a и basket_b, хранящие фрукты:
CREATE TABLE basket_a (
id INT PRIMARY KEY,
fruit VARCHAR (100) NOT NULL
);
CREATE TABLE basket_b (
id INT PRIMARY KEY,
fruit VARCHAR (100) NOT NULL
);
INSERT INTO basket_a (id, fruit)
VALUES
(1, 'Apple'),
(2, 'Orange'),
(3, 'Banana'),
(4, 'Cucumber');
INSERT INTO basket_b (id, fruit)
VALUES
(1, 'Orange'),
(2, 'Apple'),
(3, 'Watermelon'),
(4, 'Pear');
Таблицы содержат некоторые общие фрукты, такие как яблоки и апельсины. Назовём basket_a левой таблицей, а basket_b правой.
Внутреннее объединение в PostgreSQL
Следующий запрос объединяет левую таблицу с правой, используя значения в столбце fruit:
SELECT
a.id id_a,
a.fruit fruit_a,
b.id id_b,
b.fruit fruit_b
FROM
basket_a a
INNER JOIN basket_b b ON a.fruit = b.fruit;
Очевидно, что внутреннее объединение возвращает набор результатов, который содержит строки в левой таблице, которые соответствуют строкам в правой таблице.
Следующая диаграмма Венна иллюстрирует внутреннее соединение:
Левое объединение в PostgreSQL
Следующий запрос объединяет левую таблицу с правой, используя левое объединение (или левое внешнее объединение):
SELECT
a.id id_a,
a.fruit fruit_a,
b.id id_b,
b.fruit fruit_b
FROM
basket_a a
LEFT JOIN basket_b b ON a.fruit = b.fruit;
Левое объединение возвращает полный набор строк из левой таблицы с совпадающими строками из правой (если они есть). Если же совпадений нет, правая сторона будет иметь значения NULL.
Следующая диаграмма Венна иллюстрирует левое объединение:
Чтобы получить строки только из левой таблицы, не содержащиеся в правой, используйте левое объединение, а затем исключите из правой таблицы те строки, которые вам не нужны, при помощи условия WHERE:
SELECT
a.id id_a,
a.fruit fruit_a,
b.id id_b,
b.fruit fruit_b
FROM
basket_a a
LEFT JOIN basket_b b ON a.fruit = b.fruit
WHERE b.id IS NULL;
Пример вывода:
Следующая диаграмма Венна иллюстрирует левое объединение со строками только из левой таблицы:
Правое объединение в PostgreSQL
Правое объединение или правое внешнее объединение — это обратная версия левого объединения. Оно создаёт набор результатов, которые хранят все строки из правой таблицы с совпадающими строками из левой. Если же совпадений нет, левая сторона будет содержать значения NULL.
Следующий запрос выполняет правое объединение между левой и правой таблицами:
SELECT
a.id id_a,
a.fruit fruit_a,
b.id id_b,
b.fruit fruit_b
FROM
basket_a a
RIGHT JOIN basket_b b ON a.fruit = b.fruit;
Пример вывода:
Следующая диаграмма Венна иллюстрирует правое объединение:
Аналогично, вы можете получить только строки из правой таблицы, игнорируя левую, при помощи условия WHERE:
Следующая диаграмма Венна иллюстрирует правое объединение со строками только из правой таблицы:
Полное внешнее объединение в PostgreSQL
Полное внешнее объединение или полное объединение создаёт результирующий набор, который хранит все строки из обеих таблиц с соответствующими строками с обеих сторон, где это возможно. Если совпадений нет, отсутствующая сторона содержит значения NULL.
Следующий запрос иллюстрирует полное внешнее объединение:
SELECT
a.id id_a,
a.fruit fruit_a,
b.id id_b,
b.fruit fruit_b
FROM
basket_a a
FULL OUTER JOIN basket_b b ON a.fruit = b.fruit;
Обратите внимание, что использование ключевого слова OUTER необязательно.
Вывод результирующего набора:
Следующая диаграмма Венна иллюстрирует полное внешнее объединение:
Чтобы вернуть набор строк, уникальных для левой и правой таблиц, вы сначала выполняете всё то же полное объединение, а затем исключаете строки, которые вам не нужны с обеих сторон, при помощи условия WHERE:
SELECT
a.id id_a,
a.fruit fruit_a,
b.id id_b,
b.fruit fruit_b
FROM
basket_a a
FULL JOIN basket_b b ON a.fruit = b.fruit
WHERE a.id IS NULL OR b.id IS NULL;
Пример вывода:
Следующая диаграмма Венна иллюстрирует полное внешнее объединение только с уникальными строками:
Изображение ниже отображает все объединения PostgreSQL, которые мы обсуждали выше, со всеми тонкостями синтаксиса:
На этом введение в объединения PostgreSQL подходит к концу. Мы научились использовать различные виды объединений для запроса данных из нескольких таблиц. Далее мы поговорим о каждом по отдельности. Начнём со внутреннего объединения.
Источник: PostgreSQL Tutorial from Scratch