Business Analytics Quiz: Table Joins
10 questions · exam conditions
0:00
Table JoinsQuestion 1 of 10

A retailer needs a report containing every customer, along with details for any orders placed on or after January 1, 2026. Customers with no qualifying orders must still appear with null order fields.

Which join design satisfies the requirement without unintentionally removing customers who have only older orders?

Use an INNER JOIN on customer ID and place the qualifying order-date condition in the ON clause.
Use a LEFT JOIN on customer ID and place the qualifying order-date condition in the WHERE clause.
Use a LEFT JOIN on customer ID, then filter for qualifying dates or a null order ID in the WHERE clause.
Use a LEFT JOIN and place both customer-ID matching and the qualifying order-date condition in the ON clause.
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Table Joins

Practice Table Joins in Business Analytics 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 Table Joins, giving you a quick way to practice the rules, question types, and explanations that matter most for Business Analytics.

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

A retailer needs a report containing every customer, along with details for any orders placed on or after January 1, 2026. Customers with no qualifying orders must still appear with null order fields.

Which join design satisfies the requirement without unintentionally removing customers who have only older orders?

  1. Use an INNER JOIN on customer ID and place the qualifying order-date condition in the ON clause.
  2. Use a LEFT JOIN on customer ID and place the qualifying order-date condition in the WHERE clause.
  3. Use a LEFT JOIN on customer ID, then filter for qualifying dates or a null order ID in the WHERE clause.
  4. Use a LEFT JOIN and place both customer-ID matching and the qualifying order-date condition in the ON clause. (correct answer)
Explanation: Whenever a question asks you to "keep all rows from one table while selectively joining another," you're being tested on the interaction between JOIN type and filter placement — a subtle but critical SQL concept. The requirement has two parts: return every customer, and attach only orders dated on or after January 1, 2026. The trick is that filtering in the WHERE clause happens after the join, so it can silently discard rows you meant to keep. The safest design is to push all matching conditions — including the date filter — directly into the ON clause of a LEFT JOIN. This way, the database attempts to match each customer to qualifying orders only; customers with no match (older orders or no orders at all) still appear with null order fields, exactly as required. That's why D is correct. Choice A uses an INNER JOIN, which outright eliminates any customer lacking a qualifying order — customers with only pre-2026 orders vanish entirely, violating the requirement. Choice B uses a LEFT JOIN but places the date condition in the WHERE clause; after the join, unmatched customers have a null order date, and filtering order_date >= '2026-01-01' in WHERE drops those null rows, effectively converting the LEFT JOIN into an INNER JOIN. Choice C attempts to recover from B's mistake by also allowing null order IDs in the WHERE clause, but this workaround is fragile — it reintroduces rows only for customers with no orders at all, still discarding customers who have exclusively older orders. The study tip to carry forward: conditions in ON filter before joining; conditions in WHERE filter after. On SQL-heavy exams, always ask yourself whether a WHERE filter could accidentally eliminate the null rows your LEFT JOIN was designed to preserve.

Question 2

A campaign table has one row per campaign. Campaign A has spend of 100100 dollars, and campaign B has spend of 200200 dollars. A click table has three rows for campaign A and two rows for campaign B. An analyst INNER JOINs the tables by campaign and then calculates total spend and total click rows.

Which statement correctly describes the result and an appropriate correction?

  1. The join reports spend of 300300 dollars and 55 clicks; no correction is necessary because both keys match.
  2. The join reports spend of 700700 dollars and 55 clicks; aggregate clicks by campaign before joining to preserve spend grain. (correct answer)
  3. The join reports spend of 700700 dollars and 22 clicks; use a LEFT JOIN so each campaign contributes one click.
  4. The join reports spend of 500500 dollars and 55 clicks; remove campaigns having more than one click record.
