SQL Practice Patterns IV — Join Grain & Fan-out, GROUP_CONCAT Portability, NULL Ordering & Self-Join Cost (Deep Dive)

This deep dive closes out the SQL Practice Patterns series with four judgment calls that look like syntax questions but are actually about knowing the shape of your data before you write the query: what grain each table's rows live at before you join them, what an aggregate function does when its output is too long for the engine to hold, what a NULL is worth when the database has to put it somewhere in a sorted list, and what a self-join actually costs once the table stops being a five-row toy example. Patterns I covered the missing-group / zero-result pattern (INNER vs LEFT JOIN + COALESCE) and a first look at GROUP_CONCAT truncation; Patterns II covered self-join fan-out and DISTINCT (deduplicating a join that pairs every partner against every partner) and the gaps-and-islands calendar-day trap. This page goes one level deeper on a different slice: fan-out that corrupts a SUM rather than just inflating a COUNT, the exact cross-engine syntax for grouped string aggregation, why the same ORDER BY can put NULLs in opposite places on different engines, and when a self-join's quadratic cost is the real problem — not just its correctness.

1. Join grain and row fan-out: aggregating after a join to a finer grain

Mechanism first: every table has a grain — the answer to "one row of this table represents one what?" A join matches rows on a key; if that key is not unique on one side, every row on the unique side gets paired with each matching row on the other side, and whatever columns it carries are repeated once per match. That repetition is invisible when you only look at individual rows — it only shows up once you aggregate, because SUM/AVG/COUNT can't tell a genuinely repeated real-world fact from a value that was duplicated purely as a side effect of the join.

Traced example — revenue per customer, corrupted by a finer-grain join

Orders(order_id, customer_id, amount) — grain: one row per order. OrderItems(item_id, order_id, product) — grain: one row per line item, several items per order. Order 101 (customer C1, amount = $50) has 3 items; order 102 (customer C1, amount = $30) has 1 item. The goal is "total revenue per customer":

-- WRONG: joins order-grain Orders to item-grain OrderItems, then sums the order-grain column
SELECT o.customer_id, SUM(o.amount) AS revenue
FROM Orders o
JOIN OrderItems oi ON oi.order_id = o.order_id
GROUP BY o.customer_id;

order_id is unique in Orders but appears 3 times in OrderItems for order 101. The join therefore emits order 101's row three times — once per matching item — each copy still carrying amount = $50. SUM(o.amount) then adds 50 + 50 + 50 + 30 = $180, when the true revenue is 50 + 30 = $80. Nothing in the query is syntactically wrong; the bug is entirely in not checking the grain of the right-hand side before joining to it.

The fix: aggregate to the target grain before joining

