JOIN

JOIN

JOIN ikki jadval qatorlarini umumiy ustun bo‘yicha birlashtiradi. INNER JOIN (yoki shunchaki JOIN) faqat ikkala jadvalda mosligi bor qatorlarni qoldiradi.

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

SELECT s.name, c.title
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id;

Maslahatlar

• Jadvallarga qisqa nom bering (o, c) va ustunlarni shu bilan yozing — bir xil nomli ustunlar chalkashmaydi.
• ON shartini unutsangiz, har qator har qator bilan juftlanadi (n·m qator).

LEFT JOIN

LEFT JOIN chap jadvalning barcha qatorlarini qoldiradi; o‘ngda mosi bo‘lmasa, o‘ng ustunlar NULL bo‘ladi.

-- har mijoz va buyurtmalari soni (buyurtmasizlar ham, 0 bilan)
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- hech buyurtma bermagan mijozlar
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Diqqat: COUNT(*) emas, COUNT(o.id) — aks holda buyurtmasiz mijoz ham 1 deb sanaladi.