Explanation: Whenever you JOIN tables at different levels of granularity, you need to ask: what grain does each table live at, and will the join multiply rows in a way that distorts aggregates? Here, the campaign table has one row per campaign (spend grain), while the click table has multiple rows per campaign (click grain). When you INNER JOIN them on campaign ID, each spend row gets repeated once for every matching click row. Campaign A's $100\$100 spend is duplicated across its 3 click rows, and Campaign B's $200\$200 spend is duplicated across its 2 click rows. Summing spend after the join gives you 100×3+200×2=300+400=$700100 \times 3 + 200 \times 2 = 300 + 400 = \$700, while the click count of 55 is correct (since clicks aren't duplicated—they're the many-side rows). The right fix is to pre-aggregate clicks by campaign before joining, so the join stays at the campaign grain and spend is never inflated. This makes B correct. A is wrong because it assumes no row multiplication occurs—it does, and the $300 figure ignores that spend rows are repeated. C gets the inflated spend right ($700) but then claims only 2 clicks are returned, which misunderstands how the join works; all 5 click rows survive an INNER JOIN. D invents a $500 figure with no logical basis and proposes deleting valid data rather than fixing the join logic. A useful rule of thumb: always aggregate the many side of a relationship down to the one side's grain before joining—this prevents fan-out inflation of any measure on the one side.

Question 3

An employee table contains four employees with region codes East, West, null, and East. A region table contains one row each with region codes East, West, and null. Standard SQL null-comparison rules apply.

What row counts result from an equality-based INNER JOIN and from an employee-to-region LEFT JOIN on region code?

  1. The INNER JOIN returns 44 rows, and the LEFT JOIN returns 44 rows because the null codes match.
  2. The INNER JOIN returns 33 rows, and the LEFT JOIN returns 44 rows because the employee null is unmatched. (correct answer)
  3. The INNER JOIN returns 22 rows, and the LEFT JOIN returns 33 rows because duplicate East values collapse.
  4. The INNER JOIN returns 33 rows, and the LEFT JOIN returns 55 rows because both null rows are retained separately.
Explanation: When working with SQL joins, the most important rule to remember is that NULL never equals anything — including another NULL. This is the foundation for understanding this question. With an INNER JOIN on region code, SQL matches only rows where the condition evaluates to TRUE. Your employee table has East, West, null, and East. The region table has East, West, and null. The two East employees each match the single East region row (22 matches), and the West employee matches the West region row (11 match). The null employee finds no match because null = null evaluates to UNKNOWN, not TRUE — so it's excluded. That gives you 2+1=32 + 1 = 3 rows, confirming B is correct. For the LEFT JOIN, every employee row is preserved regardless of whether a match exists. The three matched employees carry their region data, and the null-coded employee is retained with NULL-filled region columns — totaling 44 rows. This confirms the second half of answer B. A is wrong because it assumes null codes match each other in a join condition — they don't. C is wrong on two counts: duplicate East values don't "collapse" in a join (each employee row generates its own match), and the LEFT JOIN count of 3 would drop the unmatched null employee, which contradicts how LEFT JOINs work. D is wrong because it suggests both null rows (one from each table) are retained separately, inflating the count to 5 — but the region table's null row only appears when matched, which never happens here. A useful rule of thumb: treat NULL as "unknown" in any join condition. If you can't confirm a match is TRUE, the INNER JOIN drops it — but a LEFT JOIN always keeps the left-side row.

Question 4

A sales-summary table contains one row per store per month for January and February. A target table also contains one target row per store per month for those months. An analyst joins the tables using store ID only and then compares each monthly sales amount with its joined target.

Why are the comparisons unreliable, and what is the most direct correction?

  1. Each sales row matches both monthly targets; join on both store ID and month to align the records. (correct answer)
  2. February rows are excluded by the INNER JOIN; switch to a LEFT JOIN using store ID only.
  3. Each target row overwrites the earlier target; sort both tables by month before performing the join.
  4. Targets are aggregated across stores; join on month only and calculate a store-level average afterward.
Explanation: Whenever you see a question about joining tables, your first instinct should be to count how many rows the join will produce and ask whether each row represents exactly one logical pairing. Here, the sales table has two rows per store (January and February), and the target table also has two rows per store. When you join on store ID alone, each store's January sales row matches both that store's target rows (January and February), and the February sales row does the same. A store with two months of data produces four joined rows instead of two, meaning every sales figure gets compared to the wrong target at least once. The fix is straightforward: join on both store ID and month, so each sales record pairs with exactly one corresponding target. That's why A is correct. B is wrong because the problem isn't about excluding rows — an INNER JOIN works fine here since every store appears in both tables. Switching to a LEFT JOIN still uses only store ID and still produces the same inflated, misaligned matches. C is wrong because sorting rows before a join has no effect on which rows match which. SQL joins operate on key values, not row order; sorting cannot fix a missing join condition. D is wrong because the targets are already store-level (one row per store per month). Joining on month only would mismatch stores entirely, comparing one store's sales to a different store's target, which makes the data even less reliable, not more. The study tip: always verify that your join keys uniquely identify the grain (level of detail) you need. If your table has one row per store per month, your join must use both store and month as keys.

