Ravindra BagaleCourses & study guides Track your progress

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

15.1 What INNER JOIN Keeps (Matching Rows Only)

By Last updated:

Why. You often need details from two tables in one result, such as each shipment with its hub code. INNER JOIN is the simplest way, but it can quietly drop rows.

What. INNER JOIN returns only the pairs of rows where the ON condition is true. A shipment whose hub does not exist, or whose hub is NULL, has no match, so it is left out. A hub with no shipments is left out too. So INNER JOIN does not guarantee that every input row is kept: unmatched rows are left out.

How. Join shipments to hubs on hub_id, then count the rows and compare with the 10 shipments you started with.

Steps

  1. Run the first example and read the shipments with their hub codes.
  2. Look for 1009 and 1010: they are not in the result.
  3. Run the second example and compare the joined count with the total.
  4. Use INNER JOIN only when you really want matching rows only.

Examples

Example 1 · dataset: swiftship_logistics

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

Output

shipment_idhub_idhub_code
10011PNQ-01
10021PNQ-01
10032BOM-02
10043DXB-01
10053DXB-01
10064LHR-03
10071PNQ-01
10082BOM-02

Meaning. Only 8 shipments appear. 1009 (hub 9 does not exist) and 1010 (no hub) were dropped, and Singapore does not appear because it has no shipments.

Example 2 · dataset: swiftship_logistics

SELECT (SELECT COUNT(*) FROM shipments) AS all_shipments,
       (SELECT COUNT(*)
        FROM shipments AS s
        INNER JOIN hubs AS h ON h.hub_id = s.hub_id) AS after_inner_join;

Output

all_shipmentsafter_inner_join
108

Meaning. 10 shipments went in and 8 rows came out. This simple count works here because hub_id is unique in hubs. If a key repeats on the right side, extra rows can hide dropped ones, so also check that the right key is unique and list unmatched rows with LEFT JOIN and IS NULL.

Ravindra Bagale's Tip

After every join, compare the row count with the table you started from, check that the right key is unique, and list unmatched rows with LEFT JOIN and IS NULL.