Validating Row Counts - SQL

1. What row count should an inner join from the fact table to the dimension table produce?
   Answer: $1{,}010$ rows, because the duplicated key adds two extra rows per referencing fact row. Whenever you see an inner join involving duplicate keys, your job is to think about the Cartesian product formed between matching rows — not just which rows survive. An inner join keeps only rows where the join condition matches, but when a key appears multiple times on one side, each match on the left pairs with every matching row on the right. Here, key K appears 3 times in the dimension and 15 times in the fact table. Those 15 fact rows don't each find one match — they each find 3 matches, producing $15 \times 3 = 45$ rows from key K alone. The remaining $1{,}000 - 20 - 15 = 965$ fact rows each match exactly one dimension row, contributing $965$ rows. The total is $965 + 45 = 1{,}010$, confirming C is correct. A is wrong because it assumes only unmatched rows are dropped and ignores the duplication effect — it would be correct only if every dimension key were unique. B claims the duplicated key adds one extra row per referencing fact row (i.e., $15 \times 2 = 30$ extra, giving $995$), but this confuses "2 copies" with "3 copies" — three dimension rows for K means two extras per fact row, not one. D misreads the duplication entirely, treating each duplicated dimension row as adding a full set of 15 rows rather than multiplying the existing matches. The key study tip: always compute join output as $\text{(left-side matches)} \times \text{(right-side matches per key)}$. Duplicate keys multiply rows — they don't just add them linearly.
2. If the posted-status predicate is placed in the ON clause of a left join, what row count should be expected?
   Answer: $750$ rows, because posted transactions remain and accounts without posted transactions add null-extended rows. When you place a filter condition in the ON clause of a LEFT JOIN rather than the WHERE clause, the behavior changes fundamentally: the condition controls which rows match, but the left table still retains every row. Unmatched left-table rows simply receive NULL for the right-table columns. This is the core concept being tested here. With that in mind, here's the math: $350$ accounts have at least one posted transaction, producing $600$ matched rows. The remaining $150$ accounts find no posted match under the ON predicate, so they appear as $150$ null-extended rows. That gives you $600 + 150 = 750$ total rows — confirming B is correct. A is wrong because it treats the left join like an inner join. Filtering in ON does not eliminate unmatched left-table rows; it only determines what gets joined on the right side. Those $150$ accounts still show up. C ($950$) has no logical basis. You don't simply add the account total ($500$) to the posted-transaction count ($600$) minus some overlap — that arithmetic doesn't reflect how join output is computed. D ($1{,}100$) would imply every account row appears plus every transaction row independently, which describes neither a join nor any standard SQL operation. It confuses row-level joining with set union. A helpful rule of thumb: moving a predicate from WHERE to ON in a left join "softens" the filter — it becomes a join condition rather than a post-join exclusion, preserving all left-side rows.
3. Which validation most appropriately determines whether $230$ is the correct row count?
   Answer: For each order, multiply its payment count by its shipment count, then sum those products. When you join a parent table to multiple child tables simultaneously, you're dealing with a fan-out multiplication problem. Each order row gets duplicated once per matching payment and once per matching shipment — and these two expansions multiply together, not add together. Understanding this is the key to validating multi-table join results. The correct approach is D. For any given order, if it has $p$ payments and $s$ shipments, the join produces $p \times s$ rows for that order — every payment gets paired with every shipment. The true expected row count is $\sum_{i=1}^{100}(p_i \times s_i)$, summed across all orders. This is the only formula that reflects how relational joins actually behave when two child tables are joined to the same parent. Choice A is wrong because $140 + 160 = 300$ assumes each child row appears exactly once, independently. It ignores the multiplicative fan-out — payments and shipments are cross-joined per order, not stacked. Choice B is wrong because the larger table (shipments, with 160 rows) does not "cap" anything. An order with 3 payments and 4 shipments contributes 12 rows, which exceeds either child table's individual contribution. Choice C is wrong because $100 + 40 + 60 = 200$ uses a subtraction heuristic that has no basis in join mechanics. It would only make sense if joins were additive, which they are not. As a study tip: whenever you see a parent joined to two child tables, always think multiplication per parent row, not addition. Sketch a small example (e.g., 1 order, 2 payments, 3 shipments = 6 rows) to build the intuition quickly.