Question 5

A service company has three customers. Customer C1 has no orders, customer C2 has one order with no shipment, and customer C3 has one order with two shipment records. An analyst first LEFT JOINs Customers to Orders and then INNER JOINs the result to Shipments using order ID.

What does the final result contain?

  1. Four rows: one for C1, one for C2, and two for C3 because the first join preserves all customers.
  2. Three rows: one for each customer, with shipment fields null for C1 and C2.
  3. Two rows, both for C3, because the later INNER JOIN removes rows without matching shipments. (correct answer)
  4. One row for C3 because multiple shipment records collapse when joined through one order.
Explanation: When you chain multiple joins together, each join applies its own filtering logic to whatever result came before it — and that sequencing can dramatically change your final row count. Here's how to trace this query step by step. The LEFT JOIN between Customers and Orders produces three rows: one for C1 (with null order fields), one for C2 (with a real order ID), and one for C3 (with a real order ID). So far, all customers survive. But then the INNER JOIN to Shipments kicks in. An INNER JOIN only keeps rows where a match exists on both sides. C1's row has a null order ID, so it matches nothing in Shipments and gets dropped. C2's order has no shipment record, so it also gets dropped. C3's order matches two shipment records, producing two rows. The final result is two rows, both for C3 — confirming answer C. Answer A is tempting because it correctly describes the LEFT JOIN step, but it ignores what the subsequent INNER JOIN does to those preserved rows. Answer B imagines that all three customers survive with nulls, which would only be true if the second join were also a LEFT JOIN. Answer D misunderstands how one-to-many joins work — joining one order to two shipments expands the result to two rows, not collapses it to one. The key strategy here: always evaluate joins in sequence, and remember that an INNER JOIN anywhere in the chain can eliminate rows that an earlier outer join worked hard to preserve. Think of an INNER JOIN as a filter that respects no prior promises.

Question 6

Five transactions have amounts of 5050, 7575, 4040, 3030, and 2020 dollars. The first two reference store S1, the third references store S2, and the last two reference store S9. A store dimension classifies S1 as North and S2 as South but has no row for S9. Transactions are LEFT JOINed to the store dimension and revenue is grouped by region. The dashboard suppresses groups whose region is null.

Why does the dashboard display only 165165 dollars instead of the full 215215 dollars?

  1. The LEFT JOIN drops S9 transactions before aggregation because S9 has no matching dimension row.
  2. The S9 transactions are assigned to South during the join but excluded by the regional aggregation.
  3. The S1 transactions are deduplicated into one North record, reducing reported revenue by 5050 dollars.
  4. The S9 transactions form a null-region group totaling 5050 dollars, which the dashboard then hides. (correct answer)
Explanation: Whenever you see a question combining JOIN behavior with dashboard filtering, treat them as two separate stages — the join determines what data survives into the result set, and the dashboard layer applies its own display rules afterward. Here, a LEFT JOIN preserves all transactions regardless of whether a matching dimension row exists. The S9 transactions ($30\$30 and $20\$20, totaling $50\$50) do get included in the joined result — they simply receive a null value for region because S9 has no dimension row. After grouping by region, those transactions form their own null-region bucket worth $50\$50. The dashboard then suppresses any group where region is null, hiding that entire bucket. So you see $50+$75=$125\$50 + \$75 = \$125 for North and $40\$40 for South, totaling $165\$165 — not the full $215\$215. That confirms D is correct. A is the most tempting wrong answer, but it confuses a LEFT JOIN with an INNER JOIN. A LEFT JOIN never drops unmatched rows from the left (fact) table — it keeps them and fills dimension columns with null. B is incorrect because null region values are not reassigned to any existing region like South; they remain null throughout aggregation. C invents a deduplication step that doesn't exist anywhere in the scenario — S1 has two separate transactions that are both counted normally, contributing the correct $125\$125 to North. As a study habit, always trace data loss through two questions: did the JOIN drop it, or did a post-join filter hide it? These are different problems with different fixes, and exams love to test whether you can tell them apart.