-- RIGHT (this query doesn't need OrderItems at all -- Orders is already at the right grain)
SELECT customer_id, SUM(amount) AS revenue
FROM Orders
GROUP BY customer_id;

-- RIGHT (if a column from OrderItems genuinely is needed, e.g. item_count):
-- aggregate OrderItems down to order grain FIRST, so both sides of the join are 1-row-per-order
SELECT o.customer_id,
       SUM(o.amount)       AS revenue,
       SUM(oi.item_count)  AS total_items
FROM Orders o
JOIN (
    SELECT order_id, COUNT(*) AS item_count
    FROM OrderItems
    GROUP BY order_id
) oi ON oi.order_id = o.order_id
GROUP BY o.customer_id;

In the second, correct query, the subquery collapses OrderItems to exactly one row per order_id — the same grain as Orders — before the join runs, so the join can no longer duplicate anything: both sides now have a unique key. The general rule: before writing a join, name the grain of each side out loud ("one row per order," "one row per line item," "one row per customer-day"). If a join's key is non-unique on either side and you plan to aggregate a column from the other side afterward, aggregate that other side down to a matching grain first, or restrict the join key to something unique on both sides.

2. GROUP_CONCAT / STRING_AGG: portability and the silent truncation trap

Mechanism first: every major engine has a "concatenate this group's values into one string" aggregate, but they don't share syntax, and one of them has a hard length ceiling the others don't. Patterns I already introduced GROUP_CONCAT's truncation behavior in one line; here is the exact cross-engine syntax and why the cap bites in practice.

Three different call shapes for the same job — a query built against one engine will not run unmodified on another, and the ordering clause is the part most likely to trip a manual port.

The truncation trap

MySQL's GROUP_CONCAT output is capped by the session variable group_concat_max_len, whose stock default is 1024 bytes. When the concatenated string would exceed that length, MySQL truncates it silently — it emits a truncation warning, not an error, and a warning is trivially easy for application code to never check. A "list every tag on this post" or "list every email that bounced for this campaign" query can ship correct-looking output for small groups for months, then quietly clip the list the day one group crosses the byte cap — with no exception thrown anywhere in the stack to say so.

-- Raise the cap before running a query whose GROUP_CONCAT output could be long
SET SESSION group_concat_max_len = 1000000;

SELECT customer_id, GROUP_CONCAT(product_name ORDER BY product_name SEPARATOR ', ') AS products
FROM order_items
GROUP BY customer_id;

PostgreSQL's and SQL Server's STRING_AGG have no equivalent silent cap (they are bounded only by the engine's general max field/row size, which is orders of magnitude larger and fails loudly, not silently, if ever hit). A query that "worked" only because its MySQL truncation happened to hide the problem will behave differently — correctly, but differently — the moment it's ported. The durable fix on MySQL is twofold: raise group_concat_max_len explicitly for any query whose group sizes aren't tightly bounded, and treat GROUP_CONCAT/STRING_AGG as a reporting convenience, not a data-integrity-bearing operation — if the full list must be provably complete, fetch the rows and assemble the list in application code instead.

3. NULL semantics: WHERE discards UNKNOWN, and ORDER BY disagrees by engine

Mechanism first: SQL comparisons involving NULL don't evaluate to FALSE — they evaluate to a third value, UNKNOWN, because a comparison against an absent value can't be truthfully answered either way. WHERE keeps a row only when its condition is TRUE; both FALSE and UNKNOWN are discarded. So WHERE salary > 100 silently drops every row with a NULL salary — not because those rows fail the test, but because the test can't be evaluated for them at all, and "can't tell" is treated the same as "no." (COUNT(column) follows the same logic one level down — it skips NULLs rather than erroring on them; Patterns I covers COUNT semantics across */column/DISTINCT column in full.)

The ORDER BY surprise

NULL has no defined position in a sort order, so the SQL standard leaves each engine free to pick where it lands — and two major engines picked opposite defaults. PostgreSQL and Oracle treat NULL as sorting larger than any real value; MySQL and SQL Server treat it as sorting smaller than any real value. The same query can therefore place the same NULL row in opposite positions depending only on which engine runs it:

SELECT employee_id, salary FROM Employee ORDER BY salary DESC;

Trace it against rows (salary: 300, NULL, 100). On PostgreSQL, NULL counts as the largest value, so DESC (largest first) puts it first: NULL, 300, 100. On MySQL, NULL counts as the smallest value, so the same DESC puts it last: 300, 100, NULL. Nothing about the query changed — only the engine's silent default for where an undefined value belongs in an ordering it was never part of.

The fix is to stop relying on the default and say what you mean:

-- PostgreSQL / Oracle: explicit NULLS FIRST / NULLS LAST is standard syntax
SELECT employee_id, salary FROM Employee ORDER BY salary DESC NULLS LAST;

-- MySQL / SQL Server: no NULLS FIRST/LAST syntax -- emulate with an explicit is-null key
SELECT employee_id, salary FROM Employee
ORDER BY (salary IS NULL) ASC, salary DESC;   -- forces NULL rows to sort last, regardless of engine default

The emulated form works everywhere (including Postgres/Oracle) because salary IS NULL evaluates to a boolean that itself sorts predictably (0/false before 1/true in ASC), giving explicit control without depending on any engine's NULL-ordering default at all.

4. Self-join cost: the quadratic blow-up, NULL foreign keys, and when a window function is cheaper

Mechanism first: a self-join is still just a join — the engine picks a join algorithm based on whether the join column is indexed, exactly as it would for two different tables. Without an index on the join column, the only available plan is a nested loop: for every one of the n outer rows, scan all n inner rows looking for a match. That is O(n²) comparisons — and because both "sides" of a self-join are the same table, there is no smaller table to drive the loop from; the full row count is on both sides.

Worked example — "employees earning more than their manager"

SELECT e1.employee_id, e1.salary, e2.employee_id AS manager_id, e2.salary AS manager_salary
FROM Employee e1
JOIN Employee e2 ON e1.manager_id = e2.employee_id
WHERE e1.salary > e2.salary;

At n = 100 employees, an unindexed nested loop does on the order of 100² = 10,000 comparisons to resolve the join — trivial. At n = 10,000, that's ~100,000,000. At n = 1,000,000, it's ~10¹² — a trillion comparisons, and the gap to the indexed case (below) keeps widening as n grows, it never narrows.

A B-tree index on Employee(employee_id) already exists implicitly if it's the primary key; what actually needs an index for this query is the join column on the other side, manager_id. With an index on manager_id (or, for the direction the plan actually probes, an index that lets the engine look up e2.employee_id = e1.manager_id for each outer row via its primary-key index — already present — the lookup per outer row becomes an index seek, not a table scan), the per-row cost drops from O(n) to O(log n), and the whole join becomes O(n log n) — the second bar series above.

The NULL foreign key inside the comparison

A CEO (or any root of the hierarchy) has manager_id IS NULL. The join condition e1.manager_id = e2.employee_id compares NULL to every employee_id value; every one of those comparisons is UNKNOWN, so the CEO's row never matches anything and is silently dropped from the join's output. Here that's the desired behavior — a person with no manager cannot be "earning more than their manager" — but it's worth naming explicitly: any self-join on a nullable foreign key drops every row whose FK is NULL, with no error and no warning, and that is only safe when the missing comparison is genuinely meaningless for the question being asked.

When a window function replaces the self-join more cheaply

Not every self-join shape has a window-function substitute. "Compare each row to its own parent via an arbitrary foreign key" (the manager example) is a graph-edge traversal — there's no partition or ordering that turns "my manager's salary" into a windowed lookup, so a self-join (or a recursive CTE for multi-level hierarchies) is the right tool, and the fix for its cost is the index above, not a rewrite. Contrast that with a self-join used to compare a row to its neighbor in a sequence — e.g. "day-over-day change" or "flag consecutive identical values" — which is a windowed relationship (previous row within an ordered partition) and is both cheaper and clearer as LAG()/LEAD() in one pass, with no join at all:

-- Self-join version: compare each day's reading to the previous day's, per sensor
SELECT a.sensor_id, a.reading_date, a.value - b.value AS delta
FROM Readings a
JOIN Readings b
  ON a.sensor_id = b.sensor_id
 AND b.reading_date = DATE_SUB(a.reading_date, INTERVAL 1 DAY);

-- Window-function version: same result, one pass, no join
SELECT sensor_id, reading_date,
       value - LAG(value) OVER (PARTITION BY sensor_id ORDER BY reading_date) AS delta
FROM Readings;

Likewise a self-join used only to compare a row against a group aggregate ("employees earning more than their department's average") is better expressed as AVG(salary) OVER (PARTITION BY department_id) — one pass instead of a join against a derived aggregate table. The dividing line: a self-join against a fixed, arbitrary parent pointer (manager, category-parent) stays a self-join; a self-join standing in for "the previous/next row" or "this row vs. its partition's aggregate" is almost always a window function in disguise.

5. Query-plan reasoning across these patterns, briefly

Which index actually supports the query: for an equi-join or equality filter, the index needs to lead with the column the join/filter condition tests — a composite index's leading column should be the equality-tested one, with range or sort columns following (the Query Execution deep dive's sargability section covers why a wrapped or non-leading column defeats the index). The "degenerate side" of a join — the side the plan drives the outer loop from — matters less for which side is indexed and more for which side is smaller/pre-filtered; the inner, probed side is the one that most needs the index, since it's re-visited once per outer row.

Books-with-zero-checkouts and denominator scoping: this is Patterns I's missing-group pattern applied to a ratio. To compute "fraction of books with at least one checkout," the denominator (all books) must be preserved by driving from a LEFT JOIN on the books table — an INNER JOIN would silently drop zero-checkout books from the denominator, not just the numerator, corrupting the fraction itself:

SELECT
    COUNT(DISTINCT c.book_id) * 1.0 / COUNT(DISTINCT b.book_id) AS fraction_checked_out
FROM Books b
LEFT JOIN Checkouts c ON c.book_id = b.book_id;

In plain text: fraction = (count of distinct entities that matched) / (count of distinct entities in the full denominator set), where the denominator side must come from a LEFT JOIN's always-preserved side, and the numerator's COUNT(DISTINCT c.book_id) naturally excludes the NULLs that unmatched books produce on the Checkouts side — no extra COALESCE needed for a COUNT, only for a SUM/AVG (Patterns I, §4).

Edge cases to check before trusting any of these patterns at scale: a composite index built in the wrong column order silently degrades to a slower access path without erroring; a nullable FK column in any equi-join (self-join or not) drops NULL-FK rows with no warning, exactly as in §4 above; and an INNER JOIN anywhere in a ratio's denominator path is the single most common way a "correct-looking" percentage is quietly computed over the wrong population.

Pitfalls

• Joining an order-grain (or any coarser-grain) table to a finer-grain table before aggregating the coarser column. The join silently duplicates the coarser column once per matching fine-grained row; the fix is aggregating to the target grain before the join, not deduplicating after.
• Trusting GROUP_CONCAT's output length on MySQL. The default 1024-byte cap truncates silently with only a warning — always check or raise group_concat_max_len for any group that could genuinely grow large.
• Assuming ORDER BY col DESC puts NULLs in the same place on every engine. Postgres/Oracle sort NULL as largest; MySQL/SQL Server sort it as smallest — the same query, unchanged, places NULL rows at opposite ends depending only on the engine.
• Assuming a self-join is "just" an O(n) scan because the table looks small in a test dataset. Without an index on the join column it is a nested loop, O(n²), and the gap to the indexed case widens, not narrows, as the table grows.
• Reaching for a self-join when a window function already expresses the relationship. "Previous row" and "partition aggregate" comparisons are cheaper and clearer as LAG()/LEAD() or a windowed aggregate than as a join back onto the same table.
• Letting an INNER JOIN sit anywhere in a ratio's denominator. It silently shrinks the population the percentage is computed over, not just the numerator — the same missing-group failure mode as Patterns I, one level removed.

Judgment layer: how a senior engineer decides

• Aggregate-first vs. join-then-aggregate: if any table on the far side of a join is not at the grain of the column you're about to aggregate, aggregate that table down to the matching grain in a subquery/CTE first, then join. Only join a raw, un-aggregated table directly into an aggregation when its join key is genuinely unique relative to the grouping key — verify that, don't assume it.
• Self-join vs. window function: is the comparison against an arbitrary parent pointer in a graph/hierarchy (manager, category parent)? Keep the self-join (or promote it to a recursive CTE for multi-level cases) and fix cost with an index. Is it against "the previous/next row in some order" or "this row vs. its group's aggregate"? Prefer LAG()/LEAD() or a windowed aggregate — one pass, no join, and it reads as the relationship it actually is.
• When to stop trusting an engine's default: NULL ordering and GROUP_CONCAT's cap are both places where "it worked in dev" hides an engine-specific default. Any query whose correctness depends on where NULL sorts, or on how long a concatenated string can get, should say so explicitly (NULLS FIRST/LAST or the emulated form; an explicit, raised group_concat_max_len) rather than inherit a default that differs by engine or version.

Takeaways

• Know the grain of every table before joining it into an aggregation — a non-unique join key duplicates the other side's column once per match, and SUM cannot detect that the duplication happened.
• GROUP_CONCAT's 1024-byte default cap truncates silently on MySQL; STRING_AGG (Postgres/SQL Server) has different syntax and no equivalent silent cap — treat cross-engine ports of grouped-string queries as a correctness check, not just a syntax swap.
• NULL comparisons are UNKNOWN, which WHERE discards like FALSE — and NULL's position in an ORDER BY is an engine-specific default (largest on Postgres/Oracle, smallest on MySQL/SQL Server) that should be made explicit, not inherited.
• An unindexed self-join is an O(n²) nested loop; index the join column to make it O(n log n), and reach for LAG()/LEAD() or a windowed aggregate instead of a self-join whenever the comparison is really "previous row" or "group aggregate," not an arbitrary parent pointer.

Related pages

• SQL Practice Patterns I — NULL Traps, COUNT Semantics, Sargability & the Missing-Group Pattern (Deep Dive) — the missing-group pattern and COUNT semantics this page reuses in §3 and §5.
• SQL Practice Patterns II — Window-Function Ties, WHERE-vs-ON Placement, Self-Join Fan-out & Portability (Deep Dive) — the self-join fan-out/DISTINCT pattern this page's §1 and §4 build on.
• Query Execution — Selectivity, the Cost Model, Join Algorithms, Spills & Sargability (Deep Dive) — the full sargability/index-order mechanics behind §5's composite-index note.
• Indexing & Storage — Fanout Arithmetic, Optimizer Stats, Composite/Skip Scans & NULL/OR Pitfalls (Deep Dive) — the indexing arithmetic behind the self-join cost analysis in §4.

Continues this guide's SQL Practice Patterns I–III series (join-grain, GROUP_CONCAT/STRING_AGG, NULL WHERE/ORDER BY semantics, and self-join cost drawn from recurring practice-problem shapes: revenue-per-customer/order-item rollups, tag/list reporting queries, NULL-inclusive sorts, and manager-salary-style self-joins). Re-authored/Deepened for this guide.