4. What is the most appropriate conclusion from the decrease at the second stage?
   Answer: A row-removing operation or noncomparable snapshot exists because a pure left join cannot reduce its left input. Whenever you encounter a question about row counts changing through a join sequence, your first move should be to recall the fundamental guarantee of a left join: it can never reduce the number of rows below the left input's count. Every row on the left side is preserved — either matched with a right-side row or padded with NULLs. This is the cornerstone of this question. Here, the intermediate result has $1{,}320$ rows entering the second stage. After a pure left join to table C, the output must have at least $1{,}320$ rows — yet only $1{,}290$ remain. That mathematical impossibility is the signal. Something other than a plain left join occurred: perhaps a filter was applied afterward, the counts came from different pipeline runs or snapshots, or the join type was mischaracterized in documentation. Answer C correctly identifies this: a row-removing operation or a noncomparable snapshot must exist, because the observed behavior is structurally incompatible with a left join. Answer A is wrong because duplicate keys in C would increase the row count, not decrease it — each left row could match multiple right rows, producing fans. Answer B is the most tempting distractor, but it describes inner join behavior. Unmatched rows in a left join produce NULL-filled columns, not dropped rows — the left side is always preserved. Answer D is partially grounded in SQL truth (NULLs don't match NULLs), but NULL join keys would simply produce unmatched rows, which a left join still retains as NULL-padded output — no rows would be lost. As a study tip: whenever a left join appears to reduce rows, treat it as a red flag that something undocumented — a filter, a DISTINCT, or a snapshot mismatch — is hiding in the pipeline.
5. What row count should be expected from orders o LEFT JOIN line_items li ON o.order_id = li.order_id?
   Answer: $308$ rows, because line items produce $300$ rows and unmatched orders add $8$ rows. When working with JOIN operations, your job is to think row-by-row: for each row on the left table, how many matching rows exist on the right? That multiplying effect — not simple addition or subtraction — is what determines your final row count. In a LEFT JOIN, every row from the left table (orders) appears in the result at least once. For orders with line items, the order row is duplicated for each matching line item. For orders without matches, the order row still appears once, with NULL filling the line-item columns. Here, $120 - 8 = 112$ orders have at least one line item, and those $112$ orders collectively account for all $300$ line-item rows. The remaining $8$ unmatched orders each contribute exactly one NULL-padded row. So the total is $300 + 8 = 308$ rows, confirming C is correct. A is wrong because it assumes the result is capped at the line-item count. A LEFT JOIN guarantees unmatched left-side rows still appear, so you cannot stop at $300$. B describes simple addition of both table sizes ($120 + 300 = 420$), which would only make sense if every order row were preserved independently and stacked — that's not how joins work. Matched rows merge, not stack. D ($412$) seems to subtract the $8$ unmatched orders from $420$, which applies the wrong operation entirely and ignores how unmatched rows actually behave in a left join. A reliable tip: for any join question, ask "how many times does each left-side row appear?" — the answer is always equal to its match count (or $1$ if unmatched in a LEFT JOIN).
6. What counts should be returned, respectively, by an inner join, a left join, and a left anti-join implemented with WHERE dimension.key IS NULL?
   Answer: $720$, $800$, and $80$ rows, respectively. When working with joins, you need to carefully track which rows qualify for each join type based on NULL handling and key matching. Start by breaking down the $800$ source rows: $30$ have a null foreign key, $50$ have a non-null foreign key that doesn't exist in the dimension, and $720$ match exactly one dimension row ($800 - 30 - 50 = 720$). An inner join returns only rows where both sides match. Nulls never match anything, and unmatched non-null keys are excluded — so you get exactly $720$ rows. A left join preserves every source row regardless of whether it matches, so all $800$ rows are returned (unmatched rows simply get NULLs on the dimension side). A left anti-join using WHERE dimension.key IS NULL filters for rows where the dimension produced no match — that's the $30$ null-key rows plus the $50$ non-null unmatched rows, totaling $80$. This confirms answer **A: $720$, $800$, and $80$**. Choice B is wrong because it claims the anti-join returns $50$ — forgetting that null foreign keys also fail to match and appear in the result. Choice C is wrong on two counts: it incorrectly drops the left join count to $770$ (excluding the $30$ null-key rows, which a left join should still preserve) and gives only $30$ for the anti-join. Choice D invents a left join count of $850$, which would require duplicate matches — impossible when the dimension key is unique. A useful rule of thumb: left join = all source rows; inner join = matched rows only; anti-join via IS NULL = all unmatched source rows, including those with null foreign keys.
7. Which conclusion is supported by these counts?
   Answer: Exactly $120$ customers have no orders because those rows contain a null order identifier. When you perform a LEFT JOIN, every row in the result set represents either a matched or unmatched record from the left table. The key insight is how COUNT(*) and COUNT(column) behave differently: COUNT(*) counts every row regardless of nulls, while COUNT(column) skips rows where that column is null. Here, COUNT(*) = 260 tells you the join produced 260 total rows. COUNT(o.order_id) = 260 would mean every customer matched an order — but instead you get COUNT(o.order_id) = 140. Since order_id is never null in the original orders table, any null order_id in the result must come from an unmatched left-join row (a customer with no orders). That gives you $260 - 140 = 120$ customers without orders, confirming B is correct. A is wrong because the $260 - 140 = 120$ gap doesn't indicate multiple orders per customer — it indicates missing matches (nulls). You'd need COUNT(*) > COUNT(DISTINCT customer_id) to detect duplicate rows from multiple orders. C is wrong because 140 non-null order_id values don't guarantee 140 distinct customers. A single customer with multiple orders would contribute multiple non-null order_id rows, so you'd need COUNT(DISTINCT customer_id) among matched rows to be sure. D is wrong because you can derive the number of orderless customers directly — the null count from the LEFT JOIN gives you exactly that without any additional distinct counting. Study tip: Always distinguish COUNT(*) from COUNT(column) in LEFT JOIN scenarios — the difference between them directly equals the number of unmatched left-table rows.