Question 7

A prescriptive retention system must produce one action record for every eligible customer. The eligibility table contains customers E1, E2, and E3. A recommendation table contains two candidate actions for E1, no actions for E2 or E3, and one action for ineligible customer E4.

Which preparation best supports one decision per eligible customer while preserving the intended decision population?

  1. INNER JOIN eligibility to recommendations on customer ID, then select one action per remaining customer after a separate step to exclude E4 from the output.
  2. LEFT JOIN recommendations to eligibility on customer ID, then retain every row whose eligibility fields are not null to remove ineligible customers such as E4.
  3. Summarize recommendations to one selected action per customer, then LEFT JOIN from eligibility to that summary so every eligible customer appears exactly once and E4 is excluded. (correct answer)
  4. LEFT JOIN eligibility directly to recommendations on customer ID, then treat all resulting rows as separate customer decisions, accepting that some eligible customers may appear more than once.
Explanation: When designing a prescriptive system that must produce exactly one action per eligible customer, you need to think carefully about join direction, row multiplication, and population control. The anchor of your query should always be the table that defines who matters — in this case, the eligibility table. Option C nails this by handling the two challenges separately before combining them. First, it collapses the recommendation table to one action per customer, eliminating the duplicate-row problem that E1's two candidates would cause. Then it LEFT JOINs from eligibility to that summary, so E1, E2, and E3 all appear exactly once. Because E4 never appears in the eligibility table, it is structurally excluded — no extra filtering step required. Option A uses an INNER JOIN, which immediately drops E2 and E3 (they have no recommendations), violating the "one record per eligible customer" requirement. Tacking on a separate exclusion step for E4 doesn't fix the lost rows. Option B joins in the wrong direction — recommendations to eligibility — which privileges the recommendations table as the anchor. While the null-check removes E4, it doesn't guarantee one row per eligible customer; E1 still produces two rows since the many-candidate problem is never resolved. Option D acknowledges the flaw in its own description: joining eligibility to recommendations without pre-summarizing means E1 generates two decision rows, directly breaking the one-decision-per-customer rule. A reliable strategy here: whenever you need a guaranteed one-to-one output, pre-aggregate the many-side first, then join from your controlling table. This pattern prevents row multiplication and keeps your population clean without extra filtering.

Question 8

A customer table contains one row each for customer IDs 101101, 102102, and 103103. An order table contains two rows for customer 101101, one row for customer 102102, and two rows for customer 104104.

An analyst joins the tables on customer ID. Which result correctly compares an INNER JOIN with a customer-to-order LEFT JOIN?

  1. The INNER JOIN returns 33 rows; the LEFT JOIN returns 44 rows, including one unmatched row for customer 103103. (correct answer)
  2. The INNER JOIN returns 33 rows; the LEFT JOIN returns 55 rows, including both unmatched orders for customer 104104.
  3. The INNER JOIN returns 22 rows; the LEFT JOIN returns 33 rows, because each customer appears at most once.
  4. The INNER JOIN returns 55 rows; the LEFT JOIN returns 66 rows, including customer 103103 with null order values.
Explanation: When working with SQL joins, your first step should always be to map out exactly which rows exist in each table and which IDs overlap. Here, the customer table has IDs 101101, 102102, and 103103. The order table has two rows for 101101, one row for 102102, and two rows for 104104. An INNER JOIN returns only rows where the customer ID exists in both tables. Customer 101101 matches twice (two orders), and customer 102102 matches once — that's 33 rows total. Customers 103103 and 104104 are excluded because neither appears in both tables simultaneously. A LEFT JOIN (customer on the left, orders on the right) keeps all rows from the customer table, then attaches matching order rows. Customer 101101 contributes 22 rows, customer 102102 contributes 11 row, and customer 103103 contributes 11 row with nulls for order columns — totaling 44 rows. Customer 104104 is invisible here because it doesn't exist in the left (customer) table. This confirms answer A. Answer B is wrong because it claims the LEFT JOIN returns 55 rows by including customer 104104's orders — but those would only appear in a RIGHT JOIN or if the order table were on the left. Answer C incorrectly assumes each customer appears once regardless of how many orders they have, misunderstanding that joins can multiply rows. Answer D inverts the INNER JOIN count entirely; 55 rows would require matching all order rows, which is impossible without customer 104104 existing in the customer table. As a study tip, always sketch a quick two-column ID list before solving join questions — it prevents the common trap of confusing which table drives a LEFT JOIN's row count.

