Join tables and preserve missing matches
See this course’s project requirements
Answer whether one assigned workshop session can accommodate six additional bookings using your accumulated query collection.
Keep your working files in your own notebook or software. Save a private description, checklist, and work link in My Proof.
What to include
- Include the complete original two-table fixture and dictionary.
- Show filtering, ordering and grouped summaries with expected results.
- Demonstrate both session-preserving and venue-preserving joins.
- Detect an invalid test record and record missing-data limits.
- Deliver the decision query with reproducible output and interpretation.
Review and revise
Use these criteria to check your work and choose what to improve.
- Question fit: row and group meanings match the decision.
- Query correctness: results follow from explicit SQL or accurate traces.
- Quality: totals reconcile and missing matches are explained.
- Reproducibility: fixture, queries and limits travel with the answer.
Want more background?
The idea in everyday life
A site code is useful for storage but a venue name is easier to read in a planning brief. Joining combines related tables without manually copying names into every session. It can also multiply rows or erase unmatched records. You will inspect both effects using the unused West venue and the session whose venue is still unassigned.
A join matches rows using shared values called keys. INNER JOIN keeps matches only; LEFT JOIN also keeps every row from the starting table when no match is found.
One step at a time.
Use the sample or information you have permission to use. Keep your work as you go. You can pause between steps.
- 1
Identify sessions.site and sites.code as the matching columns. Confirm that sites.code is unique; otherwise one session could match several site rows and appear more than once.
- 2
Start from sessions and use LEFT JOIN sites when every session must remain visible. Write the ON condition explicitly and select the session ID alongside its matched site name.
- 3
Trace session 4. Its missing site does not match a venue code, but the left join preserves the session and supplies NULL for the venue name.
- 4
Reverse the question: which sites have how many sessions, including unused sites? Start from sites, left join sessions, group by site, and count sessions.id rather than COUNT(*).
- 5
Save both query patterns and explain what an INNER JOIN would omit. Check row counts before and after the simple name join; this fixture should still have four session rows.
See how it works.
Fictional query preserving every venue:
SELECT sites.name, COUNT(sessions.id) AS sessions_count
FROM sites
LEFT JOIN sessions ON sites.code = sessions.site
GROUP BY sites.code, sites.name
ORDER BY sites.name;North returns 2, South returns 1 and West returns 0. West survives because sites is the left table. COUNT(sessions.id) ignores the NULL value supplied for its unmatched session; COUNT(*) would count that preserved row and misleadingly report one. To preserve all sessions instead, use SELECT sessions.id, sites.name FROM sessions LEFT JOIN sites ON sessions.site=sites.code ORDER BY sessions.id;, which retains session 4 with a NULL name.
Did you cover these?
- Session-preserving join returns four rows.
- Venue-preserving summary includes West=0.
- The key uniqueness check is recorded.
What to finish
Two join queries with row-count and missing-match explanations.
Watch for this
Using COUNT(*) after a left join when counting matching child records.
How can I tell I understand it?
Explain why West can have zero matches while its output row still exists.
Quick knowledge check
Answer all 3 questions. Get at least 2 right and the lesson completes automatically.
You’ll get an explanation for each answer. Signed in? Your results save to your account.
Pass the check. We’ll save the progress.
Answer all 3 questions above and get at least 2 right. You can review the explanations and try again.
Go to the knowledge checkTools, templates, and lesson sources
Tools and templates
Tools open in a new tab so you can keep your place here.
Sources and further reading
Explore the references behind this lesson.
Source & access notes · checked 2026-09-10
Opened university OpenCourseWare overview; verified querying, relationships, design and optimization topics. Used as optional further study, with original fixtures here.
Access: Free OpenCourseWare reading and videos. External grading or progress services may require accounts; optional verified certificates have separate enrollment and may be paid. This course awards no Harvard credit or affiliation.
Reuse: Linked for reference. Examples, activities and assessments here are original; free access alone does not grant permission to reuse source text or branding.
Source & access notes · checked 2026-09-10
Opened official SELECT documentation covering WHERE, GROUP BY, joins, ORDER BY and NULL behavior. All supplied queries target SQLite syntax.
Access: Free public reading; no account or certificate is required for this Life Starter course.
Reuse: Linked for reference. Examples, activities and assessments here are original; free access alone does not grant permission to reuse source text or branding.
Report a problem with this lesson
Tell us what went wrong. Please leave out personal information and private project contents.