The Life Starter
Sign up
Grade 11 · lesson 3 of 4

Keep Unmatched Rows in a Left Join

Match every left-table row by key and explicitly preserve missing matches.

Download the grade pack

Supplied materials

  • Paper and pencil; an adult may read aloud. No device, account or purchase is needed.

Learn the idea

A left join keeps every row from its left table. A matching right-table row contributes its fields; when no right row matches, those fields are NULL, a missing-value marker. It is not an empty name or an invented replacement. Here the right-table person keys are unique, so each left row yields exactly one output row.

Fictional people: P1 = Dax; P2 = Emi. Loans in order: L1 → P2, atlas; L2 → P2, globe; L3 → P9, ruler. Left join loans to people on person_id. The repeated P2 in loans represents two distinct loans, which is valid. P9 has no person match, so its loan must remain with a NULL person name.

  1. Write both input tables and underline each join-key value.
  2. For each loan, locate its unique people match.
  3. Produce one output row per loan, retaining L3 with NULL name.
  4. Compare with an inner join, which would retain only the two matching loans.
Worked example

See the method in action

Left-join rows: (L1,P2,atlas,Emi), (L2,P2,globe,Emi), (L3,P9,ruler,NULL). Left-join count = 3; inner-join count = 2. P2 appears twice because two different loan rows reference it.

Your turn

Check your reasoning

Your answer is temporary and is not saved to an account. Download your current answer before leaving or reloading. Written work on paper or in another app stays in your own file.

If L4 → P1, cube is added, how many rows does this left join return?

Feedback appears here after you check. This is practice, not a graded school assessment.

See the answer and explanation

4

Each of the four loan rows yields one output because people keys are unique. The unmatched loan is still retained.

Take it further

Add fictional person P9 = Fox without changing loans. Expected row count stays 3, but L3’s person name changes from NULL to Fox. The inner join now also has 3 rows.

Nothing here is saved to an account or sent to a teacher. The answer download includes your current choice and any checked feedback.

Check the subject sources

These LifeStarter passages, scenarios and exercises are authored for this Academy with AI assistance and review. External resources keep their publishers’ terms. A grade label suggests a starting level; it does not establish alignment with your school’s complete requirements.