Menu

JOIN в PostgreSQL: INNER, LEFT, FULL, CROSS и LATERAL

JOIN объединяет строки двух таблиц по условию. INNER JOIN оставляет совпадения, LEFT JOIN оставляет все строки первой таблицы, FULL JOIN строки обеих, CROSS JOIN даёт все пары, а LATERAL выполняет подзапрос для каждой строки.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Соединение строит строки из двух таблиц, сопоставленные по условию: 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). Внешние соединения можно переставлять только в некоторых случаях, потому что перестановка может изменить результат.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать