Write a query using EXISTS to find suppliers who have never had a late delivery.

Answer

Use NOT EXISTS to find suppliers with no matching row in a late-delivery record.

Solution

SELECT s.supplier_id, s.supplier_name
FROM Suppliers s
WHERE NOT EXISTS (
  SELECT 1 FROM Deliveries d
  WHERE d.supplier_id = s.supplier_id AND d.is_late = 1
);

Step by Step

1. For each supplier, the subquery checks whether any delivery row exists for that supplier where is_late = 1.
2. NOT EXISTS keeps only suppliers where that subquery finds zero matching rows - meaning every one of their deliveries was on time.
3. The database can stop scanning a supplier's deliveries the instant it finds one late delivery, making EXISTS/NOT EXISTS efficient for this kind of check.

Alternative Method: LEFT JOIN with IS NULL

SELECT s.supplier_id, s.supplier_name
FROM Suppliers s
LEFT JOIN Deliveries d ON d.supplier_id = s.supplier_id AND d.is_late = 1
WHERE d.delivery_id IS NULL;

This achieves the same result: the join condition includes the late-delivery filter, so suppliers with no late deliveries get an unmatched (NULL) right side, which the outer WHERE ... IS NULL then keeps.

Key Exam Traps

• NOT IN vs NOT EXISTS: Using WHERE supplier_id NOT IN (SELECT supplier_id FROM Deliveries WHERE is_late = 1) looks equivalent but breaks silently if any supplier_id value in the subquery is NULL - the entire NOT IN then returns no rows at all. NOT EXISTS has no such issue and is the safer default.
• Suppliers with zero deliveries at all are correctly included by this query (they trivially have no late ones) - confirm whether that's the intended interpretation of "never had a late delivery," since a supplier with no track record isn't quite the same claim as one with a proven perfect record.
• Late-delivery flag placement: Putting the is_late = 1 condition in the join's ON clause versus a separate WHERE changes the result for the LEFT JOIN version specifically - the same pitfall covered in earlier join-based questions in this set.