Çoxluq əməliyyatları və mürəkkəb birləşmələr

Məktəb müştərilərə və şagirdlərə eyni bülleteni göndərmək istəyir — iki cədvəldən bir ünvan siyahısı lazımdır. Direktor soruşur: «Hansı şəhərlərdə həm şagirdimiz, həm də müştərimiz var?», satış şöbəsi isə «Heç vaxt elektronika almayan müştəriləri» axtarır. Bu suallar riyaziyyatdakı çoxluqlar dilində ən asan cavablanır: birləşmə, kəsişmə, fərq. SQL-də onlar üçün xüsusi operatorlar var, bir neçə «qeyri-adi» birləşmə üsulu isə mənzərəni tamamlayır.

UNION və UNION ALL

UNION iki sorğunun nəticəsini bir-birinin altına yazır və təkrarları silir, UNION ALL isə təkrarları saxlayır. Qaydalar: hər iki sorğuda sütunların sayı eyni olmalı, tipləri uyğun gəlməlidir; sütun adları birinci sorğudan götürülür; ORDER BY yalnız bir dəfə, ən sonda yazılır və bütün nəticəyə aid olur.

SELECT
  (SELECT COUNT(*) FROM (SELECT city FROM students
                         UNION
                         SELECT city FROM customers)) AS with_union,
  (SELECT COUNT(*) FROM (SELECT city FROM students
                         UNION ALL
                         SELECT city FROM customers)) AS with_union_all;

with_union | with_union_all
11 | 19

SELECT 'student' AS type, first_name AS name, city
FROM students
WHERE city = 'Bakı'
UNION ALL
SELECT 'customer', name, city
FROM customers
WHERE city = 'Bakı'
ORDER BY type, name;

type | name | city
customer | Anar Mustafayev | Bakı
customer | Zəhra Hüseynli | Bakı
student | Aysel | Bakı
student | Kamran | Bakı
student | Leyla | Bakı
student | Rəşad | Bakı

INTERSECT və EXCEPT

INTERSECT hər iki nəticədə olan sətirləri (kəsişmə), EXCEPT isə birinci nəticədə olub ikincidə olmayan sətirləri (fərq) qaytarır. Hər ikisi təkrarları silir. Aşağıda həm şagirdimiz, həm də müştərimiz olan şəhərləri, sonra isə yalnız şagirdlərin yaşadığı şəhərləri tapırıq.

SELECT city FROM students
INTERSECT
SELECT city FROM customers
ORDER BY city;

city
Bakı
Gəncə

SELECT city FROM students
EXCEPT
SELECT city FROM customers
ORDER BY city;

city
Lənkəran
Naxçıvan
Quba
Sumqayıt
Şəki

Özünə birləşmə və CROSS JOIN

Özünə birləşmədə eyni cədvəl iki müxtəlif ləqəblə iki dəfə işlədilir — sanki onun iki nüsxəsi var. Məsələn, eyni şəhərdən olan şagirdləri cüt-cüt tanış etmək istəyirik. a.city = b.city şərti şəhərləri tutuşdurur, a.id < b.id isə hər cütü yalnız bir dəfə saxlayır.

SELECT a.first_name AS student_1,
       b.first_name AS student_2,
       a.city
FROM students AS a
JOIN students AS b ON b.city = a.city AND a.id < b.id
ORDER BY a.city, a.id, b.id;

student_1 | student_2 | city
Aysel | Leyla | Bakı
Aysel | Rəşad | Bakı
Aysel | Kamran | Bakı
Leyla | Rəşad | Bakı
Leyla | Kamran | Bakı
Rəşad | Kamran | Bakı
Murad | Tural | Gəncə
Elvin | Orxan | Sumqayıt

CROSS JOIN bilərəkdən bütün kombinasiyaları qurur. Adətən səhvdir, amma bəzən məhz lazımdır: «hər şagird × hər kurs» cədvəli qurub ona LEFT JOIN ilə faktiki yazılışları qoşsaq, kimin hansı kursa yazılmadığını görərik. Aşağıda 8-ci sinif şagirdləri və riyaziyyat kursları üçün belə matris var: NULL — yazılış yoxdur.

SELECT s.first_name, c.title, e.score
FROM students AS s
CROSS JOIN courses AS c
LEFT JOIN enrollments AS e
       ON e.student_id = s.id AND e.course_id = c.id
WHERE s.grade = 8 AND c.subject = 'Math'
ORDER BY s.id, c.id;

first_name | title | score
Leyla | Algebra | NULL
Leyla | Geometry | 95
Günay | Algebra | NULL
Günay | Geometry | NULL
Orxan | Algebra | NULL
Orxan | Geometry | 70

Anti-birləşmə: NOT EXISTS

«Heç vaxt elektronika almayan müştərilər» — anti-birləşmə sualıdır: sol cədvəldən sağ cədvəldə cütü olmayan sətirləri saxlayırıq. Ən etibarlı yazılış NOT EXISTS-dir: alt sorğu hər müştəri üçün «elektronika sifarişi varmı?» sualını verir və cavab «yox»dursa, müştəri nəticəyə düşür.

SELECT c.name, c.country
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Electronics'
)
ORDER BY c.name;

name | country
John Carter | United Kingdom
Mehmet Kaya | Türkiye
Olga Ivanova | Russia

Əks sual — «ən azı bir dəfə elektronika alanlar» — EXISTS ilə yazılır (yarım-birləşmə). Onu adi JOIN ilə yazsan, iki elektronika sifarişi olan Anar nəticədə iki dəfə görünər; EXISTS isə hər müştərini bir dəfə qaytarır, çünki cütü tapan kimi axtarışı dayandırır.

SELECT c.title
FROM courses AS c
JOIN enrollments AS e ON e.course_id = c.id
JOIN students AS s ON s.id = e.student_id
WHERE s.city = 'Bakı'
-- INTERSECT the same query for 'Sumqayıt'
ORDER BY title;

title
Geometry
Mechanics

SELECT s.first_name
FROM students AS s
WHERE NOT EXISTS (
  -- an enrollment of this student in a Math course
)
ORDER BY s.first_name;

first_name
Elvin
Günay
Kamran
Rəşad
Tural