SQL Quiz: Validating Row Counts
10 questions · exam conditions
0:00
Validating Row CountsQuestion 1 of 10

An orders table contains 120120 rows, one per order. A line_items table contains 300300 rows. Exactly 88 orders have no line items, and every line item references one valid order.

What row count should be expected from orders o LEFT JOIN line_items li ON o.order_id = li.order_id?

300300 rows, because the joined table cannot exceed the number of line-item rows.
420420 rows, because a left join adds the row counts of both input tables.
308308 rows, because line items produce 300300 rows and unmatched orders add 88 rows.
412412 rows, because only the 88 unmatched orders should be excluded from the input total.
← Back to quizzes

SQL Quiz

SQL Quiz: Validating Row Counts

Practice Validating Row Counts in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Validating Row Counts, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An orders table contains 120120 rows, one per order. A line_items table contains 300300 rows. Exactly 88 orders have no line items, and every line item references one valid order.

What row count should be expected from orders o LEFT JOIN line_items li ON o.order_id = li.order_id?

  1. 300300 rows, because the joined table cannot exceed the number of line-item rows.
  2. 420420 rows, because a left join adds the row counts of both input tables.
  3. 308308 rows, because line items produce 300300 rows and unmatched orders add 88 rows. (correct answer)
  4. 412412 rows, because only the 88 unmatched orders should be excluded from the input total.
Explanation: 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=112120 - 8 = 112 orders have at least one line item, and those 112112 orders collectively account for all 300300 line-item rows. The remaining 88 unmatched orders each contribute exactly one NULL-padded row. So the total is 300+8=308300 + 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 300300. B describes simple addition of both table sizes (120+300=420120 + 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 (412412) seems to subtract the 88 unmatched orders from 420420, 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 11 if unmatched in a LEFT JOIN).

Question 2

An invoices table has 400400 rows and is unique on (company_id, invoice_id), but invoice_id alone is not unique. A payment-allocation table has 550550 rows, each referencing exactly one valid composite invoice key. Joining only on invoice_id returns 575575 rows.

Which validation result most directly confirms that the omitted company key caused the row-count discrepancy?

  1. A join on both key columns returns 550550 rows, and the 2525 excess rows disappear. (correct answer)
  2. The incorrect join still returns 550550 distinct allocation identifiers despite producing 575575 total rows.
  3. The two input tables contain 400400 invoice rows and 550550 allocation rows before the join.
  4. Changing the incorrect join to a left join continues to return the same 575575 joined rows.
Explanation: 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 575575 rows instead of the expected 550550. 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 550550 rows — matching the allocation table's count — and the 2525 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 550550 distinct allocation IDs still appear doesn't explain why 575575 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 400400 and 550550 rows before the join tells you nothing about why the join inflated the result. Answer D is a trap. A left join preserving 575575 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.

Question 3

A pipeline records 1,2001{,}200 rows before joins, 1,3201{,}320 rows after a left join to table B, and 1,2901{,}290 rows after what is documented as only a second left join to table C. All counts were expected to use the same pipeline run.

What is the most appropriate conclusion from the decrease at the second stage?

  1. Duplicate keys in table C must have caused some rows from the first join to be replaced.
  2. Unmatched rows in table C naturally reduced the count even though the join was left-sided.
  3. A row-removing operation or noncomparable snapshot exists because a pure left join cannot reduce its left input. (correct answer)
  4. Null join keys in the intermediate result explain the loss because null values cannot match table C.
Explanation: 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,3201{,}320 rows entering the second stage. After a pure left join to table C, the output must have at least 1,3201{,}320 rows — yet only 1,2901{,}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.

Question 4

An events table contains 10,00010{,}000 rows with unique, non-null event_id values. A left join to an attributes table returns COUNT(*) = 10,250 and COUNT(DISTINCT event_id) = 10,000.

Which conclusion is justified by these validation counts?

  1. Exactly 250250 events each matched exactly two attribute rows, because the 250250 excess rows must be distributed evenly.
  2. 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. (correct answer)
  3. No source event lacks an attribute row, because if any event were unmatched its event_id would be excluded from the distinct count.
  4. The join cardinality is confirmed valid, because preserving all 10,00010{,}000 distinct event identifiers guarantees the total row count is correct.
Explanation: 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,00010{,}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{,}250meanstheoutputhasmeans the output has250$$ 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,00010{,}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,00010{,}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.

Question 5

A fact table contains 1,0001{,}000 rows. Of these, 2020 have no matching dimension key. The dimension normally contains one row per key, but key K appears three times. Exactly 1515 fact rows reference K.

What row count should an inner join from the fact table to the dimension table produce?

  1. 980980 rows, because only the fact rows with a matching dimension key survive.
  2. 995995 rows, because the duplicated key adds one row for each referencing fact row.
  3. 1,0101{,}010 rows, because the duplicated key adds two extra rows per referencing fact row. (correct answer)
  4. 1,0401{,}040 rows, because each duplicated dimension row adds all 1515 referencing fact rows.
Explanation: 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×3=4515 \times 3 = 45 rows from key K alone. The remaining 1,000−20−15=9651{,}000 - 20 - 15 = 965 fact rows each match exactly one dimension row, contributing 965965 rows. The total is 965+45=1,010965 + 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×2=3015 \times 2 = 30 extra, giving 995995), 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 (left-side matches)×(right-side matches per key)\text{(left-side matches)} \times \text{(right-side matches per key)}. Duplicate keys multiply rows — they don't just add them linearly.

