Stage 5 · L21

Explain inner, left, right and full joins with actual rows

Core · original Session 5

A join combines related stored rows according to a condition. Rental.equipment_id equals Equipment.id is the relationship used here. An inner join excludes a rental without a matching item; a left outer join retains the left row with nulls on the right. The implemented non-null foreign key prevents orphaned rental item references, but outer joins can still be useful for optional relations and reporting.

A foreign key constrains stored references; an ORM relationship supplies navigation; a join specifies a query shape. They are three different responsibilities. Do not assume that declaring a relationship means all rows are automatically loaded or that a join selects the fields required by a public response.

The nested rental representation includes equipment details. list_rentals deliberately uses joinedload(Rental.equipment), which joins and populates the many-to-one relationship in one statement. It then constructs RentalDetail while the session is owned. This is different from writing a join only to filter rows while leaving a relationship unloaded.

When joining a one-to-many collection, one parent can appear in several SQL rows. Limiting raw joined rows can truncate a parent's children or change parent-page membership. This checkpoint loads only a many-to-one item from each rental, so each rental contributes one item. For collection responses choose a parent-page query and a deliberate second select-in load, rather than treating all join shapes as interchangeable.

RIGHT JOIN retains every right-side row and supplies nulls for missing left matches; reversing the tables in a LEFT JOIN expresses the same retention. FULL OUTER JOIN retains unmatched rows from both sides. join_examples.py uses two VALUES relations requested{1,2} and stocked{2,3}: RIGHT gives(2,2)/(null,3); FULL also includes(1,null). These are actual PostgreSQL query exercises, not new inventory tables or orphaned rental rows. The application foreign keys still prevent missing referenced equipment.

The focused SQL uses WITH to name two temporary query relations, each populated by VALUES. USING(id) compares their id columns. NULL marks the absent side of an unmatched pair; it is not a fabricated matching value. COALESCE(r.id,s.id) chooses the non-null key for readable ordering. These small row sets let you calculate every join result before running PostgreSQL; no state update, lock or pagination is part of this lesson.

WITH requested(id) AS (VALUES (1),(2)), stocked(id) AS (VALUES (2),(3))
SELECT r.id AS requested_id, s.id AS stocked_id
FROM requested r FULL OUTER JOIN stocked s USING(id)
ORDER BY COALESCE(r.id,s.id);

Inner: (2,2). Left: (1,NULL),(2,2). Right: (2,2),(NULL,3). Full: all three pairs. The preserved side determines unmatched rows. This is an actual PostgreSQL VALUES join exercise; it changes no rental state and needs no later capacity policy.