Əlavə şərtlər JOIN-da: ON ... AND ...

Artıq bilirsən ki, cədvəlləri JOIN ilə birləşdirmək olur. Amma real həyatda sadəcə açarların uyğun gəlməsi bəzən kifayət etmir. Tez-tez belə bir məsələ çıxır: məlumatları yalnız əlavə bir kriteriyaya uyğun gələndə birləşdirmək — məsələn, yalnız aktiv qeydlər, yalnız cari ilin məlumatları və ya yalnız tamamlanmış sifarişlər.

Və bax, burada ON konstruksiyasını AND ilə genişləndirmək işə yarayır.

JOIN ... ON-da əlavə şərtlər birləşmədə hansı sətrlərin iştirak edəcəyinə tam nəzarət etməyə imkan verir, hələ SQL nəticəni qurmağa başlamamışdan əvvəl. Bu sorğunu belə edir:

• Daha sürətli (daha az sətir JOIN-dan keçir),
• Daha dəqiq (filtrasiya birləşmə mərhələsində baş verir),
• Daha proqnozlaşdırıla bilən LEFT JOIN ilə işləyəndə (WHERE-da filtrasiyadan fərqli olaraq).

Nümunə: Yalnız kurslara aktiv yazılanlar

Tutaq ki, enrollments cədvəlində tələbənin iştirak statusu var: active, dropped, pending.

students cədvəli:

enrollments cədvəlini yeniləyək:

courses cədvəli:

İndi istəyirik ki, yalnız aktiv kursları olan tələbələri əldə edək:

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;

Nəticə:

Burada AND enrollments.status = 'active' ON-un içində əlavə etdik ki, birləşmə yalnız aktiv qeydlərə görə baş versin, nəticədən sonra filtrasiya olmasın.

Niyə WHERE yox?

Belə də yazmaq olardı:

...
WHERE enrollments.status = 'active'

Amma bu LEFT JOIN-da fərqli davranır. WHERE-da filtrasiya uyğunluq olmayan sətrləri (NULL) silir və nəticədə LEFT JOIN-ı INNER JOIN-a çevirir.

Amma AND enrollments.status = 'active' şərti ON-un içində birləşən sətrləri dərhal məhdudlaşdırır — birləşməyə ümumiyyətlə hansı sətrlərin düşəcəyinə nəzarət edir, sadəcə nəticəni sonra filtrasiya etmir.

Bu yanaşma xüsusilə vacibdir, əgər istəyirsənsə ki, bir cədvəldə olan sətrlər qalsın, hətta digər cədvəldə uyğun dəyər olmasa belə (bu, hesabat və analitikada tez-tez olur).

ON ... AND ... istifadəsinə başqa nümunələr

students cədvəli:

enrollments cədvəli:

courses cədvəli:

Nümunə: yalnız cari ilin kursları

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;

Burada yalnız cari ilə aid olan qeydləri birləşdiririk.

Nümunə: dəyərə görə istisna

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

Çıxarılmış tələbələri birləşmə mərhələsində istisna edirik, nəticədən sonra yox.

Şərt ON-un içində olanda, PostgreSQL birləşmə planını optimallaşdıra və daha az sətir işləyə bilər. Bu, böyük həcmli məlumatlarda xüsusilə vacibdir. Daxili filtrasiya JOIN-dan sonra "təmizləmə"dən daha effektivdir.

JOIN ON — yalnız açarlar üçün deyil

Çoxları düşünür ki, ON — sadəcə id = id-dir. Əslində ora əlavə etmək olar:

• Logical operatorlar: AND, OR, NOT
• Müqayisələr: >, <, <>, BETWEEN, IN
• Expressions: EXTRACT, DATE_TRUNC, COALESCE, NULLIF

Hamısını birləşdiririk

students cədvəli:

faculties cədvəli:

courses cədvəli:

enrollments cədvəli:

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

Burada eyni anda filtrasiya edirik:

• Aktiv qeydlərə,
• "Fizkultura"dan başqa kurslara,
• Adı olan fakültələrə.

Sorğunun nəticəsi:

Ümid edirəm bu leksiya xoşuna gəldi. Sorğularda bir neçə JOIN və filtr tez-tez istifadə edəcəksən. Demək olar ki, həmişə :)