FULL JOIN, CROSS JOIN, SELF JOIN

~15 daq · MySQL · JOIN va Munosabatlar

Muammodan boshlaylik

INNER va LEFT JOIN — 95% holatni qamraydi. Qolgan 5% uchun uchta maxsus JOIN bor:

FULL JOIN — ikki ro'yxatni solishtirish: "ombordagi hisob va buxgalteriyadagi hisob — qaysi biri qayerda yo'q?"
CROSS JOIN — barcha kombinatsiyalar: "har bir o'lcham × har bir rang"
SELF JOIN — jadvalni o'zi bilan: "har bir xodim va uning rahbari"

FULL OUTER JOIN — MySQL da yo'q

Standart SQL da FULL OUTER JOIN bor: ikkala jadvalning ham barcha qatorlari, mosi bo'lmasa — NULL.

SELECT ... FROM a FULL OUTER JOIN b ON ...;
-- MySQL: ERROR 1064: You have an error in your SQL syntax

MySQL buni qo'llab-quvvatlamaydi. Lekin uni LEFT JOIN + RIGHT JOIN + UNION bilan yasash mumkin.

Misol: ombor va hisob tizimi solishtiruvi

CREATE TABLE ombor_hisobi (sku VARCHAR(20) PRIMARY KEY, soni INT);
CREATE TABLE buxgalteriya  (sku VARCHAR(20) PRIMARY KEY, soni INT);

INSERT INTO ombor_hisobi VALUES ('A1', 10), ('A2', 5), ('A3', 7);
INSERT INTO buxgalteriya  VALUES ('A1', 10), ('A2', 4), ('A4', 2);

SELECT o.sku AS ombor_sku, o.soni AS ombor, b.sku AS bux_sku, b.soni AS bux
FROM ombor_hisobi o
LEFT JOIN buxgalteriya b ON b.sku = o.sku

UNION

SELECT o.sku, o.soni, b.sku, b.soni
FROM ombor_hisobi o
RIGHT JOIN buxgalteriya b ON b.sku = o.sku;

+-----------+-------+---------+------+
| ombor_sku | ombor | bux_sku | bux  |
+-----------+-------+---------+------+
| A1        |    10 | A1      |   10 |
| A2        |     5 | A2      |    4 |   ← soni farq qiladi
| A3        |     7 | NULL    | NULL |   ← faqat omborda
| NULL      |  NULL | A4      |    2 |   ← faqat buxgalteriyada
+-----------+-------+---------+------+

Birinchi qism — ombordagi hammasi (+ buxgalteriyadagi mosi)
Ikkinchi qism — buxgalteriyadagi hammasi (+ ombordagi mosi)
UNION — ikkalasini birlashtiradi va takrorlarni (A1, A2) olib tashlaydi (24-dars)

Faqat farqlarni ko'rsatish:

SELECT * FROM ( /* yuqoridagi so'rov */ ) AS t
WHERE ombor_sku IS NULL OR bux_sku IS NULL OR ombor <> bux;

FULL JOIN amalda kam kerak bo'ladi — asosan ikki manbani sverka (solishtirish) qilishda.

CROSS JOIN — barcha kombinatsiyalar

CROSS JOIN da ON yo'q. Birinchi jadvalning har bir qatori ikkinchi jadvalning har bir qatori bilan birlashadi:

CREATE TABLE olchamlar (olcham VARCHAR(5));
CREATE TABLE ranglar   (rang VARCHAR(20));

INSERT INTO olchamlar VALUES ('S'), ('M'), ('L');
INSERT INTO ranglar   VALUES ('Oq'), ('Qora');

SELECT o.olcham, r.rang
FROM olchamlar o
CROSS JOIN ranglar r
ORDER BY o.olcham, r.rang;

+--------+------+
| olcham | rang |
+--------+------+
| L      | Oq   |
| L      | Qora |
| M      | Oq   |
| M      | Qora |
| S      | Oq   |
| S      | Qora |
+--------+------+

3 × 2 = 6 qator. Bu — Dekart ko'paytmasi.

Qachon foydali

Mahsulot variantlarini yaratish:

INSERT INTO futbolka_variantlari (olcham, rang)
SELECT o.olcham, r.rang FROM olchamlar o CROSS JOIN ranglar r;

Bo'sh kunlarni ham ko'rsatadigan hisobot — har bir kun × har bir toifa, keyin LEFT JOIN bilan savdo ma'lumotini qo'shish. Savdo bo'lmagan kunlar 0 bilan ko'rinadi.

[!WARNING] CROSS JOIN tez "portlaydi": 10 000 × 10 000 = 100 000 000 qator. Ko'pincha CROSS JOIN tasodifan paydo bo'ladi — JOIN shartini unutganda yoki eski FROM a, b sintaksisida WHERE ni unutganda. So'rov kutilmaganda uzoq ishlasa yoki natija juda katta bo'lsa — birinchi navbatda shuni tekshiring.

