JOIN в PostgreSQL

Статья основана на 20 видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

В прошлых статьях мы уже разбирали некоторые команды в PostgreSQL: SELECT, ALTER&DROP, WHERE, UPDATE, INSERT, MERGE, DELETE. В этой статье рассмотрим JOIN.

JOIN – одна из наиболее часто используемых команд в SQL-синтаксисе. Она используется для объединения информации из разных таблиц по заранее определенным критериям (напоминаю, что информация хранится в разных таблицах в соответствие с нормальными формами). Данные объединяются по внешним и внутренним ключам, которые создаются заранее.

SQL JOIN помогает настроить фильтр поиска в базе данных, опираясь на взаимосвязи между различными элементами БД и их отличительные черты (ID, номер документа и т. д.)

Виды JOIN

Виды JOIN не соответствуют операциям над множествами. Поэтому иллюстрировать диаграммами Венна некорректно. Подробнее в моей статье.

Синтаксис JOIN:

SELECT <поля>
FROM <таблица 1>
[INNER]
{{LEFT | RIGHT | FULL } [OUTER]} JOIN <таблица 2>
[ON <предикат>]
[WHERE <предикат>]

Перечисляются поля из двух таблиц, которые будут соединены (SELECT). Выбирается вид JOIN (INNER, LEFT, RIGHT, FULL). Указывается условие (ON), по которому они будут соединены (классически – это первичный и внешний ключи). Также может быть указано дополнительное условие отбора (WHERE).

Важно, что PostgreSQL проверяет только корректность написания условия (синтаксис), но не его логику. Поэтому некорректные условия – полностью ответственность разработчика (например, попытка JOIN двух непересекающихся выборок).

INNER JOIN

Простейший вид JOIN – INNER JOIN – внутреннее соединение. В итоговой выборке остаются только записи, где ключи совпадают.

Синтаксис:

SELECT <поля>
FROM A
INNER JOIN B
    ON A.ID=B.FK_A

Классическая запись:

    ON A.KEY=B.KEY

Но обычно JOIN происходит по первичному ключу таблицы А и внешнему ключу таблицы B.

Проиллюстрировать можно гифкой по объединению таблиц с городами и атаками Годзиллы. Сравниваются каждая строчка из обеих таблиц друг с другом и формируется результирующая выборка при совпадении условия совпадения ключей:

Слово INNER может быть пропущено, так как оно является DEFAULT для команды JOIN. Но рекомендуется его прописывать во избежание путаницы.

Рассмотрим INNER JOIN на примере столов и стульев. Столы – таблица А, стулья – таблица В. Условие для объединения – цвет. Для белого стола нашёлся белый стул, он попал в результирующую выборку. Для черного – не нашелся, поэтому он не попал.

Создаем таблицу Столы:

CREATE TABLE tables (
    color text, 
    type_table text
);

Создаем таблицу Стулья:

CREATE TABLE chairs (
    color text, 
    type_chair text
);

Вставляем две строки в Столы:

INSERT INTO tables VALUES 
    ('white', 'classic'),
    ('black', 'classic');

Вставляем две строки в стулья:

INSERT INTO chairs VALUES 
    ('white', 'plastic'),
    ('blue', 'plastic');

Применяем INNER JOIN в 2 вариантах – результат будет индентичен:

SELECT *
FROM tables a
JOIN chairs b
  ON a.color = b.color;

SELECT *
FROM tables a
INNER JOIN chairs b
  ON a.color = b.color;

Теперь добавим ещё две строки в таблицу Столы:

INSERT INTO tables VALUES 
    ('white', 'classic'),
    ('black', 'classic');

И ещё две строки в таблицу Стулья:

INSERT INTO chairs VALUES 
    ('white', 'plastic'),
    ('blue', 'plastic');

Проведем тот же JOIN:

SELECT *
FROM tables a
--INNER JOIN chairs b
JOIN chairs b
    ON a.color = b.color;

Для каждого белого стола из первой таблицы нашелся каждый белый стул из второй таблицы (Декартово произведение парных по цветам).

LEFT JOIN

Не менее популярный вид JOIN – LEFT JOIN – левостороннее соединение. Суммарно LEFT JOIN и INNER JOIN составляют около 99% от всех использований JOIN. Обратите внимание, что можно указывать слово OUTER – оно ни на что не влияет и оставлено для совместимости со старым кодом. Рекомендация – не указывать.

Синтаксис:

SELECT <поля>
FROM A
LEFT OUTER JOIN B
    ON A.ID=B.FK_A

LEFT JOIN – расширенный вариант INNER JOIN. При применении этой команды точно также будет запущен поиск совпадений в таблице В для каждой строки из таблицы А. Однако даже те строки из А, к которым не будет найдено совпадения в В, будут добавлены в результирующую выборку с подставленным значением NULL.

Рассмотрим на столах и стульях. Столы – таблица А, из неё мы берем все значения. К белому столу нашелся белый стул, а к черному не нашлось ничего, поэтому он будет записан в паре с NULL.

