PLAN-101 Career OS
Beginner 40 Minutes

SQL Joins, Missing Rows and Query Correctness

Learn to combine student and submission tables without losing students who have not submitted work.

Key Learning Objectives

  • Distinguish INNER JOIN from LEFT JOIN.
  • Reason about unmatched rows and NULL.
  • Avoid accidental row multiplication.
  • Write a query for students without submissions.

Start with a business question and a join key

Suppose students are stored in one table and assignments in another. An inner join answers which students have matching submissions; it intentionally excludes students with no submission. A left join starts with every student and attaches a submission when one exists. The join condition belongs in ON and usually matches a stable primary/foreign-key pair, not display names. If two students share a name, joining by name creates incorrect results.

SELECT s.id, s.name, a.assignment_id
FROM students AS s
LEFT JOIN submissions AS a ON a.student_id = s.id
ORDER BY s.id;

All students appear. For those who have not submitted, the submission columns are NULL.

Find missing work without accidentally changing the join

To find students who have not submitted, filter for the missing match after a left join. Beware of putting a condition on the right-hand table in WHERE: a condition such as WHERE a.status = 'graded' removes NULL rows and can effectively turn the outer join into an inner join. If the condition describes which rows may match, place it inside ON; if it describes the final result, use WHERE with the intended NULL behavior.

SELECT s.id, s.name
FROM students AS s
LEFT JOIN submissions AS a ON a.student_id = s.id
WHERE a.student_id IS NULL;

The unmatched student rows survive the outer join and are selected by the null test.

Understand one-to-many row multiplication

A student with three submissions produces three joined rows. This is expected, not a database bug. If your dashboard needs one row per student, decide whether to group and aggregate, select one latest record with a window function, or pre-aggregate in a CTE. Using DISTINCT to hide unexpected duplicates can conceal a wrong join. First sketch a tiny dataset on paper and predict the result count. Indexing the foreign key often improves join performance for larger datasets; check EXPLAIN instead of assuming.

Build It: Submission gap report

Create a three-table student, assignment and submission schema and query missing submissions for each assignment.

Project Deliverables

  • CREATE TABLE statements with primary and foreign keys.
  • Test data including a student with no submission and a student with multiple submissions.
  • Queries for all students, non-submitters and total submissions per assignment.

Acceptance Criteria

  • The missing-submission report retains unmatched students.
  • The count query handles zero submissions.
  • Every join uses a documented key.

Next Steps & Related Learning

This interactive lesson is part of the SQL curriculum. Continue exploring or try another interactive lesson:

View Full SQL → Arrays, Contiguous Memory & Hash Tables → JavaScript Asynchronous Execution & V8 Event Loop → Relational Database Architecture, B-Trees & Query Optimization → Semantic HTML, Accessible Forms & Document Structure → Python Functions, Dictionaries and Reliable Data Processing → React State, Events and Derived UI → Designing a Task API: HTTP Methods, Errors and Validation → Sliding Window: From Quadratic Loops to a Linear Scan → Responsive CSS Layout: Flexbox, Grid and Fluid Constraints → TypeScript Union Types and Runtime Narrowing → Docker Images, Containers and Reproducible Builds → Git Branches, Pull Requests and Conflict Resolution → Graph Traversal: BFS, DFS and Visited Sets → AWS IAM: Least Privilege and Secure Cloud Access → API Pagination, Error Contracts and Safe Retries →