When one statement fetches parents together with their child collection, why does the same parent come back repeatedly?
answer
- rows, not a tree
- the join multiplies, the mapper does not
- one row per child row
- parent columns repeat down the grid
- collapse on parent identity after assembly
basics
~20 sThe join returns one row per matching child, with the parent's columns repeated on every one of them. A layer that produces one result entry per row therefore hands back the same parent once per child row.
solid answer
~40 sA relational statement returns a flat grid, not a tree. Joining a parent to its child collection produces one row per child row, and the parent's own columns are repeated verbatim down that grid. When the layer turns rows into objects it walks the grid row by row, so unless it de-duplicates, the returned list holds one entry per row: a parent with three children appears three times. In a layer that keeps an identity map those repeats are the same object, so it looks like one parent listed three times rather than three parents. Adding `DISTINCT` to the SQL does not help, because the rows genuinely differ in their child columns. The repair belongs after the rows are assembled, keyed on the parent's identity.
code
sql · 11 lines-- parent 1 has 3 children, parent 2 has 1 child
SELECT p.id, p.name, c.id AS child_id, c.label
FROM parent p
JOIN child c ON c.parent_id = p.id
WHERE p.id IN (1, 2);
-- 4 rows come back, 2 distinct parents:
-- 1, 'A', 10, 'x'
-- 1, 'A', 11, 'y'
-- 1, 'A', 12, 'z'
-- 2, 'B', 20, 'q'go deeper
Remember the shape: a join gives one flat row per child row, with the parent columns copied onto each. That is why the same parent appears several times in the list.
Explain the mapping loop: rows in, one entry emitted per row, the parent resolved by key each time. Then explain why a SQL DISTINCT cannot collapse rows that differ in their child columns.
Show that you check the symptom by comparing the result size against the distinct parent keys, and that you weigh the transport cost of repeating wide parent columns against the convenience of one round trip.
Frame it as an interface question: a read path that returns fanned-out rows leaks a storage detail to its callers. Decide whether the layer de-duplicates by default, and what that default costs in memory across the whole system.
## What the database actually returns A relational statement returns a **flat grid of rows**, never a nested structure. When you ask for parents *and* their children in one statement, the only shape SQL can give you is one row per matching child row, with every parent column repeated on each of those rows. So for a `parent` joined to a `child` table: - a parent with **3** children contributes **3** rows, each carrying the same parent id, name and every other parent column; - a parent with **1** child contributes 1 row; - a parent with **no** children contributes **0** rows under an inner join, and **1** row with null child columns under a left outer join; - the total row count is therefore the **sum of the child counts**, not the number of parents. This is arithmetic, not a defect of any particular data-access layer. The layer asked for a join; the join fans out. ## From rows to objects The layer now walks that grid. For each row it takes the parent key, finds or creates the parent, then attaches the child from the same row to the parent's collection. Two things fall out of this: 1. **The collection ends up correct.** Each child row is seen once, so the parent's collection holds exactly its children. 2. **The result list ends up wrong.** If the layer emits one entry per row, the list has as many entries as the grid has rows, so the same parent is listed once per child. In a layer that maintains an **identity map** — a per-unit-of-work index from key to loaded object — the repeated entries are the *same* object, so writing through one is visible through all of them. A thinner layer that just maps rows to plain records with no identity map can hand you genuinely separate copies of the same parent, each carrying only the child from its own row. Both are the same underlying fan-out; the observable damage differs. | Parents matched | Children each | Rows returned | Entries in the result list | |---|---|---|---| | 1 | 3 | 3 | 3 (the same parent) | | 3 | 4 | 12 | 12 (each parent 4 times) | | 2 | 0 (inner join) | 0 | 0 | | 2 | 0 (left outer join) | 2 | 2 | ## Why a DISTINCT in the SQL is the wrong tool The instinctive fix is to put `DISTINCT` on the select. It does nothing useful here, because `DISTINCT` compares **whole rows** and the rows differ — each carries a different child. Nothing is removed. What you do get is the cost: the engine must sort or hash the entire result to prove the rows are distinct, so the statement gets slower and the duplicates survive. The only place the duplication can be undone is **after the rows have been assembled into objects**, where the parent's identity is known and the layer can collapse entries that share a key. ## Where the duplication actually hurts - **Reported sizes are wrong.** "Found 42 orders" is really 42 order-line rows over some smaller number of orders. - **Loops run too often.** Iterating the result and sending a notification, or adding to a running total, double-charges every parent that has more than one child. - **A row limit stops meaning what you think.** Ten rows is not ten parents once the grid is multiplied. - **Transport is paid per row.** A parent with wide columns is shipped over the wire once per child row, which is often a bigger cost than the duplicate objects themselves. - **Downstream code sees repeats it did not expect**, and defensive de-duplication starts appearing in callers. ## How to confirm it in five minutes 1. Turn on statement logging and read the emitted SQL — if it joins a collection table, expect fan-out. 2. Compare the size of the returned list against the number of **distinct parent keys** in it. If they differ, that is the whole diagnosis. 3. Count the rows the database reported versus the parents you expected. 4. Ask whether the caller needed the collection at all. Not fetching it is the cheapest fix; de-duplicating is the next one. The short version to carry into an interview: **the grid multiplies, the mapper does not.** Any repair happens either by not joining the collection, or by collapsing on identity once the rows are in memory.
- Does the parent's collection itself contain duplicates in this case?No. With a single collection joined, every child row appears exactly once, so the collection is assembled correctly. Only the top-level result list repeats the parent. Duplicates *inside* a collection appear once a second collection is joined in the same statement and multiplies the first one's contents.
- Why does a childless parent vanish from the result, and how do you keep it?An inner join keeps only rows that match on both sides, so a parent with no children produces no row at all and disappears. A left outer join emits one row with null child columns instead, which the layer maps to a parent with an empty collection. Fetching a collection should therefore use an outer join unless you deliberately want only parents that have children.
- Are the repeated entries the same object or copies?It depends on the layer. Where an identity map indexes loaded objects by key within the unit of work, every repeat resolves to the same instance, so a change through one entry is visible through all of them. A layer that maps rows straight to plain records with no such index can return separate copies that drift apart.
It is the same shape as exporting orders to a spreadsheet: the customer's name and address are repeated on every line of the order, because a spreadsheet, like a result grid, has no way to nest lines under a header.
saying these in an interview costs you the question
- Thinks a join can return a nested parent-with-children shape
- Expects one result row per parent when a collection is joined
- Says adding DISTINCT to the SQL removes the repeated parents
- Assumes the repeats are different parents rather than one parent listed twice
- Blames the object mapping instead of the join's row grid
- Believes the duplication also corrupts the single joined collection