Question 6

An accounts table contains 500500 accounts. There are 600600 posted transactions, distributed across 350350 accounts. The remaining 150150 accounts have no posted transactions, although some may have transactions with other statuses.

If the posted-status predicate is placed in the ON clause of a left join, what row count should be expected?

  1. 600600 rows, because only posted transaction rows contribute to the joined output.
  2. 750750 rows, because posted transactions remain and accounts without posted transactions add null-extended rows. (correct answer)
  3. 950950 rows, because the account count and posted-transaction count should be combined after matching.
  4. 1,1001{,}100 rows, because every account is retained in addition to every posted transaction row.
Explanation: 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: 350350 accounts have at least one posted transaction, producing 600600 matched rows. The remaining 150150 accounts find no posted match under the ON predicate, so they appear as 150150 null-extended rows. That gives you 600+150=750600 + 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 150150 accounts still show up. C (950950) has no logical basis. You don't simply add the account total (500500) to the posted-transaction count (600600) minus some overlap — that arithmetic doesn't reflect how join output is computed. D (1,1001{,}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.

Question 7

A table contains 100100 orders. A payments table contains 140140 rows, and a shipments table contains 160160 rows. Every order has at least one payment and at least one shipment. Directly joining orders to both child tables returns 230230 rows.

Which validation most appropriately determines whether 230230 is the correct row count?

  1. Compare 230230 with 140+160140 + 160 because both child-table row counts should be represented once.
  2. Compare 230230 with 160160 because the larger child table sets the maximum possible join count.
  3. Compare 230230 with 100+(140−100)+(160−100)100 + (140 - 100) + (160 - 100) to count each excess child row.
  4. For each order, multiply its payment count by its shipment count, then sum those products. (correct answer)
Explanation: 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 pp payments and ss shipments, the join produces p×sp \times s rows for that order — every payment gets paired with every shipment. The true expected row count is ∑i=1100(pi×si)\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=300140 + 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=200100 + 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.

Question 8

A source table contains 800800 rows. Its foreign key is null in 3030 rows and non-null but absent from the dimension in 5050 rows. Every other source row matches exactly one row in a dimension whose key is unique.

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?

  1. 720720, 800800, and 8080 rows, respectively. (correct answer)
  2. 750750, 800800, and 5050 rows, respectively.
  3. 720720, 770770, and 3030 rows, respectively.
  4. 750750, 850850, and 8080 rows, respectively.
Explanation: 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 800800 source rows: 3030 have a null foreign key, 5050 have a non-null foreign key that doesn't exist in the dimension, and 720720 match exactly one dimension row (800−30−50=720800 - 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 720720 rows. A left join preserves every source row regardless of whether it matches, so all 800800 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 3030 null-key rows plus the 5050 non-null unmatched rows, totaling 8080. This confirms answer A: 720720, 800800, and 8080. Choice B is wrong because it claims the anti-join returns 5050 — 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 770770 (excluding the 3030 null-key rows, which a left join should still preserve) and gives only 3030 for the anti-join. Choice D invents a left join count of 850850, 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.

Question 9

A customers table has one row per customer. After a left join to orders, a validation query returns COUNT(*) = 260 and COUNT(o.order_id) = 140. The orders.order_id column is never null in the orders table.

Which conclusion is supported by these counts?

  1. Exactly 6060 customers have multiple orders because the output exceeds the customer count by 6060.
  2. Exactly 120120 customers have no orders because those rows contain a null order identifier. (correct answer)
  3. Exactly 140140 customers have orders because every non-null order identifier represents a different customer.
  4. The number of customers without orders cannot be derived unless customer identifiers are also counted distinctly.
Explanation: 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=120260 - 140 = 120 customers without orders, confirming B is correct. A is wrong because the 260−140=120260 - 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.

Question 10

Table A contains two rows with key 11, one row with key 22, and one row with key 33. Table B contains three rows with key 11 and two rows with key 44.

How many rows should a full outer join on the key return?

  1. 77 rows, using the larger input multiplicity for each key appearing in either table.
  2. 88 rows, using one matched row per shared key plus all remaining input rows.
  3. 99 rows, using the sum of the row counts from both input tables.
  4. 1010 rows, using pairwise matches for key 11 and unmatched rows for other keys. (correct answer)
Explanation: 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 11, one row with key 22, one row with key 33. Table B has: three rows with key 11, two rows with key 44. For key 11, the join creates every pairwise combination: 2×3=62 \times 3 = 6 matched rows. Key 22 appears only in A — that's 11 unmatched row. Key 33 appears only in A — that's 11 unmatched row. Key 44 appears only in B — that's 22 unmatched rows. Total: 6+1+1+2=106 + 1 + 1 + 2 = 10 rows, confirming D is correct. A is wrong because using the "larger multiplicity" (33 for key 11) 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=94 + 5 = 9) would only be correct if every row were unmatched — it ignores the multiplication effect of the cross-product on key 11. A useful tip: whenever a join key has multiplicity mm in one table and nn in the other, always calculate m×nm \times n matched rows for that key, then add unmatched rows separately.