الربط ومستوى تفصيل العلاقات

WITH customers AS (
 SELECT 7 customer_id, 'Nile Lab' customer_name FROM dual
), shipments AS (
 SELECT 101 shipment_id, 7 customer_id FROM dual UNION ALL
 SELECT 102, 7 FROM dual
)
SELECT s.shipment_id, c.customer_name
FROM shipments s JOIN customers c ON c.customer_id = s.customer_id
ORDER BY s.shipment_id;

SHIPMENT_ID | CUSTOMER_NAME
101         | Nile Lab
102         | Nile Lab

تطابق كل شحنة الأب الفريد مرة واحدة. يحتفظ UNION ALL بصفّي الشحن الاصطناعيين.

SELECT s.shipment_id, COUNT(sc.scan_id) AS accepted_scans
FROM shipments s
LEFT JOIN scans sc
  ON sc.shipment_id = s.shipment_id AND sc.status = 'accepted'
GROUP BY s.shipment_id
ORDER BY s.shipment_id;

تظهر كل شحنة مرة بعد التجميع. الشحنة التي لا تملك مسحاً مقبولاً لها accepted_scans = 0.