Count matching row pairs before interpreting the report.
Review inner and left joins, ON versus WHERE, NULL-padded rows, card counts and correlated NOT EXISTS.
The section counts matching outcomes on six supplied SQLite 3.53.4 tasks. It is not a professional level, percentile, employer cutoff or qualification. Every explanation and all four full guides remain available regardless of count.
One team can match more than one card
The supplied setup names three teams and four cards. One team has two matching cards, another has one and the last has none. A separate card has a NULL team id. These explicit multiplicities drive every answer. An inner join counts matching pairs, while a left join can add a NULL-padded row for an unmatched team. Start by drawing the actual pairs before applying a filter or group calculation.
A right-side IS NULL test can keep the unmatched row
In a new unscored example, left ids are one and two. The right table has a primary-key row ninety-one that matches left id one. Left join them, then filter WHERE r.rid IS NULL. Only left id two remains. This is a right-side WHERE condition that deliberately keeps the unmatched row. It shows why a rule that every such condition makes a left join behave like an inner join is too broad.
Distinguish a team from a joined pair
A team with two cards legitimately produces two joined rows. That is not automatically an error to repair with DISTINCT, because the correct grain depends on the question. The bank asks explicitly for joined row counts in some items and one grouped count per team in another. A left join preserves unmatched left rows, not every unmatched right row. Keep the card with a NULL team id out of the matching pairs for the equality shown.
Compare the two complete predicates
The points comparison in WHERE excludes the NULL-padded row because that comparison is not true there. Putting the same comparison into ON changes which card pairs match before unmatched teams are added. The resulting count changes. Neither placement is always the right answer for an unspecified report; each represents a different request. Explain which teams should survive and which cards should qualify before deciding where a condition belongs.
Count a real card id or test for a matching row
COUNT(c.cid) ignores the NULL card id on a padded row, so an unmatched team can have a grouped card count of zero even though one joined row exists. NOT EXISTS instead asks whether the correlated matching query returns any row. It does not count how many cards a matching team has. Keep those two questions separate, and do not replace a non-NULL card identifier with an unrelated nullable field when checking the grouped count.
Add one match before changing the query
Use invented ids in a disposable in-memory example. Predict an inner join and a left join, then add one extra right-side match for a single left id. Count the new pairs before aggregating them. Next compare a null-rejecting WHERE predicate with an ON condition and with IS NULL. Write the intended grain beside each result. The website displays the provided SQL as text; it needs no access to production tables or customer records.
A count does not establish reporting competence
Six matching outcomes can point to a useful review topic, but they do not establish that a large join is correct, that a report reconciles or that a production query is efficient. Read the complete explanations and use fresh data after learning these keys. The bank is fixed and verified in the stated SQLite environment. There are no calibrated developer tiers, hiring pass marks or qualifications, and every full guide remains readable at any count.