SQL: bog'lanishlar va JOIN

Relatsion bazaning eng kuchli tomoni — jadvallarni bir-biriga bog'lash qobiliyatidir. Ma'lumot bir joyda takrorlanmasligi uchun uni bir necha jadvalga bo'lamiz, so'ng JOIN orqali birlashtirib o'qiymiz. Ushbu darsda kalitlar, munosabatlar, JOIN turlari va guruhlash bilan tanishamiz.

Nima uchun ma'lumotni bo'lamiz?

Aytaylik, foydalanuvchilar va ularning buyurtmalarini bitta jadvalda saqlamoqchimiz. Har buyurtma qatorida foydalanuvchi ismini takrorlashga to'g'ri keladi:

Yomon usul (hamma narsa bitta jadvalda):

 order_id |  user_name | product   | price
 -------- + ---------- + --------- + -----
    1     |   Ali      | Kitob     | 50
    2     |   Ali      | Ruchka    | 5
    3     |   Vali     | Daftar    | 10

Bu yerda "Ali" ikki marta yozilgan. Agar Ali ismini o'zgartirsak, ikkala qatorni ham yangilashimiz kerak. Ma'lumot ko'p bo'lsa, bu chalkashlik va xatolikka olib keladi. To'g'ri yechim — ma'lumotni ikki jadvalga bo'lish:

users jadvali:            orders jadvali:
 id |  name              id | user_id | product | price
 -- + -----             -- + ------- + ------- + -----
  1 |  Ali               1 |    1    | Kitob   |  50
  2 |  Vali              2 |    1    | Ruchka  |   5
                        3 |    2    | Daftar  |  10

Endi "Ali" faqat bir marta saqlanadi. orders jadvalidagi user_id ustuni qaysi foydalanuvchiga tegishli ekanligini ko'rsatadi. Bu — jadvallar orasidagi bog'lanish.

PRIMARY KEY — asosiy kalit

Har bir jadvalda odatda asosiy kalit (primary key) bo'ladi — bu qatorni yagona aniqlaydigan ustun. Uning qiymati takrorlanmaydi va hech qachon NULL bo'lmaydi. Odatda bu id ustuni:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Yuqorida id — asosiy kalit. Bazadagi har bir foydalanuvchining o'ziga xos idsi bor: 1, 2, 3... Ana shu id orqali biz uni boshqa jadvallardan aniq topa olamiz.

FOREIGN KEY — tashqi kalit

Tashqi kalit (foreign key) — bir jadvaldagi ustun boshqa jadvalning asosiy kalitiga ishora qiladi. orders jadvalidagi user_id — bu tashqi kalit, u users jadvalining idsiga bog'langan:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  product VARCHAR(100),
  price DECIMAL(10, 2)
);

REFERENCES users(id) qismi bazaga aytadi: "bu user_id ustuni users jadvalining idsiga ishora qiladi". Bu bog'lanish ma'lumot butunligini kafolatlaydi.

Munosabatlar: 1:N

Jadvallar orasida turli munosabatlar bo'ladi. Eng keng tarqalgani — bir-ko'p (one-to-many, 1:N):

• 1:N (bir-ko'p) — bir foydalanuvchining ko'p buyurtmasi bo'lishi mumkin, lekin har buyurtma faqat bitta foydalanuvchiga tegishli. Yuqoridagi misol shunday;
• 1:1 (bir-bir) — bir foydalanuvchining bitta profili (kamroq uchraydi);
• N:M (ko'p-ko'p) — masalan, bir talaba ko'p kursga yozilishi, bir kursda ko'p talaba bo'lishi mumkin. Bu holatda oraliq (bog'lovchi) jadval kerak bo'ladi.

JOIN — jadvallarni birlashtirish

Endi eng qiziq qismi. Bizga "har buyurtma qaysi foydalanuvchiniki" degan savolga javob kerak — ya'ni ikki jadvalni birlashtirib o'qishimiz kerak. Buning uchun JOIN ishlatiladi.

INNER JOIN

INNER JOIN ikki jadvaldagi mos keluvchi qatorlarni birlashtiradi. Faqat ikkala tomonda ham mosligi bor qatorlar natijaga tushadi:

SELECT users.name, orders.product, orders.price
FROM orders
INNER JOIN users ON orders.user_id = users.id;

