skip to content

How does a data-access layer remove the parents duplicated by a collection join, and why is a SQL DISTINCT no help?

level: middleimportance: should knowfreq 60%

answer

  1. repair after assembly, not in SQL
  2. rows differ, so DISTINCT drops nothing
  3. collapse keyed on parent identity
  4. cosmetic: the fan-out was already paid
  5. insertion order kept by first occurrence

basics

~20 s

De-duplication happens in memory, after the rows are assembled: entries sharing a parent key collapse to one. A SQL DISTINCT cannot do it because the rows differ in their child columns, so it removes nothing and adds a sort.

solid answer

~40 s

Once the grid is fetched, the layer knows each row's parent key, so it can collapse the result to one entry per key — either automatically, or because the caller asked for a de-duplicated result. Where an identity map exists the collapse is trivial: the repeats are already the same object, so keeping the first occurrence preserves order and loses nothing. What this does **not** buy you is cheaper work: the database still evaluated the fan-out, the rows still crossed the wire, and the collection contents are unaffected. It is a presentation repair on an already-paid cost. A `DISTINCT` in the SQL is the wrong lever because rows carrying different children are genuinely distinct rows; the engine finds nothing to drop and charges you for a sort or hash to prove it.

go deeper

for a junior

Learn the two-step story: the join makes the extra rows, and the layer removes the extra entries afterwards in memory. Putting DISTINCT in the SQL is the classic wrong first move.

for a middle

Be able to say why DISTINCT compares whole rows and therefore finds nothing to remove, and where in the mapping pipeline the identity-keyed collapse happens instead.

for a senior

Separate the correctness repair from the cost. Show that you measure rows returned rather than objects returned, and that you treat an expensive fan-out as a fetch-shape problem, not a de-duplication problem.

for a principal

Decide the default for the codebase: silently collapsing roots is friendly but hides a fan-out that may be the real expense. Weigh a loud default against a safe one, and where the read shape should be owned.

## The problem restated in one line A statement that joins a parent to its child collection returns one row per child row, with the parent's columns repeated. Map one entry per row and the caller sees the same parent several times. The question is where that gets undone. ## Three candidate places, only one of which works 1. **In the database, with `DISTINCT`.** Fails. `DISTINCT` deduplicates whole rows, and every row carries a different child, so no two are equal. Nothing is removed and the engine adds a sort or hash over the full result to establish it. Worse, it is a silent failure — the statement runs, returns the same count, and the developer concludes the duplication must be coming from somewhere else. 2. **In the layer, while assembling objects.** Works. By the time a row is mapped, its parent key is known, so entries that resolve to the same parent can be collapsed into one. Most mappers expose this as a flag or a result shape ("give me the distinct roots"), and some apply it automatically when the results are accumulated through a keyed structure rather than a list. 3. **In the caller, after the fact.** Works, but is the worst version: every call site has to remember, and the de-duplication key has to be re-derived by hand from an object whose equality semantics may not be identity-based. | Where | Removes the duplicates? | Cost added | Cost saved | |---|---|---|---| | SQL `DISTINCT` | No — rows genuinely differ | A sort or hash over the whole result | None | | In the layer, on assembly | Yes | Negligible (a keyed pass) | None at the database | | In the caller | Yes | Repeated at every call site | None | ## What the in-memory collapse actually costs and saves The collapse is cheap: one pass over the assembled rows, keyed on identity. It is also strictly cosmetic with respect to work already done: - **The database still did the fan-out.** The join was evaluated, the rows were produced. - **The wire still carried every row.** A wide parent shipped once per child row is often the dominant cost, and de-duplication happens after that bill is paid. - **The collection is unaffected.** With a single collection joined, each child appears exactly once, so the collection was already correct — you are repairing only the top-level list. - **Order survives** if the layer keeps the first occurrence of each key, which is what a linked, insertion-ordered structure gives you. So it is a correctness repair, not a performance one. If the fan-out itself is the problem, the fix is fetching the collection in a separate statement or not fetching it at all, not de-duplicating harder. ## Why the identity map makes the collapse safe When the layer keeps an **identity map** — an index from key to loaded object, scoped to the unit of work — every repeat of a parent resolves to the *same* instance. Collapsing therefore cannot lose state: there is only ever one object, listed many times. In a thinner layer that maps rows to plain records with no such index, the repeats are separate copies, each holding the child from its own row, and naive de-duplication that keeps only the first copy would **throw children away**. That is why row-mapping layers usually make you group explicitly rather than offering a de-duplicate switch. ## Two things people get wrong about the collapse - **"De-duplicating fixed the slow endpoint."** It fixed the wrong count. If the endpoint got faster, something else changed. Measure rows returned, not objects returned. - **"A set-typed mapped collection means I do not need it."** The collection kind governs duplicates *inside* the collection. It has no bearing on how many times the parent appears in the top-level result list; nothing about the mapping of the collection can suppress that. ## The rule to carry Ask two separate questions of any fetch that joins a collection. First, *is the result list duplicated?* — repair that on identity, in memory. Second, *is the fan-out itself affordable?* — that is a question about rows on the wire, and the only answers are fetching fewer collections per statement, splitting into separate statements, or projecting just the fields the screen needs.

  • If de-duplication is in memory, does the database do less work?
    No. The join is evaluated, the rows are produced and shipped before the layer sees anything. The collapse only changes what the caller is handed. To reduce the actual work you have to stop producing the extra rows — fetch the collection in its own statement, or project only the columns the caller needs.
  • Does collapsing the result list ever lose data?
    Not where an identity map guarantees the repeats are one object. It can where the layer maps each row into a separate plain record, because each copy then holds only its own row's child; keeping the first copy discards the rest. Such layers usually ask you to group by key explicitly instead of offering a de-duplicate flag.
  • How do you tell whether the layer already de-duplicated for you?
    Compare the result size against the number of distinct parent keys, and against the row count the statement log reports. Equal list size and distinct keys with a larger row count means the layer collapsed; all three equal means there was no fan-out to collapse.

saying these in an interview costs you the question

  • Reaches for DISTINCT in the SQL to remove repeated parents
  • Claims in-memory de-duplication reduces the database's work
  • Thinks de-duplicating also fixes rows on the wire
  • Assumes every layer collapses duplicate roots by default
  • De-duplicates by hand in each caller instead of in the read
  • Believes the joined collection itself needs de-duplicating too