Question 9

An A/B test assignment table has exactly one row per assigned user. A purchase table has one row per purchase, so purchasers may appear multiple times and nonpurchasers do not appear. An analyst INNER JOINs assignments to purchases and computes the average of a field set to one on every joined purchase row, calling the result the conversion rate.

Which assessment and revised approach are most appropriate?

  1. The result is valid because an INNER JOIN restricts the metric to users who completed the conversion event, preserving the correct denominator for the rate calculation.
  2. The result understates conversion because nonpurchasers are assigned nulls by the INNER JOIN; replace null purchase fields with zeros and recompute the average across all joined rows.
  3. The result becomes one hundred percent and weights repeat purchasers; summarize to one purchase indicator per user, then LEFT JOIN to assignments. (correct answer)
  4. The result weights repeat purchasers but retains nonpurchasers as separate rows; apply DISTINCT on the purchase amount after joining to reduce each user to one row.
Explanation: Whenever you see a question about A/B test conversion rates, focus on two things: what's in the denominator and how many rows represent each user. Both must be correct for the metric to be valid. Here, the analyst INNER JOINs assignments (one row per user) to purchases (one row per purchase). This produces one row per purchase event — not per user. A user with three purchases contributes three rows, each with a field value of one. Averaging that field gives the proportion of purchase rows that are purchases, which is always 100%. More critically, users with zero purchases are dropped entirely by the INNER JOIN, so the denominator excludes nonpurchasers. The correct fix is to first collapse the purchases table to one row per user (a binary purchase indicator), then LEFT JOIN to assignments. This preserves every assigned user in the denominator and gives each user equal weight. That's exactly what C describes — making it the right answer. A is wrong because restricting to converted users destroys the denominator. You need nonpurchasers in the calculation; they represent the "did not convert" portion of the rate. B is wrong on two counts: an INNER JOIN doesn't produce nulls for nonpurchasers — it drops them entirely. Even if you addressed the null issue, the repeat-purchaser problem (multiple rows per user) would remain unsolved. D is wrong because DISTINCT on the purchase amount doesn't reliably reduce to one row per user — two identical purchase amounts from different users (or the same user) could still cause row duplication or undercounting. As a study tip: always sketch the grain of each table before joining. Ask yourself, "after this join, how many rows represent each user?" If the answer isn't one, your average will be biased.

Question 10

A procurement analyst LEFT JOINs Suppliers to Products using supplier ID. The ON clause also requires the product status to equal Active. The analyst then keeps only rows where the joined product ID is null.

Which suppliers are returned by this query?

  1. Only suppliers that have no product records of any status in the product table.
  2. Suppliers with no active products, including suppliers that have only inactive products. (correct answer)
  3. Only suppliers that have at least one inactive product and at least one active product.
  4. Suppliers with active products whose product IDs were converted to null by the LEFT JOIN.
Explanation: When you combine a LEFT JOIN with a filter condition in the ON clause (rather than the WHERE clause), the behavior is subtly different from what most people expect — and that distinction is exactly what this question tests. Here's the logic: a LEFT JOIN with ON supplier_id = supplier_id AND product_status = 'Active' attempts to match each supplier to only its active products. If a supplier has no active products — whether because it has zero products at all, or because all its products are inactive — the join finds no matching row, so SQL preserves the supplier row but fills all product columns with NULL. When you then filter WHERE product_id IS NULL, you're keeping only those suppliers where the join found no active product match. This makes B correct: the query returns any supplier lacking active products, which includes both suppliers with zero products and suppliers whose products are all inactive. A is too narrow. It assumes only completely product-less suppliers appear, ignoring that a supplier with inactive-only products also produces a NULL product_id after the join fails to find an active match. C is wrong because it describes a supplier that does have active products — those suppliers would successfully join and return a non-null product_id, so they'd be excluded by the WHERE product_id IS NULL filter. D confuses how LEFT JOINs work. Active products that successfully match are not converted to NULL; NULLs only appear when no match is found at all. Study tip: Always ask yourself — is the filter in the ON clause or the WHERE clause? Conditions in ON shape which rows match; conditions in WHERE filter after the join. This distinction is a classic SQL trap on analytics exams.