LEFT JOIN, missing matches and filter placement

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

LEFT JOIN, missing matches and filter placement

Concept and contract

Keep companies without matching roles while filtering the right-side population correctly.

A LEFT JOIN retains every row from its left input even when no right-side row matches. The worked report lists every company with its active role identities. Company D has no roles, so it appears once with a null right-side identity.

The output grain is a matching company-role pair plus one placeholder for an unmatched company. It is not yet one row per company. Predict seven rows: two for A, three for B, one for C and the unmatched placeholder for D.

Mechanism and worked example

The active-role condition belongs in ON when the requirement is to retain all companies while restricting which roles match.

The active-role condition belongs in ON when the requirement is to retain all companies while restricting which roles match. The engine first decides matching pairs under the complete ON expression, then supplies null right-side columns for companies with no match.

A WHERE predicate is applied to the joined output. Testing r.active = 1 there removes D because its right-side active field is null, not one. This often turns an intended outer report into an effectively inner result for that predicate.

Complete worked example

sql

SELECT c.id, r.id AS role_id FROM companies c
LEFT JOIN roles r ON r.company_id = c.id AND r.active = 1
ORDER BY c.id, r.id;

Reasoning: D remains with a null right-side identity.

Counterexamples and common mistakes

The counterexample moves active status into WHERE.

The counterexample moves active status into WHERE. Companies with no matching role disappear, including D. An apparently reasonable filter therefore changes the population of companies rather than merely selecting active role evidence under each retained company.

Not every right-side condition must universally go in ON. Placement depends on the question. If the business request truly wants companies with active matches only, a WHERE filter may be intentional. Explain the desired unmatched-row behavior before prescribing a syntax rule.

Counterexample: a plausible but wrong query

sql

SELECT c.id, r.id AS role_id FROM companies c
LEFT JOIN roles r ON r.company_id = c.id
WHERE r.active = 1 ORDER BY c.id, r.id;

Reasoning: The WHERE predicate removes the unmatched placeholder.

Independent practice: small query

List all companies and only their Ukrainian role identities, including archived Ukrainian roles for this task.

List all companies and only their Ukrainian role identities, including archived Ukrainian roles for this task. Predict A with identities 1, 2 and 7, followed by B, C and D each with a null role identity. Keep the country condition inside ON.

The practice changes the right-side matching population while preserving every left-side company. Do not carry over the active predicate silently: the prompt explicitly includes archived records. Reuse stable company and role ordering to compare the expected pairs.

Step 1: Preserve companies as the left input. Step 2: Add the country rule to the match condition.

Practice starter: solve before reading the solution

sql

SELECT c.id, r.id AS role_id FROM companies c
LEFT JOIN roles r ON r.company_id = c.id AND /* Ukrainian roles */
ORDER BY c.id, r.id;

Reasoning: Restrict right-side matches without removing any company.

Hint 1: Do not place r.country in a rejecting WHERE for this task.
Hint 2: Archived Ukrainian role 7 is intentionally included.

Solution, tests and trade-offs

The solution produces six rows: three actual A-role matches and three unmatched placeholders.

The solution produces six rows: three actual A-role matches and three unmatched placeholders. NULL means no right-side role satisfied this join condition; it does not mean that B or C has no roles anywhere. This distinction matters in country-filtered market reports.

When counting later, COUNT(r.id) ignores those placeholders, while COUNT(*) counts them. Keep the right-side identity nullable in the result contract even if its source column is a primary key. Outer joining changes which output expressions may be missing.

Practice solution and acceptance checks

sql

SELECT c.id, r.id AS role_id FROM companies c
LEFT JOIN roles r ON r.company_id = c.id AND r.country = 'UA'
ORDER BY c.id, r.id;

Reasoning: A has three matches; B, C and D each have one null placeholder.

Interview reasoning and next step

In an interview, trace an unmatched company through ON, null extension and WHERE.

In an interview, trace an unmatched company through ON, null extension and WHERE. This concrete sequence explains the bug more clearly than saying left joins always preserve everything. A later WHERE clause can still remove the very rows you meant to retain.

The next lesson adds a second one-to-many relationship, where cardinality errors can inflate counts even without unmatched rows. Keep the entity grain explicit at every step, then choose counting or preaggregation strategies based on that grain.

Optional stretch — after the core task works: Count right-side identities per company and explain why COUNT(*) gives D one rather than zero.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Keep companies without matching roles while filtering the right-side population correctly.