Mode disclosure
All modes use one coherent workspace; only disclosure and guidance change. Learn mode keeps theory, concept names, full schema help, progressive hints, and solution review available.
LEFT JOIN, Missing Relationships, and Zero Counts / predict result
M18-A01 - Prediction - compare inner and left join outputs on unmatched rows
M18-A01 - Prediction - compare inner and left join outputs on unmatched rows. Preserve unmatched left rows and calculate zero-related counts correctly.
- Result grain
- one comparison row per join type for customer to order matching
- Exact columns
- join_type; joined_rows; unmatched_customer_rows
SQL editor shortcuts: Ctrl or Command Enter runs the query, Ctrl or Command Shift Enter checks it, Alt H opens the next hint, Ctrl or Command slash toggles a line comment, Ctrl or Command Shift F formats the SQL, and Escape closes transient UI.
Cursor at line 1, column 1.
Scenario
Use LEFT JOIN to preserve unmatched parent rows: place right-side filters intentionally, count right-side keys for zeroes, and test right-side keys for anti-joins.
LEFT JOIN, Missing Relationships, and Zero Counts / predict result
One-sentence task
M18-A01 - Prediction - compare inner and left join outputs on unmatched rows. Preserve unmatched left rows and calculate zero-related counts correctly.
Learn mode disclosure
Theory, concept names, full schema help, and progressive hints are available.
Structured output contract
- Result grain
- one comparison row per join type for customer to order matching
- Exact columns
- join_type; joined_rows; unmatched_customer_rows
- Source population
- Use the prompt setup plus FROM, JOIN, WHERE, and subquery predicates as the source population. Visible rows are only examples.
- Grouping
- Group only at the requested output grain: one comparison row per join type for customer to order matching.
- Ordering
- order by join_type
- Validation
- select-only; hidden deterministic variants.
Relevant tables
Time and difficulty
- Estimated time
- 5 minutes
- Difficulty
- 3/5
Objective and concepts
State the requested SQL output contract for left join, missing relationships, and zero counts using source grain, columns, ordering, and edge-case evidence.
Glossary links
Concept material
SQL Trail treats every query as an evidence trail: identify source grain, transform rows deliberately, then compare output to a shared contract.
A passing query must handle hidden nulls, ties, boundaries, and no-match rows when the contract makes them relevant.
Syntax card
SELECT <requested_columns>
FROM <source_table>
WHERE <source_population_filter>
GROUP BY <result_grain_columns>
ORDER BY <deterministic_tie_breakers>;- <requested_columns> means the exact output columns, aliases, and order from the visible contract.
- <source_population_filter> means the row population definition, not a copied visible-row value.
- <deterministic_tie_breakers> means all ordering and tie rules needed for repeatable output.
Why this works
A left join keeps every customer row, so customers with no orders still contribute one unmatched row with NULL right-side columns.
Edge cases
Hidden variants preserve nulls, ties, duplicates, boundaries, no-match rows, and alternate row order when those risks apply.
PostgreSQL note
The local engine uses PostgreSQL-compatible syntax, including explicit NULL predicates, deterministic ORDER BY clauses, and transactional grading.
Worked example
SELECT join_type, joined_rows, unmatched_customer_rows FROM (VALUES ('inner_join', (SELECT COUNT(*)::int FROM customers c JOIN orders o ON o.customer_id = c.customer_id), 0), ('left_join', (SELECT COUNT(*)::int FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id), (SELECT COUNT(*)::int FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL))) AS join_counts(join_type, joined_rows, unmatched_customer_rows) ORDER BY join_type;Assumptions, dialect notes, and common traps
- Duplicate policy
- Preserve duplicate facts unless the prompt explicitly asks for distinct tuples or set semantics.
- Null policy
- Preserve NULL, empty string, zero, and false as distinct values unless the contract says to display a fallback.
- Tie-breakers
- Use every ordering rule in the contract and end tied business metrics with deterministic secondary keys when needed.
- Zero-related entities
- Preserve requested zero-related, no-match, or complete-period entities and make display fallback explicit.
- Numeric tolerance
- Use exact semantic comparison unless the activity explicitly declares a numeric tolerance.
PostgreSQL-compatible local checks
Queries run in a local PGlite worker with PostgreSQL-style syntax and transactional grading.
- LEFT preserves the written left side: The physically smaller table is assumed to be preserved regardless of query order. Repair: Put the parent population that must survive before LEFT JOIN.
- Right filters in WHERE erase no-matches: Rows with no qualifying child disappear even though the prompt asks for zeroes. Repair: Move right-side qualification into ON or into aggregate FILTER depending on the requested metric.
- COUNT star is not a zero-child count: A parent with no child reports 1 because the NULL-extended preserved row is counted. Repair: Count the right-side key when the metric is number of matched children.
- Nullable attributes do not prove absence: A matched child with a NULL delivered_at or other attribute is treated as if no row matched. Repair: Use the right-side key NULL test for anti-joins and absence checks.
Opened hints
No hints opened yet.