Subquery yoki JOIN — qachon qaysi biri

"Kamida bitta buyurtmasi bor mijozlar" — buni IN, EXISTS yoki JOIN bilan yozish mumkin. Uchalasi ham ishlaydi, lekin natija har doim bir xil emas. Farqni bilish sizni yashirin dublikatlardan qutqaradi.

Jadval: customers

Jadval: orders

Uch xil yechim

-- 1. IN
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- 2. EXISTS
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

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

IN va EXISTS natijasi:

JOIN natijasi esa:

Aziz ikki marta chiqdi, chunki unda ikkita buyurtma bor. JOIN har bir mos juftlik uchun bitta qator yasaydi.

Asosiy qoida

• "Bormi?" degan savol (filtr) — EXISTS yoki IN. Ular chap jadval qatorlarini ko'paytirmaydi.
• Boshqa jadvaldan ustun kerak (buyurtma summasi, sanasi) — JOIN.

Ko'p uchraydigan xato: JOIN dan keyin SUM

Vazifa: har bir mijozning jami xaridi va to'lovlari soni. payments jadvalida Azizning 3 ta to'lovi bor deylik.

SELECT c.name, SUM(o.amount) AS total, COUNT(p.id) AS payments
FROM customers c
JOIN orders o   ON o.customer_id = c.id
JOIN payments p ON p.customer_id = c.id
GROUP BY c.name;

Aziz uchun total 200 000 emas, 600 000 chiqadi: 2 ta buyurtma × 3 ta to'lov = 6 ta qator, har bir buyurtma summasi uch martadan qo'shildi. Bu "fan-out" (qatorlar ko'payishi) deb ataladi.

To'g'risi — har bir jadvalni avval alohida agregatlab, keyin birlashtirish:

SELECT c.name, o.total, p.payments
FROM customers c
JOIN (SELECT customer_id, SUM(amount) AS total
      FROM orders GROUP BY customer_id) o ON o.customer_id = c.id
LEFT JOIN (SELECT customer_id, COUNT(*) AS payments
           FROM payments GROUP BY customer_id) p ON p.customer_id = c.id;

Subquery qiymat sifatida

Subquery bitta qiymat qaytarsa, uni oddiy son kabi solishtirish mumkin:

SELECT name, amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE amount > (SELECT AVG(amount) FROM orders);

O'rtacha 83 333.33, natijada faqat Azizning 120 000 lik buyurtmasi qoladi.

Xulosa

• Filtr ("bormi / yo'qmi") — EXISTS / IN; ular qatorlarni ko'paytirmaydi.
• Boshqa jadval ustunlari kerak — JOIN, lekin bitta qatorga bir nechta mos kelishini unutmang.
• Bir nechta "ko'p" jadvalni JOIN qilib SUM qilsangiz, avval har birini alohida agregatlang.
• Teskari vazifa ("yo'qlar") uchun NOT EXISTS ni tanlang — NOT IN NULL da buziladi.