SELF JOIN — jadvalni o'zi bilan

Jadval o'ziga o'zi ishora qilganda kerak bo'ladi.

Ierarxiya: xodim va rahbar

CREATE TABLE xodimlar (
    id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ism       VARCHAR(100) NOT NULL,
    lavozim   VARCHAR(100),
    rahbar_id INT UNSIGNED NULL,
    FOREIGN KEY (rahbar_id) REFERENCES xodimlar(id)
);

INSERT INTO xodimlar (ism, lavozim, rahbar_id) VALUES
    ('Bekzod',  'Direktor',          NULL),
    ('Gulnora', 'Savdo bo''limi',    1),
    ('Sardor',  'IT bo''limi',       1),
    ('Kamola',  'Sotuvchi',          2),
    ('Otabek',  'Dasturchi',         3),
    ('Zarina',  'Dasturchi',         3);

Har bir xodim — rahbarining ismi bilan:

SELECT x.ism AS xodim, x.lavozim, r.ism AS rahbar
FROM xodimlar x
LEFT JOIN xodimlar r ON r.id = x.rahbar_id;

+---------+---------------+---------+
| xodim   | lavozim       | rahbar  |
+---------+---------------+---------+
| Bekzod  | Direktor      | NULL    |
| Gulnora | Savdo bo'limi | Bekzod  |
| Sardor  | IT bo'limi    | Bekzod  |
| Kamola  | Sotuvchi      | Gulnora |
| Otabek  | Dasturchi     | Sardor  |
| Zarina  | Dasturchi     | Sardor  |
+---------+---------------+---------+

Bitta jadval ikki marta, ikki xil taxallus bilan: x — xodim sifatida, r — rahbar sifatida. Taxalluslarsiz SELF JOIN mumkin emas.

LEFT — direktorning rahbari yo'q, lekin u ro'yxatda bo'lishi kerak.

Har bir rahbarning bo'ysunuvchilari soni:

SELECT r.ism, COUNT(x.id) AS boysunuvchilar
FROM xodimlar r
LEFT JOIN xodimlar x ON x.rahbar_id = r.id
GROUP BY r.id, r.ism
HAVING COUNT(x.id) > 0;

[!INFO] SELF JOIN faqat bir daraja yuqoriga qaraydi. "Otabekning barcha rahbarlari zanjiri — Sardor, keyin Bekzod" kabi cheksiz chuqurlikdagi ierarxiya uchun — rekursiv CTE (23-dars).

Qatorlarni bir-biri bilan solishtirish

Bir xil narxdagi mahsulot juftliklari:

SELECT a.nom, b.nom, a.narx
FROM mahsulotlar a
JOIN mahsulotlar b ON a.narx = b.narx AND a.id < b.id;

a.id < b.id — har bir juftlik faqat bir marta chiqsin (A-B, lekin B-A emas) va mahsulot o'zi bilan juftlanmasin.

JOIN turlari — umumiy jadval

JOIN	Natija
`INNER JOIN`	Faqat ikkala tomonda mosi borlar
`LEFT JOIN`	Chapdagi hammasi + o'ngdagi mosi (yo'q bo'lsa `NULL`)
`RIGHT JOIN`	O'ngdagi hammasi + chapdagi mosi
`FULL JOIN`	Ikkala tomondagi hammasi (MySQL da: LEFT `UNION` RIGHT)
`CROSS JOIN`	Barcha kombinatsiyalar, `ON` siz
SELF JOIN	Jadval o'zi bilan, turli taxalluslar bilan (har qanday JOIN turi)

Nima uchun shunday?

Nima uchun MySQL da FULL OUTER JOIN yo'q, PostgreSQL da esa bor?

MySQL tarixan veb-ilovalar uchun tez va oddiy baza sifatida rivojlangan — u yerda FULL JOIN deyarli kerak bo'lmaydi. Ishlab chiquvchilar resurslarni ko'proq ishlatiladigan imkoniyatlarga sarflagan. Va FULL JOIN ni UNION bilan to'liq yasash mumkin bo'lgani uchun, bu imkoniyat hech qachon ustuvor bo'lmagan. PostgreSQL esa SQL standartiga to'liq moslikni maqsad qilgan.

Xulosa

Eslab qoling:

MySQL da FULL JOIN yo'q → LEFT JOIN ... UNION ... RIGHT JOIN
FULL JOIN — ikki manbani solishtirish (sverka) uchun
CROSS JOIN — barcha kombinatsiyalar; N × M qator — ehtiyot bo'ling
Tasodifiy CROSS JOIN — unutilgan JOIN shartining belgisi
SELF JOIN — bir jadval, ikki taxallus; ierarxiya va qatorlarni solishtirish
Chuqur ierarxiya — rekursiv CTE (23-dars)

Keyingi dars

18-dars: One-to-Many munosabat — loyihalash va so'rovlar