15. INNER JOIN and LEFT JOIN (The Two You Need First)

15.4 How to Spot Dropped and Repeated Rows After a Join

By Ravindra Bagale Last updated: 31 October 2026

Why. A join can give a total that looks fine but is wrong, because rows were lost or repeated. You need a quick way to catch both.

What. Rows are dropped when an INNER JOIN finds no match. Rows are repeated when the key is not unique on the other side: if hub 1 has 2 contacts, every hub-1 shipment appears twice, and a SUM counts its fee twice. Also, never add fees from different currencies together; group by currency instead.

How. Total the hub-1 fees with and without the contacts join, then build a safe per-hub total that shows the currency.

Steps

Run the first example and compare the true total with the joined total.
See that the joined total is exactly double, because hub 1 has 2 contacts.
Run the second example for a per-hub total, one currency per row.
Always check the row count and a known total after a join.

Examples

Example 1 · dataset: swiftship_logistics

SELECT
  (SELECT SUM(fee) FROM shipments WHERE hub_id = 1) AS true_total_inr,
  (SELECT SUM(s.fee)
   FROM shipments AS s
   INNER JOIN hub_contacts AS hc ON hc.hub_id = s.hub_id
   WHERE s.hub_id = 1) AS joined_total_inr;

Output

true_total_inr	joined_total_inr
1130	2260

Meaning. The true hub-1 total is 1130 INR, but after joining contacts it shows 2260 INR. Each shipment was repeated once per contact, so the fee was counted twice.

Example 2 · dataset: swiftship_logistics

SELECT h.hub_code, h.currency,
       COUNT(s.shipment_id) AS shipments,
       SUM(s.fee) AS total_fee
FROM hubs AS h
LEFT JOIN shipments AS s ON s.hub_id = h.hub_id
GROUP BY h.hub_id, h.hub_code, h.currency
ORDER BY h.hub_id;

Output

hub_code	currency	shipments	total_fee
PNQ-01	INR	3	1130
BOM-02	INR	2	1130
DXB-01	AED	2	105
LHR-03	GBP	1	18
SIN-01	SGD	0	NULL

Meaning. Each row shows one hub in its own currency: PNQ-01 3 shipments 1130 INR, BOM-02 2 shipments 1130 INR, DXB-01 2 shipments 105 AED, LHR-03 1 shipment 18 GBP, SIN-01 0 shipments. SIN-01 shows NULL, not 0, because SUM of no rows is NULL. Shipments 1009 and 1010 are not here, because they have no valid hub.

Ravindra Bagale's Tip

If you need a list from a table with repeats, such as contacts, total the main table first in a subquery and join the result afterwards.