JOIN, LEFT JOIN и FULL JOIN, соединение таблицы с самой собой и цепочки из нескольких таблиц.
Данные разложены по таблицам: у товара в products записан только номер категории (category_id), а её название —
в categories. Соединение — JOIN — склеивает строки двух таблиц по условию в ON:
SELECT p.name AS product, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id;
products p — псевдоним: дальше вместо имени таблицы пишут p. Когда в двух таблицах есть колонки с одним
именем (id, name), без указания таблицы PostgreSQL не поймёт, о какой речь, и ответит ошибкой
column reference "name" is ambiguous.
JOIN (полностью — INNER JOIN) оставляет только пары, для которых условие в ON истинно. Товар без категории
(category_id — NULL) пары не найдёт и из результата пропадёт.
LEFT JOIN оставляет все строки левой таблицы — той, что написана до JOIN. Если пары справа нет, колонки
правой таблицы будут NULL:
SELECT p.name AS product, c.name AS category
FROM products p
LEFT JOIN categories c ON c.id = p.category_id
ORDER BY c.name NULLS FIRST;
Отсюда классический приём «найти строки без пары» — LEFT JOIN и проверка IS NULL у колонки правой таблицы,
которая в паре никогда не пустая, например у её id.
RIGHT JOIN — то же, но сохраняется правая таблица; его почти не пишут: таблицы меняют местами и пишут
LEFT JOIN. FULL JOIN сохраняет строки обеих таблиц. CROSS JOIN склеивает каждую строку с каждой, без
условия: 5 строк × 4 строки = 20 строк.
У подкатегории в categories есть parent_id — номер родительской категории в той же таблице. Чтобы вывести оба
названия рядом, таблицу подключают дважды, под разными псевдонимами:
SELECT child.name AS category, parent.name AS parent
FROM categories child
JOIN categories parent ON parent.id = child.parent_id;
Таблицы соединяют цепочкой — у каждого следующего JOIN своё условие:
SELECT o.id, c.name AS customer, p.name AS product, i.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
WHERE o.id = 1;
При LEFT JOIN важно, где стоит условие. Условие в ON решает, какая строка справа годится в пару; условие
в WHERE отбрасывает строки уже после соединения, включая те, где справа NULL. Об этом — последняя задача блока.