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

15.2 What LEFT JOIN Keeps (Every Left Row, Matched or Not)

By Ravindra Bagale Last updated: 31 October 2026

Why. Reports often must show every shipment or every customer, even the ones with a problem. LEFT JOIN keeps them, so problems stay visible.

What. LEFT JOIN keeps every row from the left table, the one written after FROM. When there is a match, it adds the right table's columns. When there is no match, it fills the right table's columns with NULL. So a NULL after a LEFT JOIN can mean no match, or it can be a real NULL stored in the right table. Check a key column from the right table, such as h.hub_id, to tell them apart.

How. Run the same shipment–hub join as a LEFT JOIN, then count shipments for every customer, including the one with none.

Steps

Run the first example and see all 10 shipments.
Find 1009 and 1010: their hub columns are NULL because there was no match.
Run the second example and see Marina Spices with 0 shipments.
To count matching shipments, count the right table's primary key, s.shipment_id, which is never NULL in a real match. COUNT(*) would give 1 for Marina, because it counts result rows, including the unmatched placeholder row.

Examples

Example 1 · dataset: swiftship_logistics

SELECT s.shipment_id, s.hub_id, h.hub_id AS matched_hub, h.hub_code
FROM shipments AS s
LEFT JOIN hubs AS h ON h.hub_id = s.hub_id
ORDER BY s.shipment_id;

Output

shipment_id	hub_id	matched_hub	hub_code
1001	1	1	PNQ-01
1002	1	1	PNQ-01
1003	2	2	BOM-02
1004	3	3	DXB-01
1005	3	3	DXB-01
1006	4	4	LHR-03
1007	1	1	PNQ-01
1008	2	2	BOM-02
1009	9	NULL	NULL
1010	NULL	NULL	NULL

Meaning. All 10 shipments stay. For 1009 and 1010, matched_hub and hub_code are NULL because no hub matched. These NULLs were made by the join, not stored in hubs.

Example 2 · dataset: swiftship_logistics

SELECT c.customer_name,
       COUNT(s.shipment_id) AS shipments
FROM customers AS c
LEFT JOIN shipments AS s ON s.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Output

customer_name	shipments
Kavya Traders	2
Desai Pharma	2
Al Noor Foods	2
Thames Books	2
Lotus Crafts	2
Marina Spices	0

Meaning. Every customer appears. Marina Spices shows 0, so the report makes clear that this customer has not shipped anything yet.

Ravindra Bagale's Tip

Put the table you must not lose after FROM, and the optional table after LEFT JOIN. The order decides which rows are kept.