Mürəkkəb iç-içə sorğular nümunələri: EXISTS, IN, HAVING birləşməsi

Təbriklər, artıq həqiqətən maraqlı yerə gəldik! Bu gün müxtəlif subquery-ləri necə birləşdirib çətin məsələləri həll etməyi görəcəyik. EXISTS, IN, HAVING — bu trio ilə özünü database maq kimi hiss edəcəksən. Bir cədvəldən məlumat çıxaracağıq, başqa cədvəldəki məlumatlara görə filtrləyəcəyik, qruplaşdıracağıq, sonra isə qrupları filtrləyəcəyik. Bonus olaraq — sorğuları necə daha effektiv etmək olar, onu da müzakirə edəcəyik.

Gəlin, bir ümumi tapşırıq qoyaq və leksiya boyunca onu addım-addım həll edək.

Tapşırığın qoyuluşu

Tutaq ki, bizdə universitetin database-i var və orada üç cədvəl var:

students cədvəli

courses cədvəli

enrollments cədvəli

Aşağıdakı tələbələri seçmək lazımdır:

1. Ən azı bir kursa yazılmış olanlar EXISTS.
2. Yazıldığı kurslardan ən az birində qiyməti olmayanlar IN.
3. Orta balı 80-dən yuxarı olan qruplara aid olanlar HAVING.

EXISTS və IN ilə həll

Addım 1: Yazılmış tələbələri yoxlamaq (EXISTS). Ən sadə şərtdən başlayaq. Hansı tələbənin ən azı bir kursa yazıldığını tapmaq lazımdır. Bunun üçün EXISTS istifadə edə bilərik.

SELECT name
FROM students s
WHERE EXISTS (
  SELECT 1
  FROM enrollments e
  WHERE e.student_id = s.id
);

1. Xarici sorğu students cədvəlindən adları seçir.
2. Subquery-də yoxlayırıq ki, enrollments cədvəlində həmin tələbəyə uyğun qeyd var, ya yox (WHERE e.student_id = s.id).
3. SELECT 1 sadəcə qeyd olub-olmamasını yoxlamaq üçündür, məzmun vacib deyil.

Nəticə:

Artıq bilirik ki, hansı tələbələr kurslara yazılıb. Amma daha çox istəyirik. Qiyməti olmayanları filtrləmək istəyirik.

Addım 2: Qiymətin olmamasını yoxlamaq (IN + NULL). İndi filtr əlavə edirik: yalnız o tələbələr lazımdır ki, ən azı bir kursda qiyməti yoxdur. Burada IN və NULL kömək edəcək.

SELECT name
FROM students s
WHERE id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. Xarici sorğuda tələbələrin adlarını seçirik.
2. Subquery enrollments cədvəlindən student_id siyahısı qaytarır, harada grade IS NULL.

Nəticə:

Deməli, Otto — qiyməti olmayan yeganə tələbədir. Nə dramatik! Amma işimiz hələ bitməyib: yalnız orta balı 80-dən yuxarı olan qrupları nəzərə almalıyıq.

HAVING ilə həll

Addım 3: Qruplaşdırma və HAVING ilə filtrasiya.

İndi isə hər şeyi birləşdirməyin vaxtıdır. Lazımdır ki:

1. Hər qrup üçün orta balı hesablayaq.
2. Orta balı 80-dən yuxarı olan qrupları filtrləyək.
3. Bu qruplardan olan tələbələri, əvvəlki şərtləri də nəzərə alaraq çıxaraq.

SELECT name
FROM students s
WHERE s.group_id IN (
  SELECT group_id
  FROM students
  JOIN enrollments ON students.id = enrollments.student_id
  WHERE grade IS NOT NULL
  GROUP BY group_id
  HAVING AVG(grade) > 80
)
AND id IN (
  SELECT e.student_id
  FROM enrollments e
  WHERE e.grade IS NULL
);

1. Xarici sorğu bütün şərtlərə uyğun tələbələrin adlarını seçir.
2. WHERE-də birinci subquery orta balı 80-dən yuxarı olan qrupların group_id siyahısını qaytarır.
   • students ilə enrollments birləşdiririk ki, qiymətləri alaq.
   • Yalnız grade IS NOT NULL olanları filtrləyirik.
   • Məlumatları group_id üzrə qruplaşdırırıq.
   • Qrupları HAVING ilə filtrləyirik.
3. WHERE-də ikinci subquery tələbənin ən azı bir kursda qiyməti olmadığını yoxlayır.
4. Hər iki şərt AND ilə birləşdirilib.

Nəticə:

Beləliklə, Otto — təkcə qiyməti olmayan tələbə deyil, həm də uğurlu qrupun üzvüdür.

Yanaşmaların müqayisəsi: EXISTS vs IN

EXISTS ən yaxşısı, sadəcə qeyd olub-olmadığını tez yoxlamaq istəyəndə işləyir. O, ilk qeyd tapılan kimi axtarışı dayandırır, bu da böyük cədvəllər üçün vacibdir.

Eyni zamanda IN məlumatın məzmununa fokuslananda faydalıdır. Məsələn, filtr üçün id siyahısı çıxaranda. Amma unutma ki, çoxlu dəyər qaytaran subquery-lər üçün IN yavaş ola bilər.

HAVING nə vaxt istifadə olunur

Aqreqasiya olunmuş məlumatlarda, nəticəyə görə filtr lazımdırsa, HAVING ən yaxşı seçimdir. Amma şərti WHERE-ə köçürmək mümkündürsə (məsələn, sütuna görə filtr), sorğunu sadələşdir və sürətləndir.

Tam nümunə

Pekişdirmək üçün başqa bir nümunə: elə qrupları seç ki, orada ən azı bir tələbənin qiyməti 75-dən aşağıdır, amma heç biri "Fəlsəfə" kursuna yazılmayıb.

Xatırladaq, cədvəllərimiz:

students cədvəli

courses cədvəli

enrollments cədvəli

SELECT DISTINCT group_id
FROM students s
WHERE group_id IN (
  SELECT s.group_id
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.grade < 75
)
AND group_id NOT IN (
  SELECT s.group_id                                 -- 1-ci səviyyəli iç-içə sorğu
  FROM students s
  JOIN enrollments e ON s.id = e.student_id
  WHERE e.course_id = (
    SELECT id FROM courses WHERE name = 'Fəlsəfə' -- 2-ci səviyyəli iç-içə sorğu :P
  )
);

1. Birinci subquery qiyməti 75-dən aşağı olan tələbə olan qrupları seçir.
2. İkinci subquery "Fəlsəfə" kursu ilə əlaqəli qrupları çıxarır.
3. IN və NOT IN ilə şərtləri birləşdiririk və nəticəni alırıq.

Nəticə:

Bu nə qədər faydalıdır?

Real həyatda belə yanaşmalar, məlumatlar arasında mürəkkəb əlaqələri analiz edəndə çox kömək edir. Məsələn:

• Analitikada "xüsusi" müştəri qruplarını (VIP, problemli və s.) seçmək üçün.
• Tövsiyə sistemləri hazırlayanda, istifadəçini bir çox kriteriyaya görə filtrləyəndə.
• Müsahibədə, mürəkkəb SQL-sorğunu optimallaşdırmaq istəyəndə.

Çalış, məşq elə! Bu, ustalığa gedən yoldur.