Создаем те же таблицы по две строки в каждой:

CREATE TABLE tables (
    color text, 
    type_table text
);
CREATE TABLE chairs (
    color text, 
    type_chair text
);
INSERT INTO tables VALUES 
    ('white', 'classic'),
    ('black', 'classic');
INSERT INTO chairs VALUES 
    ('white', 'plastic'),
    ('blue', 'plastic');

Проведем LEFT JOIN, соответствующий картинке выше:

SELECT *
FROM tables a
--LEFT JOIN chairs b
LEFT OUTER JOIN chairs b
    ON a.color = b.color;

LEFT JOIN with NULL

Синтаксис:

SELECT <поля>
FROM A
LEFT OUTER JOIN B
    ON A.ID=B.FK_A
WHERE B.FK_A is NULL

Выбираются все записи из таблицы А, которым не найдено соответствия в таблице В.

С помощью этой команды можно, например, объединить таблицы Склад и Отдел продаж, увидев товары, которые ни разу не продавались. Также можно найти сотрудников, у которых не назначено задание, используя таблицы Сотрудники и Задания.

В примере со столами и стульями будут найдены те столы, к которым нет парного стула.

Выполним LEFT JOIN with NULL в ранее созданных таблицах:

SELECT *
FROM tables a
LEFT JOIN chairs b
    ON a.color = b.color
WHERE b.color is NULL;

RIGHT JOIN

Синтаксис:

SELECT <поля>
FROM A
RIGHT OUTER JOIN B
    ON A.ID=B.FK_A

RIGHT JOIN аналогичен LEFT JOIN, только в этом случае берутся все значения из таблицы В, а к ним уже ищутся совпадения в А. При отсутствии совпадения в пару записывается NULL.

RIGHT JOIN не используется, так как это тот же LEFT JOIN, но с поменянными местами таблицами А и В.

FULL JOIN

Синтаксис:

SELECT <поля>
FROM A
FULL OUTER JOIN B
    ON A.KEY=B.KEY

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

Результат применения такой команды на примере столов и стульев:

Применим FULL JOIN к существующим таблицам:

SELECT *
FROM tables a
FULL JOIN chairs b
    ON a.color = b.color;

FULL JOIN with NULL

Синтаксис:

SELECT <поля>
FROM A
FULL OUTER JOIN B
ON A.KEY=B.KEY
WHERE A.KEY is NULL OR B.KEY is NULL

FULL JOIN with NULL находит несовпадающие элементы из двух таблиц.

Команда может быть использована, например, для автосервиса. А именно, для нахождения всех мастеров из таблицы Мастера, у которых сейчас нет задания, а также нахождения всех машин из таблицы Машины, на которые ещё не был назначен мастер.

В примере со столами и стульями FULL JOIN with NULL найдет как все некомплектные столы, так и некомплектные стулья.

Применим команду:

SELECT *
FROM tables a
FULL JOIN chairs b
    ON a.color = b.color
WHERE a.color is NULL or b.color is NULL;

CROSS JOIN

Синтаксис:

SELECT <поля A>, <поля B>
FROM A
CROSS JOIN B

CROSS JOIN представляем из себя Декартово произведение (все со всеми без условий).

CROSS JOIN может быть использован, например, для соревнований – каждый из первой таблицы должен сыграть с каждым из второй.

Для столов и стульев CROSS JOIN выглядит так:

Команда для наших таблиц:

SELECT *
FROM tables a
CROSS JOIN chairs b;

Можно заметить, что результат CROSS JOIN совпадает с таким запросом, где таблицы перечислены через запятую (не самый очевидный синтаксис):

SELECT *
FROM tables, chairs;

LATERAL JOIN

LATERAL JOIN – объединение с зависимыми подзапросами.

Синтаксис:

SELECT <target list>
FROM <table>
JOIN LATERAL
(<subquery using table.column>) as foo;

Создадим таблицу с продажами (пользователь и дата продажи), заполним её значениями:

CREATE TABLE test (
  id int,
  user_id int,
  created_date date
);
INSERT INTO test VALUES 
    (1, 1, date'2023-01-01'), 
    (2, 1, date'2023-03-02'), 
    (3, 1, date'2023-04-11'), 
    (4, 2, date'2023-04-02'), 
    (5, 2, date'2023-06-15'), 
    (6, 3, date'2023-07-12');

Узнаем дату первой и последней (второй и далее) продажи для каждого пользователя:

SELECT *
FROM (
SELECT user_id, min(created_date) AS first_order
FROM test
GROUP BY user_id
) s1
LEFT JOIN LATERAL (
SELECT id, created_date AS last_order
FROM test
WHERE user_id = s1.user_id
AND created_date > first_order
ORDER BY created_date
LIMIT 1
) s2
ON TRUE;

Дополнительный материал про LATERAL JOIN

Презентация к статье здесь.

21 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.