ON orders.user_id = users.id — bu bog'lanish sharti: buyurtmadagi user_id foydalanuvchining idsiga teng bo'lgan qatorlarni birlashtir. Natija:

 name  | product | price
 ----- + ------- + -----
 Ali   | Kitob   |  50
 Ali   | Ruchka  |   5
 Vali  | Daftar  |  10

LEFT JOIN

LEFT JOIN chap jadvaldagi (FROM'dan keyingi) barcha qatorlarni oladi, o'ng jadvalda mos kelmasa ham. Mos kelmagan joylar NULL bilan to'ldiriladi:

-- barcha foydalanuvchilar, buyurtmasi bo'lmaganlar ham
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

Agar biror foydalanuvchining hech qanday buyurtmasi bo'lmasa, u baribir natijada ko'rinadi, lekin product ustuni NULL bo'ladi. Bu "buyurtma bermagan foydalanuvchilarni topish" kabi savollar uchun juda foydali.

RIGHT JOIN

RIGHT JOIN — LEFT JOIN'ning aksi: o'ng jadvaldagi barcha qatorlarni oladi. Amaliyotda kamroq ishlatiladi, chunki jadvallar o'rnini almashtirib LEFT JOIN qilish mumkin.

GROUP BY va agregat funksiyalar

Ko'pincha bizga alohida qatorlar emas, umumlashtirilgan ma'lumot kerak: "nechta buyurtma bor?", "jami summa qancha?", "o'rtacha narx?". Buning uchun agregat funksiyalar ishlatiladi:

• COUNT(*) — qatorlar sonini sanaydi;
• SUM(ustun) — qiymatlar yig'indisi;
• AVG(ustun) — o'rtacha qiymat;
• MIN(ustun) / MAX(ustun) — eng kichik / eng katta qiymat.

Oddiy agregat (butun jadval bo'yicha):

-- jami nechta buyurtma bor?
SELECT COUNT(*) FROM orders;

-- barcha buyurtmalar summasi va o'rtacha narxi
SELECT SUM(price), AVG(price) FROM orders;

Endi eng muhimi. GROUP BY qatorlarni guruhlarga bo'ladi va agregatni har bir guruh uchun hisoblaydi. Masalan, "har bir foydalanuvchi nechta buyurtma bergan?":

SELECT user_id, COUNT(*) AS buyurtmalar_soni
FROM orders
GROUP BY user_id;

Natija — har bir user_id uchun bitta qator, va uning yonida buyurtmalar soni. AS kalit so'zi natija ustuniga nom (taxallus) beradi.

GROUP BY'ni JOIN bilan birlashtirib, ismlar bilan chiroyliroq natija olamiz:

SELECT u.name, COUNT(o.id) AS buyurtmalar_soni, SUM(o.price) AS jami
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

HAVING — guruhlarni filtrlash

WHERE alohida qatorlarni filtrlaydi, lekin u guruhlashdan oldin ishlaydi. Guruhlangan natijani (agregatni) filtrlash uchun HAVING ishlatiladi:

-- 2 tadan ortiq buyurtma bergan foydalanuvchilar
SELECT user_id, COUNT(*) AS soni
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 2;

To'liq tartibda SQL so'rovi quyidagi ketma-ketlikda yoziladi:

SELECT ustunlar
FROM jadval
JOIN boshqa_jadval ON shart
WHERE qatorlar_sharti
GROUP BY ustun
HAVING guruh_sharti
ORDER BY ustun
LIMIT n;

Xulosa

• PRIMARY KEY — qatorni yagona aniqlaydigan kalit (odatda id);
• FOREIGN KEY (REFERENCES) — boshqa jadvalning kalitiga ishora, bog'lanishni yaratadi;
• Munosabatlar: eng keng tarqalgani 1:N (bir foydalanuvchi — ko'p buyurtma);
• INNER JOIN — faqat mos qatorlar; LEFT JOIN — chap jadvalning hammasi (mos kelmagani NULL bilan);
• Agregat funksiyalar: COUNT, SUM, AVG, MIN, MAX;
• GROUP BY — guruhlaydi, agregatni har guruh uchun hisoblaydi; HAVING — guruhlarni filtrlaydi (WHERE'dan farqli).