Виды JOIN в SQL

В SQL-джойнах скрыто больше, чем можно подумать. Давайте разберем их.

Будем использовать две простые таблицы: компании companies и их вакансии jobs.

Есть три вымышленные компании — Hoogle, Emazon и Neta — которые предлагают на удивление мало вакансий:

  jobs                                   companies
┌────────┬─────────┬──────────────┐    ┌─────────┬───────────┐
│ job_id │ comp_id │   job_name   │    │ comp_id │ comp_name │
├────────┼─────────┼──────────────┤    ├─────────┼───────────┤
│ 1      │ 10      │ Data Analyst │    │ 10      │ Hoogle    │
│ 2      │ 20      │ Go Developer │    │ 20      │ Emazon    │
│ 3      │ 20      │ ML Engineer  │    │ 30      │ Neta      │
│ 4      │ 99      │ UI Designer  │    └─────────┴───────────┘
└────────┴─────────┴──────────────┘

(свайпайте влево, чтобы увидеть компании)

Hoogle интересуется аналитиками данных. Emazon нанимает Go-разработчиков и ML-инженеров. У Neta нет вакансий. А какая-то ноунейм-компания с идентификатором 99 отчаянно разыскивает UI-дизайнера.

Время джойнить!

• Квалифицированный JOIN
• Естественный JOIN
• Перекрестный JOIN
• Секционированный JOIN
• Латеральный JOIN
• Итого

Квалифицированный JOIN

Квалифицированный джойн (qualified join) — это общий термин, которым обозначают всем известные типы джойнов: inner, left, right и full. Если вы про них не слышали или подзабыли — посмотрите SQL-шпаргалку.

Квалифицированный джойн соединяет два набора данных в один по заданным правилам. Вот как он выглядит в общем случае:

table [join-type] JOIN table join-specification

Таблица (table) — не обязательно прямо вот таблица. Это может быть представление, подзапрос или любая табличная структура данных. Но для краткости будем называть ее таблицей.

Тип джойна (join-type) может быть таким:

• inner включает только совпадающие записи из обеих таблиц.
• left включает совпадающие записи из обеих таблиц и несовпадающие из левой таблицы.
• right включает совпадающие записи из обеих таблиц и несовпадающие из правой таблицы.
• full включает совпадающие и несовпадающие записи из обеих таблиц.

Для left, right и full можно использовать необязательное слово outer, которое ничего не меняет (как будто SQL недостаточно сложный):

LEFT OUTER  = LEFT
RIGHT OUTER = RIGHT
FULL OUTER  = FULL

Да и вообще join-type можно не писать. По умолчанию джойн считается внутренним (inner).

Спецификация джойна (join-specification) задает правила соответствия между таблицами. Она бывает двух сортов.

Первый вариант использует ключевое слово on, которое вы наверняка не раз встречали. Например, выберем вакансии вместе с соответствующими названиями компаний:

select
  job_name, comp_name
from jobs
  join companies on jobs.comp_id = companies.comp_id;

Второй вариант более редкий. Он использует ключевое слово using и работает, только если целевой столбец в обеих таблицах называется одинаково:

select
  job_name, comp_name
from jobs
  join companies using(comp_id);

С on можно использовать любые условия, а using проверяет только на равенство.

Удобно!

Естественный JOIN

Естественный джойн (natural join) — это как квалифицированный через using, только мы вообще не указываем столбцы, по которым соединяются таблицы:

select
  job_name, comp_name
from jobs
  natural join companies;

Естественный джойн находит все пары столбцов с одинаковыми названиями и использует их для соединения.

Аналогично using, естественный джойн проверяет на равенство. Аналогично квалифицированному джойну, он может быть внутренним (inner, по умолчанию) или внешним (left, right или full):

table NATURAL [join-type] JOIN table

Использовать естественный джойн — сомнительная идея. Допустим, у нас есть столбец name в обеих таблицах:

create table jobs (
  job_id integer primary key,
  comp_id integer,
  name text
);

create table if not exists companies (
  comp_id integer primary key,
  name text
);

Вполне нормальная структура. Но естественный джойн между jobs и companies вернет пустой результат, потому что неявно сравнивает по такому условию:

jobs.comp_id = companies.comp_id and jobs.name = companies.name

Так что лично я без раздумий выбираю using вместо естественного соединения.

Перекрестный JOIN

Третья и последняя разновидность — перекрестный джойн (cross join), также известный как «декартово соединение» (Cartesian join):

select
  job_name, comp_name
from jobs
  cross join companies;

Перекрестный джойн игнорирует значения столбцов. Он берет каждую строку из левой таблицы (N строк) и соединяет с каждой строкой из правой таблицы (M строк), выдавая в результате N×M строк.

