SQL LEFT JOIN: Keep Unmatched Rows When Filtering

The practice task

List every study group and its approved room reservation, including groups that have no approved reservation. One group has a pending reservation; it should still appear with no approved room.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE study_groups(id INTEGER, name TEXT);
CREATE TABLE reservations(group_id INTEGER, room TEXT, status TEXT);
INSERT INTO study_groups VALUES (1,'Algebra'),(2,'Biology'),(3,'Chemistry');
INSERT INTO reservations VALUES (1,'R1','approved'),(2,'R2','pending');

SELECT g.name, r.room
FROM study_groups AS g
LEFT JOIN reservations AS r
  ON r.group_id = g.id AND r.status = 'approved'
ORDER BY g.name;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The join condition limits which reservations qualify as matches. Algebra has an approved room. Biology has a reservation, but it does not qualify; Chemistry has none. Both groups remain because study_groups is the preserved input.

Moving r.status = 'approved' to WHERE would remove the NULL-extended rows and therefore remove Biology and Chemistry. That would answer a different question: which groups currently have approved reservations?

The query also assumes that multiple approved reservations should produce multiple rows. If the intended report requires one reservation per group, define a choice rule such as a particular date or reservation ID. Do not choose an arbitrary room with an aggregate just to force one row.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Keep match qualification in ON when unmatched left rows must remain.
• Use an explicitly non-null right-side key for unmatched-row checks.
• A left join can still multiply rows when several matches qualify.