Dodatkowe warunki w JOIN: ON ... AND ...

Już wiesz, jak łączyć tabele za pomocą JOIN. Ale w prawdziwym życiu zwykłe dopasowanie po kluczach często nie wystarcza. Często pojawia się zadanie: łączyć dane tylko jeśli spełniają dodatkowe kryterium — na przykład tylko aktywne rekordy, tylko dane z bieżącego roku albo tylko zakończone zamówienia.

I tu właśnie wchodzi rozszerzenie konstrukcji ON z użyciem AND.

Dodatkowe warunki w JOIN ... ON pozwalają dokładnie kontrolować, które wiersze biorą udział w łączeniu, zanim SQL zacznie budować wynik. Dzięki temu zapytanie jest:

• Szybsze (mniej wierszy przechodzi przez JOIN),
• Dokładniejsze (filtrowanie odbywa się już na etapie łączenia),
• Bardziej przewidywalne przy pracy z LEFT JOIN (w przeciwieństwie do filtrowania w WHERE).

Przykład: Tylko aktywne zapisy na kursy

Załóżmy, że tabela enrollments zawiera status uczestnictwa studenta: active, dropped, pending.

Tabela students:

Zaktualizowana tabela enrollments:

Tabela courses:

Teraz chcemy uzyskać tylko tych studentów, którzy mają aktywne kursy:

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;

Wynik:

Tutaj dodaliśmy AND enrollments.status = 'active' do środka ON, żeby łączenie odbywało się tylko po aktywnych rekordach, a nie filtrowało się po połączeniu.

Dlaczego nie WHERE?

Można by napisać tak:

...
WHERE enrollments.status = 'active'

Ale to ma inne zachowanie przy LEFT JOIN. Filtrowanie w WHERE usuwa wiersze, gdzie nie ma dopasowań (NULL), przez co zamienia LEFT JOIN w INNER JOIN.

A warunek AND enrollments.status = 'active' wewnątrz ON od razu ogranicza łączone wiersze — steruje tym, które wiersze w ogóle trafią do połączenia, a nie tylko filtruje wynik po fakcie.

To podejście jest szczególnie ważne, jeśli chcesz zachować wiersze z jednej tabeli, nawet jeśli w drugiej nie ma pasujących wartości (co często się zdarza w raportach i analizie).

Kolejne przykłady użycia ON ... AND ...

Tabela students:

Tabela enrollments:

Tabela courses:

Przykład: tylko kursy z bieżącego roku

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;

Tutaj łączymy tylko te rekordy, które dotyczą bieżącego roku.

Przykład: wykluczenie po wartości

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

Wykluczamy studentów, którzy się wypisali, już na etapie łączenia, a nie filtrujemy po fakcie.

Kiedy warunek jest wewnątrz ON, PostgreSQL może optymalizować plan łączenia i przetwarzać mniej wierszy. To szczególnie ważne przy dużych ilościach danych. Wewnętrzne filtrowanie jest wydajniejsze niż "odsiew" po JOIN.

JOIN ON — to nie tylko klucze

Wielu myśli, że ON to po prostu id = id. Tak naprawdę można tam wrzucać:

• Operatory logiczne: AND, OR, NOT
• Porównania: >, <, <>, BETWEEN, IN
• Wyrażenia: EXTRACT, DATE_TRUNC, COALESCE, NULLIF

Łączymy wszystko razem

Tabela students:

Tabela faculties:

Tabela courses:

Tabela 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 != 'PhysicalEducation'
JOIN faculties f
    ON c.faculty_id = f.id
    AND f.name IS NOT NULL;

Tutaj jednocześnie filtrujemy po:

• Aktywnych rekordach,
• Kursach innych niż "PhysicalEducation",
• Wydziałach, które mają nazwę.

Wynik zapytania:

Mam nadzieję, że spodobał ci się ten wykład. Bardzo często będziesz używać w zapytaniach kilku JOIN z filtrami. Prawie zawsze :)