В предыдущих уроках мы использовали ON c.id = o.customer_id для соединений. Стандарт SQL даёт ещё одну форму — USING (column_name). Она короче, но имеет тонкие отличия в результирующей схеме. И есть отдельная важная тема: разница между предикатом в ON и предикатом в WHERE для OUTER JOIN — то, на чём ломаются 90% производственных запросов.
USING: короткая запись для одноимённых колонок
Если в обеих таблицах есть колонка с одним и тем же именем — можно написать USING (имя). Это эквивалентно ON A.имя = B.имя, плюс одно важное отличие: общая колонка появится в результате в единственном экземпляре, без префикса таблицы.
В нашей схеме нет одноимённых FK (мы их специально называем customer_id, order_id, чтобы избегать путаницы). Но если бы переименовали колонки — USING стал бы доступен.
Демонстрация USING на нашей схеме — потребуется переименование:
Заметь: в результате колонка id появляется один раз. Если написать SELECT * — это удобно, не нужно решать, какая id чья. В ON c.id = o.id обе колонки попадают в результат (c.id и o.id), даже если значения совпадают.
Разница в схеме результата
USING коалесцирует общую колонку. В OUTER JOIN это даёт неожиданное поведение для NULL.
В случае LEFT JOIN + USING коалесцированная колонка ведёт себя по-особому. Если справа пары нет (b.id IS NULL) — USING-колонка всё равно содержит a.id, потому что она «уже выбрана» из левой таблицы. С ON ты увидел бы a.id = 5, b.id = NULL. С USING (id) ты увидишь просто id = 5. Это упрощает код, но иногда скрывает, что пары не было.
Когда USING ок, когда нет
USING безопасен и удобен в одном случае: когда схема таблиц стабильна и одноимённые колонки осмысленны (id, user_id, …). Если в одну из таблиц добавят колонку с тем же именем, что в другой — USING не пострадает, потому что использует только явно указанные колонки. В отличие от NATURAL JOIN, который автоматически берёт все общие колонки.
Реальное правило большинства команд:
JOIN ... ON ...— дефолт, всегда работает.USING (...)— допустим, если ты часто пишешь join’ы по одинаково названным колонкам и тебя устраивает коалесценция.NATURAL JOIN— нельзя.
Самое важное: ON vs WHERE для OUTER JOIN
Это тема, на которой обжигаются все. В уроке 4.1 мы её затронули — теперь разберём детально.
Для INNER JOIN разницы нет: INNER JOIN ON p и JOIN ON ... WHERE p дают одинаковый результат. Оптимизатор может перетащить предикат туда-сюда — это эквивалентно.
Для OUTER JOIN разница принципиальна. Предикат в ON — это правило соединения. Предикат в WHERE — это фильтр после соединения.
Сравнение: фильтр в ON vs фильтр в WHERE для LEFT JOIN
Вариант B: тот же фильтр, но в WHERE — результат другой
Вариант A: «все клиенты, плюс их delivered-заказы, если есть». Клиенты без delivered-заказов в выводе остаются (с NULL в правых колонках).
Вариант B: «клиенты с их заказами, затем оставь только строки, где status = delivered». Клиенты без delivered-заказов после WHERE отсеются — o.status IS NULL для них, а NULL = 'delivered' это NULL, не TRUE. LEFT JOIN молча превращается в INNER.
Простое правило
- Предикат на правую таблицу в LEFT JOIN → пиши в
ON. - Предикат на левую таблицу в LEFT JOIN → можно писать в
WHERE, никакой разницы (оптимизатор поднимет фильтр перед JOIN). - Предикат межтабличный (вроде
c.signup_date < o.placed_at) — тоже вONдля LEFT, в WHERE для INNER без разницы.
Для FULL OUTER JOIN правило ещё строже: любой WHERE-фильтр, который требует не-NULL в любой из таблиц, превращает FULL в INNER (или в одну из сторон). Если хочешь сохранить семантику FULL OUTER — все фильтры держи в ON, а в WHERE оставляй только ... IS NULL для выявления сирот.
FULL OUTER + WHERE — типичная ошибка. Фильтр в WHERE убивает сирот:
В этом запросе мы потеряем и заказы без платежей, и платежи без заказов (хотя последних в нашей вселенной нет). Сравни с правильной версией:
Тот же фильтр в ON: сирот сохраняем, фильтруем только метод платежа:
NULL-семантика: ON vs WHERE — итог
| Случай | Поведение |
|---|---|
| INNER JOIN + предикат в ON | Идентично WHERE |
| INNER JOIN + предикат в WHERE | Идентично ON |
| LEFT JOIN + предикат на правую таблицу в ON | Сохраняет сирот; правая часть NULL для несовпавших |
| LEFT JOIN + предикат на правую таблицу в WHERE | Схлопывается в INNER; сироты теряются |
| LEFT JOIN + IS NULL на правую в WHERE | Anti-join: оставляет только сирот |
| FULL JOIN + любой нетривиальный фильтр в WHERE | Часто превращает в одно из направлений |
Чек-лист
USING (col)— короткая запись для одноимённых колонок; коалесцирует колонку в результате.ON— универсальный синтаксис, работает всегда.- Для INNER JOIN место предиката (ON vs WHERE) не важно.
- Для LEFT JOIN предикат на правую таблицу в WHERE схлопывает JOIN в INNER. Хочешь сохранить сирот — пиши в ON.
- Для FULL OUTER WHERE-фильтры на любую сторону часто ломают семантику. Все фильтры — в ON, в WHERE только
IS NULLдля anti-join’а. - Soft delete + LEFT JOIN:
WHERE deleted_at IS NULLоставит и сирот, и активные пары. Часто это и нужно.