Соединение строит строки из двух таблиц, сопоставленные по условию: SELECT ... FROM a JOIN b ON a.id = b.a_id. JOIN (INNER JOIN) оставляет только совпавшие строки, LEFT JOIN все строки первой таблицы, а FULL JOIN все строки обеих.
Возвращаются три строки. У Grace нет заказов, а заказ 13 принадлежит несуществующему клиенту, поэтому оба не попадают в результат. Соединения ниже используют те же две таблицы.
Типы соединений
| Соединение | Какие строки возвращает |
|---|---|
INNER JOIN | Пары, которые подходят под условие |
LEFT JOIN | Все левые строки; правые столбцы NULL, где совпадения нет |
RIGHT JOIN | Все правые строки; зеркало LEFT JOIN |
FULL JOIN | Все строки обеих сторон, пробелы заполнены NULL |
CROSS JOIN | Каждая левая строка в паре с каждой правой |
LEFT JOIN: оставить строки без совпадения
Grace появляется с 0 заказов. Считайте o.id, а не *: count(*) посчитал бы строку Grace из NULL как 1.
- Строки без совпадения (anti join): добавьте
WHERE o.id IS NULL, чтобы оставить только клиентов без заказов.WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)делает то же и яснее выражает намерение. - ON или WHERE: условие на правую таблицу пишется в
ON.LEFT JOIN orders o ON o.customer_id = c.id WHERE o.total > 20теряет Grace, потому что еёNULLв total не проходитWHERE. Соединение превратилось во внутреннее. ПишитеON o.customer_id = c.id AND o.total > 20, чтобы сохранить всех клиентов.
FULL JOIN: строки, которых нет с одной из сторон
FULL JOIN находит расхождения в обе стороны, это помогает при сравнении двух списков:
В результате есть Grace без заказа и заказ 13 без клиента. Внешний ключ на orders.customer_id не допустил бы вторую строку.
CROSS JOIN: все комбинации
У CROSS JOIN нет условия. Он сопоставляет каждую строку одной стороны с каждой строкой другой, поэтому 3 размера и 2 цвета дают 6 строк:
Здесь оба списка это списки VALUES с именами, как у таблиц. С настоящими таблицами запрос выглядит как FROM sizes CROSS JOIN colors, а FROM sizes, colors делает то же самое. Соединение, условие ON которого всегда истинно, размножает строки так же; это частая причина запроса, который вдруг возвращает слишком много строк.
LATERAL: подзапрос для каждой строки
Подзапрос во FROM обычно не видит другие таблицы того же FROM. С LATERAL видит и вычисляется по разу для каждой строки таблицы перед ним. Так решается задача «top N в каждой группе»:
Ada получает два самых крупных заказа, Linus свой единственный, а Grace строку из NULL благодаря LEFT JOIN ... ON true. CROSS JOIN LATERAL отбросил бы клиентов без заказов. С индексом на orders (customer_id, total) каждый подзапрос читает лишь несколько записей индекса. Для одной строки на группу часто короче DISTINCT ON.
Часто задаваемые вопросы
JOIN и INNER JOIN в PostgreSQL это одно и то же?
Да. Просто JOIN означает INNER JOIN, а LEFT JOIN означает LEFT OUTER JOIN; ключевые слова INNER и OUTER необязательны.
Почему LEFT JOIN возвращает больше строк, чем в левой таблице?
Левая строка появляется по разу на каждую подходящую правую строку. Клиент с тремя заказами появится трижды. Агрегируйте через GROUP BY или соединяйте с подзапросом, где на каждый ключ уже одна строка.
Чем ON отличается от USING?
ON принимает любое условие. USING (id) это сокращение для равенства одноимённых столбцов в обеих таблицах, и в результате остаётся один столбец id вместо двух.
Влияет ли порядок соединений на производительность в PostgreSQL?
Обычно нет. Планировщик переставляет внутренние соединения в поисках дешёвого плана, когда в запросе не больше join_collapse_limit таблиц (по умолчанию 8). Внешние соединения можно переставлять только в некоторых случаях, потому что перестановка может изменить результат.