Ravindra BagaleCourses & study guides Track your progress

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

15.5 Practice: Shipments with Customer Names and Hubs

By Last updated:

Why. This is the kind of list a logistics team asks for every day: each shipment with the customer name and the hub, with problems clearly marked.

What. You join three tables in one query. Shipments is the left table, so every shipment stays. Customers and hubs are joined with LEFT JOIN, and a CASE expression explains why a hub is blank.

How. Start FROM shipments, LEFT JOIN customers, then LEFT JOIN hubs, and add a hub_status column.

Steps

  1. Write the expected count first: 10 rows, one per shipment.
  2. Run the example.
  3. Check that there are 10 rows and every customer name is filled.
  4. Read hub_status: OK for 8 shipments, Hub not found for 1009, Hub missing for 1010.

Examples

Example 1 · dataset: swiftship_logistics

SELECT s.shipment_id,
       c.customer_name,
       h.hub_code,
       CASE
         WHEN s.hub_id IS NULL THEN 'Hub missing'
         WHEN h.hub_id IS NULL THEN 'Hub not found'
         ELSE 'OK'
       END AS hub_status
FROM shipments AS s
LEFT JOIN customers AS c ON c.customer_id = s.customer_id
LEFT JOIN hubs AS h ON h.hub_id = s.hub_id
ORDER BY s.shipment_id;

Output

shipment_idcustomer_namehub_codehub_status
1001Kavya TradersPNQ-01OK
1002Kavya TradersPNQ-01OK
1003Desai PharmaBOM-02OK
1004Al Noor FoodsDXB-01OK
1005Al Noor FoodsDXB-01OK
1006Thames BooksLHR-03OK
1007Lotus CraftsPNQ-01OK
1008Desai PharmaBOM-02OK
1009Thames BooksNULLHub not found
1010Lotus CraftsNULLHub missing

Meaning. All 10 shipments are listed with customer names. 1009 says Hub not found, because hub 9 does not exist. 1010 says Hub missing, because its hub is unknown right now. The team can fix those two at the source.

Ravindra Bagale's Tip

Check s.hub_id IS NULL before h.hub_id IS NULL in the CASE. Both are NULL-related, but only the first one means the shipment's own hub value is unknown.