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.