SQL joins explained

What it means

A join combines values from two inputs into output rows. The match condition determines which left–right pairs qualify; each qualifying pair becomes a separate joined row. A left row can therefore appear in several output rows when several right rows match it. The example isolates one left row so you can examine this pairing rule separately from the rule for preserving unmatched rows.

Try it

Example — local and synthetic. Start with Join type = LEFT and Right-hand matches for this left row = 0. Change only Join type to INNER, then inspect the new count and explanation. Select Compare with baseline to place the current outcome beside the documented baseline. No SQL is executed.

Baseline: 1 output row(s). An INNER join omits the unmatched left row; LEFT preserves it with NULL right-side fields. The baseline's row exists because LEFT preserves the supplied left row, not because a right-side match was found.

Keep Right-hand matches for this left row = 0 throughout this comparison. Both outcomes concern the same one synthetic left row, with no post-join filtering evaluated.

Reset restores Join type = LEFT and Right-hand matches for this left row = 0, removes input errors, and clears the comparison. It preserves your independent open or closed choices for FAQ answers and About this model.

How it works

Conceptually, first consider which right rows satisfy the match condition for a particular left row. Each qualifying right row contributes one pair. With three matches, LEFT produces 3 output row(s), not one combined row: the left-side values appear in three pairs, each with a different matching right row. INNER produces the same pairs when matches exist.

Only when there are no matching pairs does the preservation rule make a difference. INNER contributes no row. LEFT contributes one row containing the left values and NULLs for the right-side fields. The zero match count describes how many pairs qualified; it does not mean the missing right-side values become numeric zeros.

Preservation applies at the join stage, not unconditionally to the final query result. For example, suppose a later WHERE condition requires right.status = 'active'. On a preserved unmatched row, the right-side status is NULL, so that comparison is not true and the filter removes the row. If the status restriction is instead part of ON, it narrows which right rows qualify as matches; a left row with none is still preserved by LEFT before later filtering. The model does not evaluate either filter placement, so its count stops before this step.

Worked example

This is a synthetic baseline, not an observed database result.

Result: 1 output row(s). There are no qualifying left–right pairs. LEFT therefore takes its unmatched-row branch: retain the one left row and supply NULLs for the right-side fields. That intermediate distinction matters—the output contains a preserved row, not a successful match.

What changes when the condition changes

Change only Join type from LEFT to INNER, keeping Right-hand matches for this left row = 0. The result becomes 0 output row(s), compared with the baseline's 1 output row(s). There are still no matching pairs; what changed is the preservation rule. INNER omits the unmatched left row instead of adding a row with NULL right-side fields.

In practice

For an actual query, start in a local or test database you are authorized to inspect. Keep the investigation read-only: review the join condition, relevant key constraints, and any later filters. Limit the first check to one chosen left row and the right rows that satisfy its actual match condition. A small synthetic dataset with zero, one, and several matches makes the intended relationship easier to verify.

The expected observation is one joined pair per qualifying right row, or one NULL-extended row under LEFT when none qualify, before later filtering. Compare that expectation with the query's result after accounting for its filters. If they differ, stop before changing data, constraints, or a deployed query. Establish which condition or relationship explains the difference first. If permissions, the intended relationship, or predicate behavior are unclear, the result remains undiagnosed; adding DISTINCT is not a substitute for that evidence.

Limits and unknowns

This is a local Example, not a SQL engine or a live database check. It accepts INNER or LEFT and a right-hand match count from 0 through 10 for one synthetic left row. It does not discover matches, evaluate join-key equality or NULL predicates, or calculate the effects of WHERE and other later processing. A valid count here therefore does not establish the final row count of a real query.

An invalid entered value has no applicable calculated outcome until you correct it or use Reset. If your real match count is unknown, entering zero does not resolve that uncertainty: zero specifically models no matches. You need evidence from the actual condition and permitted data to determine that count.

No database connection, credential, external request, or production change is involved. Without JavaScript, the initial example and explanation remain readable, but the controls do not operate.

What to carry forward

Choose INNER when unmatched left rows should be omitted, and LEFT when they should be preserved at the join stage. Neither choice guarantees one output row per left row. To predict the final result, establish how many right rows qualify for each left row, apply the preservation rule, and then account for later filters.