Дополнительные условия в JOIN: ON ... AND ...

Вы уже знаете, как объединять таблицы с помощью JOIN. Но в реальной жизни простого совпадения по ключам может быть недостаточно. Часто возникает задача: объединять данные, только если они соответствуют дополнительному критерию — например, только активные записи, только данные за текущий год или только завершённые заказы.

И вот тут в игру вступает расширение конструкции ON с помощью AND.

Дополнительные условия в JOIN ... ON позволяют точно контролировать, какие строки участвуют в соединении, ещё до того, как SQL начнёт строить результат. Это делает запрос:

• Быстрее (меньше строк проходит через JOIN),
• Точнее (фильтрация происходит на этапе соединения),
• Предсказуемее при работе с LEFT JOIN (в отличие от фильтрации в WHERE).

Пример: Только активные записи на курсы

Допустим, таблица enrollments содержит статус участия студента: active, dropped, pending.

Таблица students:

Обновим таблицу enrollments:

Таблица courses:

Теперь мы хотим получить только тех студентов, у которых активные курсы:

SELECT 
    students.name AS student_name,
    courses.name AS course_name
FROM students 
INNER JOIN enrollments 
    ON students.id = enrollments.student_id
    AND enrollments.status = 'active'
INNER JOIN courses 
    ON enrollments.course_id = courses.id;

Результат:

Здесь мы добавили AND enrollments.status = 'active' внутрь ON, чтобы соединение происходило только по активным записям, а не фильтровалось после объединения.

Почему не WHERE?

Можно было бы написать так:

...
WHERE enrollments.status = 'active'

Но это имеет другое поведение при LEFT JOIN. Фильтрация в WHERE удаляет строки, где нет совпадений (NULL), и тем самым превращает LEFT JOIN в INNER JOIN.

А вот условие AND enrollments.status = 'active' внутри ON сразу ограничивает сами соединяемые строки — управляет тем, какие строки вообще попадут в соединение, а не просто фильтрует результат после.

Такой подход особенно важен, если вы хотите сохранить строки из одной таблицы, даже если в другой таблице нет подходящих значений (что часто бывает в отчётах и аналитике).

Ещё примеры использования ON ... AND ...

Таблица students:

Таблица enrollments:

Таблица courses:

Пример: только курсы текущего года

SELECT
    students.name,
    courses.name,
    enrollments.enrolled_at
FROM students
JOIN enrollments
    ON students.id = enrollments.student_id
    AND EXTRACT(YEAR FROM enrollments.enrolled_at) = EXTRACT(YEAR FROM CURRENT_DATE)
JOIN courses
    ON enrollments.course_id = courses.id;

Здесь мы объединяем только те записи, которые относятся к текущему году.

Пример: исключение по значению

JOIN enrollments
    ON students.id = enrollments.student_id
    AND enrollments.status != 'dropped'

Исключаем студентов, которые отчислились, на этапе соединения, а не фильтруем после.

Когда условие размещено внутри ON, PostgreSQL может оптимизировать план соединения и обработать меньше строк. Это особенно критично на больших объёмах данных. Внутренняя фильтрация эффективнее, чем "отсев" после JOIN.

JOIN ON — это не только ключи

Многие думают, что ON — это просто id = id. На самом деле туда можно вставлять:

• Логические операторы: AND, OR, NOT
• Сравнения: >, <, <>, BETWEEN, IN
• Выражения: EXTRACT, DATE_TRUNC, COALESCE, NULLIF

Комбинируем всё вместе

Таблица students:

Таблица faculties:

Примечание: у факультета с id=30 имя — NULL.

Таблица courses:

Таблица enrollments:

SELECT
    s.name AS student_name,
    c.name AS course_name,
    f.name AS faculty_name
FROM students s
JOIN enrollments e
    ON s.id = e.student_id
    AND e.status = 'active'
JOIN courses c
    ON e.course_id = c.id
    AND c.name != 'PE'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

Здесь мы одновременно фильтруем по:

• Активным записям,
• Курсам, кроме "Физкультуры",
• Факультетам, у которых есть имя.

Результат выполнения запроса:

Надеюсь вам понравилась эта лекция. Вы будете очень часто использовать в запросах несколько JOIN с фильтрами. Почти всегда :)

2

Задача

SQL SELF, 12 уровень, 2 лекция

Недоступна

Выбор студентов с активными курсами

Выбор студентов с активными курсами

Открыть

2

Задача

SQL SELF, 12 уровень, 2 лекция

Недоступна

Выбор записей за текущий год

Выбор записей за текущий год

Открыть