8. Which validation result most directly confirms that the omitted company key caused the row-count discrepancy?
   Answer: A join on both key columns returns $550$ rows, and the $25$ excess rows disappear. When diagnosing a join gone wrong, the gold standard is controlled comparison: isolate the variable you suspect, change it, and observe whether the problem disappears. That's exactly the logic this question tests. Here, joining only on invoice_id produces $575$ rows instead of the expected $550$. The hypothesis is that omitting company_id causes cross-matching — allocation rows incorrectly matching invoices from the wrong company but with the same invoice_id. To confirm this, you need evidence that adding the missing key resolves the inflation. Answer A is correct because it provides a direct before-and-after comparison. Joining on both (company_id, invoice_id) returns exactly $550$ rows — matching the allocation table's count — and the $25$ phantom rows vanish. This is causal confirmation: changing only the join condition eliminates the discrepancy, proving the omitted column was responsible. Answer B is a distractor. The fact that $550$ distinct allocation IDs still appear doesn't explain why $575$ rows were produced — it just confirms duplicates exist on the invoice side. It describes a symptom, not a cause-and-effect validation. Answer C simply restates the input row counts, which were already given in the problem setup. Knowing the tables have $400$ and $550$ rows before the join tells you nothing about why the join inflated the result. Answer D is a trap. A left join preserving $575$ rows only confirms that the extra rows aren't NULL-padded — it doesn't isolate the join key as the culprit. Study tip: When debugging join inflation, always test the fix directly — correcting the join condition and verifying the expected row count is the most airtight validation strategy.
9. Which conclusion is justified by these validation counts?
   Answer: Every source event is represented in the output, and at least one event produced more than one joined row due to fanout in the attributes table. When validating joins, two counts tell you different things: COUNT(*) reveals the total output rows, while COUNT(DISTINCT event_id) tells you how many unique source records survived into the result. Learning to read these together is the core skill being tested here. The events table has $10{,}000$ rows, and COUNT(DISTINCT event_id) = 10{,}000 confirms every single source event appears in the join output — none were silently dropped. Meanwhile, `COUNT(*) = 10{,}250$means the output has$250$more rows than the source. This excess is the signature of **fanout**: at least one event matched multiple rows in the attributes table, causing its event_id to appear on multiple output rows. Answer B captures both facts precisely — full event retention and evidence of fanout — making it the justified conclusion. Answer A is tempting but overreaches. The math does not tell you *how* the 250 extra rows are distributed. They could all come from one event, or from many events unevenly. "Distributed evenly" is an assumption the data never supports. Answer C makes a logical error. A left join *preserves* unmatched source rows as NULLs — those NULLs would still be counted by `COUNT(*)` but *excluded* from `COUNT(DISTINCT event_id)`. So if any events were unmatched, the distinct count would still be$10{,}000$, making this count alone insufficient to prove every event has an attribute row. Answer D conflates row identity with row count validity. Preserving all$10{,}000$$ distinct IDs does not confirm the total count is correct — that's exactly what the discrepancy reveals. Study tip: Always pair COUNT(*) with COUNT(DISTINCT key) when auditing joins. The gap between them diagnoses fanout; equality between the distinct count and your source row count confirms retention.
10. How many rows should a full outer join on the key return?
    Answer: $10$ rows, using pairwise matches for key $1$ and unmatched rows for other keys. When working with JOIN operations, the key is understanding exactly how rows are matched and combined. A full outer join returns every pairwise combination of matching rows, plus any unmatched rows from either table padded with NULLs. Let's walk through the data. Table A has: two rows with key $1$, one row with key $2$, one row with key $3$. Table B has: three rows with key $1$, two rows with key $4$. For key $1$, the join creates every pairwise combination: $2 \times 3 = 6$ matched rows. Key $2$ appears only in A — that's $1$ unmatched row. Key $3$ appears only in A — that's $1$ unmatched row. Key $4$ appears only in B — that's $2$ unmatched rows. Total: $6 + 1 + 1 + 2 = 10$ rows, confirming D is correct. A is wrong because using the "larger multiplicity" ($3$ for key $1$) ignores the cross-product nature of joins — it's not a max operation. B is wrong because counting "one matched row per shared key" misunderstands how joins work; matching rows combine pairwise, not one-to-one. C is wrong because summing all input rows ($4 + 5 = 9$) would only be correct if every row were unmatched — it ignores the multiplication effect of the cross-product on key $1$. A useful tip: whenever a join key has multiplicity $m$ in one table and $n$ in the other, always calculate $m \times n$ matched rows for that key, then add unmatched rows separately.

Start this quiz