Перекрестный джойн полезен, чтобы получить все пары значений из двух таблиц. Например, комбинации «цвет-размер» по всем продуктам. В нашем примере с вакансиями толку от него немного.

Перекрестный джойн — то же самое, что внутренний джойн (inner join, он же просто join) без критерия совпадения:

select
  job_name, comp_name
from jobs
  join companies on true;

Иногда перекрестный джойн записывают так:

select job_name, comp_name
from jobs, companies;

Хотя в запрос вообще отсутствует слово join, это все то же самое перекрестное соединение.

Секционированный JOIN

Не я ли чуть выше говорил, что существует всего три вида джойнов — квалифицированный, естественный и перекрестный? Ну да.

Но тут вот какая штука. Помните определение квалифицированного джойна?

table [join-type] JOIN table join-specification

Согласно стандарту SQL, вместо table здесь можно использовать секционированную джойн-таблицу (partitioned join table):

select ...
from table_x partition by (col_1, col_2, ...)
  join table_y on ...
;

Допустим у нас есть таблица продуктов (products) и продаж (sales) по дням:

  sales                                          products
┌────┬────────────┬────────────┬──────────┐    ┌────┬───────┐
│ id │  sale_dt   │ product_id │ quantity │    │ id │ name  │
├────┼────────────┼────────────┼──────────┤    ├────┼───────┤
│ 1  │ 2023-06-01 │ 10         │ 30       │    │ 10 │ Alpha │
│ 2  │ 2023-06-01 │ 20         │ 60       │    │ 20 │ Beta  │
│ 3  │ 2023-06-01 │ 30         │ 90       │    │ 30 │ Gamma │
│ 4  │ 2023-06-02 │ 20         │ 60       │    └────┴───────┘
│ 5  │ 2023-06-03 │ 10         │ 30       │
│ 6  │ 2023-06-03 │ 30         │ 90       │
└────┴────────────┴────────────┴──────────┘

(свайпайте влево, чтобы увидеть продукты)

Выберем продажи с соответствующими названиями продуктов:

select
  sale_dt, products.name, quantity
from sales
   join products on sales.product_id = products.id
order by sale_dt;

Пока все просто. Но что если я хочу увидеть продажи каждого продукта за каждый день? Включая дни, в которые не было продаж отдельных продуктов:

┌────────────┬───────┬──────────┐
│  sale_dt   │ name  │ quantity │
├────────────┼───────┼──────────┤
│ 2023-06-01 │ Alpha │ 30       │
│ 2023-06-01 │ Beta  │ 60       │
│ 2023-06-01 │ Gamma │ 90       │
│ 2023-06-02 │ Alpha │ 0        │
│ 2023-06-02 │ Beta  │ 60       │
│ 2023-06-02 │ Gamma │ 0        │
│ 2023-06-03 │ Alpha │ 30       │
│ 2023-06-03 │ Beta  │ 0        │
│ 2023-06-03 │ Gamma │ 90       │
└────────────┴───────┴──────────┘

Эту задачу никак не решить без дополнительных ухищрений. Разве что использовать секционированный джойн (partitioned join).

Секционированный джойн говорит движку СУБД выполнять соединение отдельно по каждой секции, которую мы задали для таблицы. Поэтому, если мы определим секции продаж по дате:

select
  sale_dt, name, quantity
from sales partition by (sale_dt)
  right join products on sales.product_id = products.id
order by sale_dt, name;

То движок независимо соединит продажи за 01.06 со всеми продуктами, затем продажи за 02.06 со всеми продуктами, затем продажи за 03.06 со всеми продуктами, и наконец объединит промежуточные результаты. В результате получим искомый набор данных (разве что вместо 0 здесь null):

┌────────────┬───────┬──────────┐
│  sale_dt   │ name  │ quantity │
├────────────┼───────┼──────────┤
│ 2023-06-01 │ Alpha │ 30       │
│ 2023-06-01 │ Beta  │ 60       │
│ 2023-06-01 │ Gamma │ 90       │
│ 2023-06-02 │ Alpha │ (null)   │
│ 2023-06-02 │ Beta  │ 60       │
│ 2023-06-02 │ Gamma │ (null)   │
│ 2023-06-03 │ Alpha │ 30       │
│ 2023-06-03 │ Beta  │ (null)   │
│ 2023-06-03 │ Gamma │ 90       │
└────────────┴───────┴──────────┘

Надо сказать, что странноватая фича. Я удивлен, что она вообще вошла в стандарт (подозрительно связано с тем, что она реализована в Oracle). Другие производители СУБД так никогда и не реализовали секционированный джойн. Не могу их за это осуждать.

В любом случае, теперь и вы знаете о существовании этого джойна. Не нести же мне бремя бесполезных SQL-знаний одному.

Если вам интересно, как решить задачу без секционированного джойна (знаю, что нет), то вот:

