Suppliers With No Purchase Order in 2024

Easy
374.

Suppliers With No Purchase Order in 2024

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

ColumnType
supplier_idint
supplier_namevarchar
cityvarchar

Primary key: supplier_id.

One row per registered supplier.

Table: PurchaseOrder

ColumnType
po_idint
supplier_idint
order_datedate
amountint

Primary key: po_id.

One row per purchase order; amount is in rupees. supplier_id is always in Supplier.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Supplier

supplier_idsupplier_namecity
1Sharma TradersDelhi
2Annapurna FoodsPune
3Deccan DistributorsHyderabad
4Gupta AgenciesJaipur
5Malabar Spices CoKochi

PurchaseOrder

po_idsupplier_idorder_dateamount
10112024-03-1248000
10222023-12-3021500
10322025-01-0433000
10432024-12-319000
10512024-07-1956000
10652024-01-0112500

Output

supplier_idsupplier_name
2Annapurna Foods
4Gupta Agencies
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
supplier_idint
supplier_namevarchar
cityvarchar

Primary key: supplier_id.

One row per registered supplier.

Table: PurchaseOrder

ColumnType
po_idint
supplier_idint
order_datedate
amountint

Primary key: po_id.

One row per purchase order; amount is in rupees. supplier_id is always in Supplier.

Examples

Example 1

Supplier

supplier_idsupplier_namecity
1Sharma TradersDelhi
2Annapurna FoodsPune
3Deccan DistributorsHyderabad
4Gupta AgenciesJaipur
5Malabar Spices CoKochi

PurchaseOrder

po_idsupplier_idorder_dateamount
10112024-03-1248000
10222023-12-3021500
10322025-01-0433000
10432024-12-319000
10512024-07-1956000
10652024-01-0112500

Output

supplier_idsupplier_name
2Annapurna Foods
4Gupta 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 NULL

Another 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 →