Додаткові умови в 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:

Таблиця 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 != 'Фізкультура'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

Тут ми одночасно фільтруємо по:

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

Результат виконання запиту:

Сподіваюсь, тобі сподобалась ця лекція. Ти будеш дуже часто використовувати у запитах кілька JOIN з фільтрами. Майже завжди :)