JOIN va LATERAL

JOIN ni siz bilasiz. Bu bo'limda uning nozik joylarini va PostgreSQL ning LATERAL imkoniyatini ko'ramiz.

CREATE TABLE muallif (
    id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ism text NOT NULL
);

CREATE TABLE kitob (
    id         int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sarlavha   text NOT NULL,
    muallif_id int REFERENCES muallif(id),
    narx       numeric(10,2) NOT NULL,
    sotilgan   int NOT NULL DEFAULT 0
);

INSERT INTO muallif (ism) VALUES
    ('Abdulla Qodiriy'), ('Cho''lpon'), ('Oybek'), ('Hali kitobsiz');

INSERT INTO kitob (sarlavha, muallif_id, narx, sotilgan) VALUES
    ('O''tkan kunlar',    1, 45000, 320),
    ('Mehrobdan chayon',  1, 38000, 210),
    ('Obid ketmon',       1, 29000,  95),
    ('Kecha va kunduz',   2, 42000, 180),
    ('Bahor qaytmaydi',   2, 35000, 140),
    ('Navoiy',            3, 52000, 260),
    ('Qutlug'' qon',      3, 48000, 175),
    ('Nomsiz kitob',   NULL, 15000,   5);

JOIN turlari #

INNER JOIN faqat mos kelgan qatorlarni beradi:

SELECT m.ism, k.sarlavha
FROM muallif m
JOIN kitob k ON k.muallif_id = m.id
ORDER BY m.ism, k.sarlavha
LIMIT 4;

       ism       |     sarlavha
-----------------+------------------
 Abdulla Qodiriy | Mehrobdan chayon
 Abdulla Qodiriy | O'tkan kunlar
 Abdulla Qodiriy | Obid ketmon
 Cho'lpon        | Bahor qaytmaydi
(4 rows)

LEFT JOIN esa chap jadvalning hamma qatorini saqlaydi:

SELECT m.ism, count(k.id) AS kitoblar
FROM muallif m
LEFT JOIN kitob k ON k.muallif_id = m.id
GROUP BY m.ism
ORDER BY kitoblar DESC, m.ism;

       ism       | kitoblar
-----------------+----------
 Abdulla Qodiriy |        3
 Cho'lpon        |        2
 Oybek           |        2
 Hali kitobsiz   |        0
(4 rows)

"Hali kitobsiz" muallifi ham chiqdi - 0 bilan. INNER JOIN da u umuman ko'rinmasdi.

Yo'qolgan qatorlarni topish #

LEFT JOIN ning klassik ishlatilishi - "bog'lanmagan" qatorlarni topish:

SELECT k.sarlavha
FROM kitob k
LEFT JOIN muallif m ON m.id = k.muallif_id
WHERE m.id IS NULL;

   sarlavha
--------------
 Nomsiz kitob
(1 row)

WHERE m.id IS NULL - bu "moslik topilmadi" degani. Shu yo'l bilan yetim qolgan yozuvlarni topish mumkin.

LATERAL - har guruhdan eng yaxshi N ta #

Endi qiyinroq vazifa: har bir muallifning eng ko'p sotilgan ikkita kitobi.

Oddiy JOIN bilan buni yozib bo'lmaydi - chunki ichki so'rovga tashqi qatorning qiymati kerak. LATERAL aynan shunga ruxsat beradi:

SELECT m.ism, top.sarlavha, top.sotilgan
FROM muallif m
JOIN LATERAL (
    SELECT k.sarlavha, k.sotilgan
    FROM kitob k
    WHERE k.muallif_id = m.id
    ORDER BY k.sotilgan DESC
    LIMIT 2
) top ON true
ORDER BY m.ism, top.sotilgan DESC;

       ism       |     sarlavha     | sotilgan
-----------------+------------------+----------
 Abdulla Qodiriy | O'tkan kunlar    |      320
 Abdulla Qodiriy | Mehrobdan chayon |      210
 Cho'lpon        | Kecha va kunduz  |      180
 Cho'lpon        | Bahor qaytmaydi  |      140
 Oybek           | Navoiy           |      260
 Oybek           | Qutlug' qon      |      175
(6 rows)

LATERAL ni LEFT JOIN bilan ishlatish ham mumkin - u holda kitobi yo'q muallif ham qoladi:

SELECT m.ism, top.sarlavha
FROM muallif m
LEFT JOIN LATERAL (
    SELECT k.sarlavha FROM kitob k
    WHERE k.muallif_id = m.id
    ORDER BY k.sotilgan DESC LIMIT 1
) top ON true
ORDER BY m.ism;

       ism       |    sarlavha
-----------------+-----------------
 Abdulla Qodiriy | O'tkan kunlar
 Cho'lpon        | Kecha va kunduz
 Hali kitobsiz   |
 Oybek           | Navoiy
(4 rows)

Jadvalni o'zi bilan birlashtirish #

Bir xil jadvalning ikki qatorini solishtirish kerak bo'lsa, uni ikki marta ishlatish mumkin:

SELECT a.sarlavha AS birinchi, b.sarlavha AS ikkinchi, a.narx
FROM kitob a
JOIN kitob b ON a.narx = b.narx AND a.id < b.id;

 birinchi | ikkinchi | narx
----------+----------+------
(0 rows)

a.id < b.id sharti ikkita narsani qiladi: qatorni o'zi bilan solishtirishni to'xtatadi va har juftlikni bir marta ko'rsatadi.

Xulosa #

• INNER JOIN faqat mos kelgan qatorlarni, LEFT JOIN esa chapdagi hammasini qaytaradi.
• Savolni o'zbekcha gapda ayting - kerakli JOIN turi o'z-o'zidan aniqlanadi.
• LEFT JOIN da count(*) ishlatmang - u mos kelmagan qatorni ham 1 deb sanaydi.
• To'g'ri yozuv: count(bog'langan_ustun).
• WHERE ... IS NULL bilan bog'lanmagan (yetim) qatorlarni topish mumkin.
• LATERAL ichki so'rovga tashqi qatorga murojaat qilish imkonini beradi.
• U "har guruhdan eng yaxshi N ta" vazifasini tabiiy hal qiladi.
• LEFT JOIN LATERAL ... ON true bo'sh guruhlarni ham saqlaydi.
• Jadvalni o'zi bilan birlashtirganda a.id < b.id shartini qo'ying.
• RIGHT JOIN kamdan-kam kerak - jadvallarni almashtirib LEFT yozish tushunarliroq.

Keyingi bo'limda agregat funksiyalarning PostgreSQL ga xos kengaytmalarini ko'ramiz.