Suppliers With No Purchase Order in 2024 — SQL Joins Problem
Procurement is cleaning up the vendor master before the next financial audit.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #374
Problem statement
Procurement is cleaning up the vendor master before the next financial audit. A supplier is dormant when no purchase order dated in calendar year 2024 was raised on them — orders from 2023 or 2025 do not keep a supplier active.
Return supplier_id and supplier_name for every dormant supplier, including suppliers that never received any order at all. Return the rows in any order.
Tables
Table: Supplier
| Column | Type |
|---|---|
| supplier_id | int |
| supplier_name | varchar |
| city | varchar |
Primary key: supplier_id.
One row per registered supplier.
Table: PurchaseOrder
| Column | Type |
|---|---|
| po_id | int |
| supplier_id | int |
| order_date | date |
| amount | int |
Primary key: po_id.
One row per purchase order; amount is in rupees. supplier_id is always in Supplier.
Examples
Example 1
Supplier
| supplier_id | supplier_name | city |
|---|---|---|
| 1 | Sharma Traders | Delhi |
| 2 | Annapurna Foods | Pune |
| 3 | Deccan Distributors | Hyderabad |
| 4 | Gupta Agencies | Jaipur |
| 5 | Malabar Spices Co | Kochi |
PurchaseOrder
| po_id | supplier_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-03-12 | 48000 |
| 102 | 2 | 2023-12-30 | 21500 |
| 103 | 2 | 2025-01-04 | 33000 |
| 104 | 3 | 2024-12-31 | 9000 |
| 105 | 1 | 2024-07-19 | 56000 |
| 106 | 5 | 2024-01-01 | 12500 |
Output
| supplier_id | supplier_name |
|---|---|
| 2 | Annapurna Foods |
| 4 | Gupta Agencies |
How to solve Suppliers With No Purchase Order in 2024
This is an anti join with a condition on the other table. The LEFT JOIN brings in each supplier's 2024 orders only, because the year test sits in the ON clause; a supplier with no 2024 order then appears once with NULLs in every order column, and po.po_id IS NULL keeps exactly those rows.
The classic mistake is to put the date test in WHERE. Then the join brings in all of a supplier's orders, and the filter order_date BETWEEN … removes the NULL rows of suppliers with no orders while po_id IS NULL removes everything else — the answer comes back empty. Conditions on the optional side of an outer join belong in ON.
NOT EXISTS with the year test in the subquery reads more directly and is what most optimisers do with the join anyway. NOT IN is safe here only because supplier_id is never NULL in PurchaseOrder. The boundary dates 2024-01-01 and 2024-12-31 are both in the year, so BETWEEN is inclusive at both ends as needed.
Reference solution (MySQL)
SELECT s.supplier_id, s.supplier_name
FROM Supplier s
LEFT JOIN PurchaseOrder po
ON po.supplier_id = s.supplier_id
AND po.order_date BETWEEN '2024-01-01' AND '2024-12-31'
WHERE po.po_id IS NULLAnother way
SELECT supplier_id, supplier_name FROM Supplier s WHERE NOT EXISTS (SELECT 1 FROM PurchaseOrder po WHERE po.supplier_id = s.supplier_id AND YEAR(po.order_date) = 2024)Another way
SELECT supplier_id, supplier_name FROM Supplier WHERE supplier_id NOT IN (SELECT supplier_id FROM PurchaseOrder WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01')← Store SKUs At or Below Their Reorder Point · Stores Crossing ₹20,000 in Paid POS Bills →