Join output follows matching row combinations. A one-to-many relationship multiplies parent rows, which affects aggregation and pagination.
Before you start
You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.
The practical goal is to reason through this situation: Joining orders to items yields one row per matching item, not one per order. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.
Step-by-step walkthrough
Step 1: Set the output grain
Decide whether a row represents an order or an item.
Step 2: Trace matching combinations
An order with three matching items creates three joined rows.
Step 3: Aggregate intentionally
Count distinct order IDs or aggregate items before joining as required.
Worked scenario
Joining orders to items yields one row per matching item, not one per order.
Orders A and B have three and one items respectively. Their inner join returns four rows, so count(*) is four rather than two. Adding a second child relationship can multiply each item’s contribution again; DISTINCT on the final result does not repair an already inflated sum.
Common mistake
Adding DISTINCT can hide a mistaken relationship without fixing totals.
Verify the behavior
Use the two-order dataset and assert both order count and item count separately.
Interview exercise
Count orders after the join.
Answer and reasoning
Use an appropriate distinct identifier count or aggregate items before joining, according to the desired result grain.
Continue learning
Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.