-- выбираем все даты
with dates as (
  select distinct sale_dt
  from sales
)
-- перекрестный джойн дат с продуктами
-- дает все пары «дата-продукт»,
-- а их уже соединяем с продажами
select
  dates.sale_dt,
  name, quantity
from dates
  cross join products
  left join sales on sales.sale_dt = dates.sale_dt
    and sales.product_id = products.id
order by dates.sale_dt, name;

Проще простого.

Латеральный JOIN

SQL — странная штука. Стандарт одновременно включает и секционированный джойн (который никто кроме Oracle не поддержал), и намного более мощную и распространенную разновидность — латеральный (lateral) джойн.

Латеральное соединение, в противоположность обычному, разрешает коррелированные подзапросы. Сейчас разберемся, что это.

Вернемся к примеру с продуктами и продажами:

  sales                                          products
┌────┬────────────┬────────────┬──────────┐    ┌────┬───────┐
│ id │  sale_dt   │ product_id │ quantity │    │ id │ name  │
├────┼────────────┼────────────┼──────────┤    ├────┼───────┤
│ 1  │ 2023-06-01 │ 10         │ 30       │    │ 10 │ Alpha │
│ 2  │ 2023-06-01 │ 20         │ 60       │    │ 20 │ Beta  │
│ 3  │ 2023-06-01 │ 30         │ 90       │    │ 30 │ Gamma │
│ 4  │ 2023-06-02 │ 20         │ 60       │    └────┴───────┘
│ 5  │ 2023-06-03 │ 10         │ 30       │
│ 6  │ 2023-06-03 │ 30         │ 90       │
└────┴────────────┴────────────┴──────────┘

(свайпайте влево, чтобы увидеть продукты)

Посмотрим, как каждый продукт продавался 2 июня:

select
  '2023-06-02' as sale_dt, name, sales.quantity
from products
  left join sales on products.id = sales.product_id
  and sales.sale_dt = '2023-06-02';

А теперь посмотрим продажи каждого продукта за каждый день (как делали в примере с секционированным джойном). Было бы здорово выбрать даты отдельным подзапросом и соединить с подзапросом по конкретной дате из примера выше:

select
  d.sale_dt, ps.name, ps.quantity
from
  (select distinct sale_dt from sales) as d
  join (
    select d.sale_dt, name, sales.quantity
    from products
      left join sales on products.id = sales.product_id
      and sales.sale_dt = d.sale_dt
  ) as ps on true
order by sale_dt, name;

Но нет. Нельзя использовать столбец sale_dt из подзапроса d в следующем за ним подзапросе ps. А вот если использовать латеральный джойн — можно:

select
  d.sale_dt, ps.name, ps.quantity
from
  (select distinct sale_dt from sales) as d
  join lateral (
    select d.sale_dt, name, sales.quantity
    from products
      left join sales on products.id = sales.product_id
      and sales.sale_dt = d.sale_dt
  ) as ps on true
order by sale_dt, name;

Подзапрос d выбирает даты, а подзапрос ps соединяется с ним по столбцу sale_dt. Тем самым ps выбирает продажи каждого продукта для каждой конкретной даты. Все благодаря латеральному джойну. Удобно!

Может возникнут вопрос насчет условия on true. Дело в том, что фактически джойн по sale_dt уже произошел внутри подзапроса ps, поэтому повторять его снаружи не требуется. Можете поменять true на d.sale_dt = ps.sale_dt и убедиться, что ничего не изменилось.

Латеральные соединения поддерживаются в PostgreSQL, MySQL и Oracle. MS SQL Server не поддерживает синтаксис lateral, но предоставляет аналогичную функциональность с собственным синтаксисом cross apply (= join lateral) и outer apply (= left join lateral).

Итого

Стандарт SQL описывает три варианта JOIN:

• квалифицированный (соединение по указанным критериям);
• естественный (автоматически выбирает критерии);
• перекрестный (внутреннее соединение без критериев).

Квалифицированный джойн предусматривает четыре типа: inner, left, right и full.

Он разрешает задать критерии соединения через on или using.

Большинство производителей СУБД поддерживают все виды JOIN. Заметное исключение — MS SQL Server, который знать не знает о using и естественных джойнах.

Есть еще модификаторы, которые изменяют поведение джойна:

• lateral разрешает коррелированные подзапросы в join-части запроса. Поддерживается в PostgreSQL, MySQL и Oracle (и MS SQL Server с другим синтаксисом).
• partition by производит независимое соединение по каждой из заданных секций. Поддерживается только в Oracle.

И еще MySQL не поддерживает full-джойны. Просто чтобы жизнь сахаром не казалась.

Вот и все!

──

P.S. Хотите освоить современный SQL? Обратите внимание на Оконные функции