SQL Quiz: Avoiding Join Mistakes
10 questions · exam conditions
0:00
Avoiding Join MistakesQuestion 1 of 10

A report must list every customer and the number of that customer's shipped orders, including customers with no shipped orders. The query uses customers c LEFT JOIN orders o ON c.customer_id = o.customer_id, followed by WHERE o.status = 'SHIPPED'.

Which revision avoids unintentionally discarding customers while also producing a zero count for those without shipped orders?

Keep the status condition in WHERE, but replace the left join with a full outer join.
Keep the status condition in WHERE and calculate COUNT(*) after grouping the rows by customer.
Move the status condition into the ON clause and calculate COUNT(*) for each customer.
Move o.status = 'SHIPPED' into the ON clause and calculate COUNT(o.order_id) for each customer.
← Back to quizzes

SQL Quiz

SQL Quiz: Avoiding Join Mistakes

Practice Avoiding Join Mistakes 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 Avoiding Join Mistakes, 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

A report must list every customer and the number of that customer's shipped orders, including customers with no shipped orders. The query uses customers c LEFT JOIN orders o ON c.customer_id = o.customer_id, followed by WHERE o.status = 'SHIPPED'.

Which revision avoids unintentionally discarding customers while also producing a zero count for those without shipped orders?

  1. Keep the status condition in WHERE, but replace the left join with a full outer join.
  2. Keep the status condition in WHERE and calculate COUNT(*) after grouping the rows by customer.
  3. Move the status condition into the ON clause and calculate COUNT(*) for each customer.
  4. Move o.status = 'SHIPPED' into the ON clause and calculate COUNT(o.order_id) for each customer. (correct answer)
Explanation: When working with LEFT JOIN queries, the placement of filter conditions is critical. A LEFT JOIN preserves all rows from the left table (customers), filling right-table columns with NULL when no match exists. However, the moment you add a WHERE clause that filters on the right table (like o.status = 'SHIPPED'), you silently convert it into an inner join — rows with NULL values fail the condition and get discarded. The correct fix is D: move o.status = 'SHIPPED' into the ON clause and use COUNT(o.order_id). Placing the condition in ON means it applies during the join, not after. Customers without shipped orders still appear in results, but their order_id column is NULL. Using COUNT(o.order_id) rather than COUNT(*) is essential — COUNT ignores NULL values, so those customers correctly receive a count of 0 instead of 1. A is wrong because a FULL OUTER JOIN with a WHERE filter on o.status still discards non-matching rows — the same trap, just with a different join type. B fails for the same fundamental reason as the original query: filtering WHERE o.status = 'SHIPPED' after the join eliminates customers with no shipped orders before grouping even occurs, so no grouping logic can recover them. C correctly moves the condition to ON, but using COUNT(*) would count the NULL placeholder row as 1 for customers with no shipped orders, producing inaccurate results. A reliable rule of thumb: when you need a left join with a filter on the right table, put the filter in ON and use COUNT(right_table_column) — never COUNT(*).

Question 2

An imported contact file contains email addresses but no internal customer identifiers. A report joins it to customers on normalized email address. The customers table permits several customer records to share an email address, and the import can also contain repeated email addresses.

Which approach best prevents accidental many-to-many amplification while retaining a defensible match between the two sources?

  1. Group only the final result by email because all rows sharing an email represent the same business entity.
  2. Join the original sources on email and use DISTINCT on all selected customer and import columns afterward.
  3. Use a left join from the import because preserving its rows prevents multiple customer matches per email.
  4. Define explicit matching and deduplication rules on both sources, then join datasets having at most one chosen row per email. (correct answer)
Explanation: Whenever you see a question about joining two sources that may each contain duplicate keys, your first instinct should be to think about many-to-many joins — the silent multiplier that inflates row counts in ways that are hard to detect and easy to misinterpret. If source A has 3 rows for email X and source B has 4 rows for email X, a naive join produces 12 rows, none of which is obviously wrong at a glance. The only reliable defense is answer D: deduplicate both sides before joining, using explicit, documented business rules to select one canonical row per email from each source. That way, each email maps to exactly one row on each side, and the join produces at most one match — defensible, auditable, and free from amplification. Answer A is tempting but dangerously late. Grouping after a join doesn't undo the multiplication — it just hides it. You may silently aggregate incorrect inflated data and never notice the distortion. Answer B shares the same flaw. Applying DISTINCT after the join collapses some duplicate output rows, but only those that are completely identical across all selected columns. Any column that varies between duplicates (like a customer ID or import timestamp) will let phantom rows survive, giving you a false sense of cleanliness. Answer C misunderstands what LEFT JOIN does. Preserving all import rows doesn't prevent multiple customer rows from matching a single import email — it just ensures unmatched import rows aren't dropped. The many-to-many problem remains entirely intact. The key study tip: deduplication must happen before the join, not after. Think of it as narrowing each source to a one-row-per-key dataset first, then joining clean inputs together.

Question 3

A self-join is used to list every pair of different employees who work in the same department. The condition includes e1.department_id = e2.department_id and e1.employee_id <> e2.employee_id. The result contains both (Ana, Bo) and (Bo, Ana).

Which predicate change removes mirrored duplicates while retaining every unordered employee pair exactly once?

  1. Keep the inequality and add e1.employee_id IS NOT NULL to exclude duplicate employee combinations.
  2. Replace the inequality with e1.employee_id = e2.employee_id, keeping the department equality condition.
  3. Replace the inequality with e1.employee_id < e2.employee_id, keeping the department equality condition. (correct answer)
  4. Keep the inequality and change the self-join to a left join from the first employee alias.
Explanation: When working with self-joins, a key challenge is controlling which row combinations appear in your result. Joining a table to itself on department equality produces every ordered pair — meaning both (Ana, Bo) and (Bo, Ana) show up as separate rows. Your goal is to keep only one representative from each mirrored pair. The cleanest solution is C: replacing e1.employee_id <> e2.employee_id with e1.employee_id < e2.employee_id. This works because the less-than operator enforces a strict ordering between the two IDs. For any pair (Ana=1, Bo=2), only the row where the smaller ID appears on the left survives — the mirror image (Bo=2, Ana=1) is automatically filtered out because 2 < 1 is false. You still get every unique pairing exactly once, and same-employee rows are implicitly excluded since no ID is less than itself. A is wrong because adding e1.employee_id IS NOT NULL only filters out null employee IDs — it does nothing to eliminate mirrored duplicates, which exist regardless of nullability. B is wrong because replacing the inequality with e1.employee_id = e2.employee_id would return only rows where both aliases point to the same employee — the exact opposite of what you want. D is wrong because switching to a LEFT JOIN changes which rows are included (adding NULLs for unmatched rows), but it doesn't eliminate mirrored duplicates at all. A practical tip: whenever you need unordered pairs in a self-join, reach for < instead of <> — it's a reliable pattern for deduplication that you'll encounter frequently in SQL interview and exam questions.

Question 4

A query joins invoices to invoice_tags and then calculates SUM(DISTINCT invoices.amount) by account. Repeated tag matches no longer multiply most invoice amounts, but an account containing two different invoices for the same amount is undercounted.

Which revision corrects the logical flaw without assuming invoice amounts are unique?

  1. Add the invoice identifier to the final GROUP BY clause so each invoice amount is counted separately per account.
  2. Pre-aggregate to one row per invoice using the invoice identifier as the grain, then sum the resulting amounts by account. (correct answer)
  3. Replace the tag join with a LEFT JOIN so identical invoice amounts are preserved by the aggregate.
  4. Round each invoice amount to a consistent precision before applying SUM(DISTINCT amount) by account.
Explanation: When a join inflates rows — like matching one invoice to multiple tags — aggregate functions see duplicates. SUM(DISTINCT amount) tries to fix this by deduplicating amounts, but it deduplicates by value, not by invoice identity. If two legitimate invoices carry the same dollar amount, DISTINCT collapses them into one, undercounting your total. The real fix is to remove duplicates at the invoice level before aggregating. Option B is correct because it pre-aggregates to one row per invoice first (using the invoice identifier as the grain, typically with a subquery or CTE like SELECT invoice_id, account_id, MAX(amount) FROM ... GROUP BY invoice_id, account_id), then sums those deduplicated rows by account. This approach deduplicates by identity, not by value, so two invoices with the same amount are both counted. Option A is wrong because adding invoice_id to the outer GROUP BY breaks the account-level summary — you'd get one row per invoice rather than one row per account, defeating the purpose of the aggregation. Option C is wrong because switching to LEFT JOIN doesn't eliminate the row multiplication problem; a tag join still creates duplicates for matched invoices, and unmatched invoices simply get a NULL tag row. The core duplication flaw remains. Option D is wrong because rounding amounts doesn't solve the distinctness problem — it actually makes it worse by potentially collapsing even more distinct amounts into the same value before DISTINCT filters them. As a study habit, whenever you see SUM(DISTINCT col), ask yourself: "Am I deduplicating by value or by identity?" If your grain isn't naturally unique, pre-aggregate first.

Question 5

Each sale belongs to one product. A product may belong to several categories through product_category. A query joins sales through the bridge and reports revenue by category. Individual category totals are correct, but adding all category totals exceeds total company revenue.

Assuming the bridge contains no duplicate product-category pairs, which interpretation and remedy are most appropriate?

  1. Multi-category products intentionally contribute to multiple groups; allocate each sale across categories if totals must reconcile. (correct answer)
  2. The bridge necessarily created erroneous duplicate rows; apply DISTINCT to sale amounts before calculating category totals.
  3. The category grouping is too coarse; add product to the grouping so summed category totals reconcile automatically.
  4. The join should use only one category per product; choose an arbitrary category during query execution to remove duplicates.
Explanation: Whenever a query joins through a many-to-many bridge table and category subtotals exceed the grand total, your first instinct should be to ask: is the data intentionally being counted multiple times? This is a fan-out problem — a classic consequence of many-to-many relationships in SQL. Here's the logic: if a product belongs to three categories, every sale of that product appears three times in the joined result set — once per category row. Each category's total is individually correct (it only sums its own rows), but when you add them up, that product's revenue is triple-counted. This isn't a data error or a query bug — it's the expected, intended behavior of reporting across overlapping groups. Answer A correctly identifies this: multi-category products legitimately contribute revenue to each of their categories, and if you need totals to reconcile to company revenue, you must allocate or split each sale's amount proportionally across its categories rather than summing fully in each. Answer B is wrong because the problem statement explicitly says there are no duplicate product-category pairs in the bridge — so DISTINCT on sale amounts wouldn't help and misdiagnoses the issue. Answer C misunderstands the problem; adding product to the GROUP BY would give you finer granularity but the same double-counting when you roll up to category level — it doesn't make totals reconcile. Answer D is wrong because arbitrarily picking one category per product destroys valid business information and produces misleading category reports. A useful rule of thumb: when subtotals exceed a known grand total after a join, always check whether your join is traversing a many-to-many relationship — that's almost always the culprit.

Question 6

A developer expects an inner join from order_items to products to return exactly one result row for every item: no item should disappear, and no item should be duplicated. The join is order_items.product_id = products.product_id.

Which database conditions are sufficient to guarantee that expectation for all valid stored data?

  1. order_items.product_id has an index, and products.product_id is frequently used in equality joins.
  2. order_items.product_id is non-null and references a unique, non-null products.product_id key. (correct answer)
  3. products.product_id is non-null, while duplicate product identifiers are removed from the query with DISTINCT.
  4. order_items.product_id may be null, but every non-null value has at least one matching product row.
Explanation: When an inner join must return exactly one matching row per driving-row — no disappearing rows, no duplicates — you need to think about two separate guarantees working together: a match guarantee and a uniqueness guarantee. For the join order_items.product_id = products.product_id, every item row must find a match (no disappearing rows), and each match must be unique (no duplicates). Answer B delivers both. If order_items.product_id is non-null, null can never silently drop the row (nulls never satisfy equality conditions in SQL). If products.product_id is a unique, non-null key, then each order_items row can match at most one product row — preventing fan-out duplication. Together, these two properties ensure the one-to-one correspondence the developer expects. A is wrong because indexes are a performance tool, not a data integrity tool. An indexed column can still contain nulls or duplicates; indexing changes query speed, not row counts. C is wrong for two reasons. DISTINCT collapses duplicates after the fact — it doesn't guarantee a structural one-to-one match, and it can actually hide problems by silently discarding rows you might want to see. Also, only making products.product_id non-null ignores whether order_items.product_id is null or missing a match. D is wrong because allowing null in order_items.product_id means those rows produce no match in an inner join and vanish — violating the "no disappearing rows" requirement. Study tip: Whenever a question asks about join correctness, mentally check two constraints: referential integrity (every FK value exists in the PK table) and uniqueness (the PK side has no duplicates). Both must hold for a clean one-to-one inner join.

Question 7

A customer-history table stores several versions of each customer, with valid_from and valid_to dates. An order report joins orders to history using only customer_id. Consequently, each order can match several historical customer versions.

Which join design most directly assigns each order to the correct historical version without duplicate amplification?

  1. Join on customer identifier, then use DISTINCT over the order columns while omitting history attributes.
  2. Join on customer identifier and select the history row having the greatest valid_from date overall.
  3. Join on customer identifier and require the order date to fall within one validated, nonoverlapping validity interval. (correct answer)
  4. Join on customer identifier and require the history row's valid_to value to be non-null.
Explanation: Whenever you join a slowly changing dimension table (like customer history) to a fact table (like orders), your core challenge is row fanout — each order matching multiple historical versions and inflating your result set. The fix is always a temporal boundary check that maps each order to exactly one valid history row. Option C solves this precisely: by requiring the order date to fall within a validated, nonoverlapping validity interval (order_date >= valid_from AND order_date < valid_to), you guarantee at most one history row matches per order. When the intervals are properly maintained as nonoverlapping, this is a clean one-to-one match with no duplicates and no lost history attributes. Option A is a workaround, not a solution. Using DISTINCT on order columns simply hides the duplicates rather than preventing them — you're still joining incorrectly and then discarding data. Worse, you've already thrown away the history columns you may need. Option B selects the history row with the greatest valid_from overall — meaning the most recent version of the customer regardless of when the order was placed. An order from three years ago would be incorrectly attributed to today's customer profile, which corrupts historical reporting. Option D is nearly meaningless as a filter. Requiring valid_to to be non-null typically just excludes the current open-ended record (often stored as NULL to mean "still active"), which paradoxically drops the most recent version rather than resolving the fanout problem. Your study tip: whenever you see a history or slowly changing dimension table, immediately think "I need a date-range join" — that's almost always the correct temporal design pattern.

Question 8

A query contains:

FROM customers c, orders o

The intended relationship is c.customer_id = o.customer_id, but no predicate connecting the tables appears in the WHERE clause. The query selects only customer region and order month, then uses DISTINCT.

Why might this mistake escape a quick review of the result while still producing an invalid and potentially expensive query?

  1. DISTINCT can collapse repeated projected values, masking some Cartesian combinations without correcting the missing relationship. (correct answer)
  2. DISTINCT causes the optimizer to infer the omitted foreign-key equality and apply it before producing rows.
  3. The comma syntax automatically uses declared foreign keys, but only after duplicate projected values have been removed.
  4. The missing predicate affects execution cost only; selecting columns from both tables guarantees logically valid pairings.
Explanation: Whenever you see a question about missing join predicates, think about what the result set looks like versus what it means. A Cartesian product pairs every row from one table with every row from the other — for 1,000 customers and 10,000 orders, that's 10 million rows. The question tests whether you understand how DISTINCT interacts with that explosion and why it can hide the problem. DISTINCT eliminates duplicate rows in the projected output — in this case, just region and order month. Because there are only a handful of distinct regions and twelve possible months, the output might collapse to a small, tidy-looking grid of combinations. The problem is that those combinations aren't meaningfully paired; they simply reflect every region crossed with every month that exists anywhere in the data. The query looks reasonable, but the underlying computation scanned millions of bogus row pairings. A is correct because it precisely describes this masking effect: DISTINCT removes surface-level repetition without fixing the logical error or reducing the Cartesian join work done by the engine. B is wrong because the optimizer has no mechanism to infer a missing predicate from DISTINCT — the optimizer can reorder joins and choose indexes, but it cannot manufacture filter conditions that don't exist in your SQL. C is wrong because the comma syntax in FROM carries no implicit foreign-key awareness; it simply produces a cross join regardless of any declared constraints. D is wrong because the problem is both logical and costly — selecting columns from both tables does nothing to guarantee valid pairings; it just projects two columns from each meaningless combination. As a study habit, always verify that every table in your FROM clause has a corresponding join condition. If you use DISTINCT and the row count seems suspiciously low, treat that as a warning sign, not a sign of correctness.

Question 9

In a multi-tenant system, orders has the composite primary key (tenant_id, order_id). The shipments table uses the same two columns as a foreign key. An analyst joins the tables using only order_id. Order identifiers are unique within a tenant but can be reused by other tenants.

The query currently returns correct-looking results in a test database containing only one tenant. Which revision directly addresses the latent join mistake?

  1. Add tenant_id to the join condition so both columns of the relationship are matched. (correct answer)
  2. Add DISTINCT to the select list so cross-tenant shipment matches are removed automatically.
  3. Change the join to a LEFT JOIN so each order remains present regardless of shipment matches.
  4. Group the joined rows by order_id so shipments belonging to other tenants collapse together.
Explanation: When working with composite primary keys in multi-tenant systems, your join conditions must mirror the full relationship definition — not just the convenient portion. If a primary key spans two columns, a foreign key referencing it also spans two columns, and your join must match on all of them. Here, order_id alone is ambiguous because the same value can legally appear across multiple tenants. In a single-tenant test database, this works by accident — there are no duplicate order_id values across tenants yet. But in production, joining only on order_id would match an order from Tenant A with shipments belonging to Tenant B, silently corrupting your results. Answer A fixes this directly by adding tenant_id to the join condition, ensuring both columns of the composite relationship are evaluated together and rows are matched only within the correct tenant context. Answer B is wrong because DISTINCT removes duplicate rows in the output, not incorrect matches. Cross-tenant rows that look distinct would still appear — you'd just suppress legitimate duplicates alongside them. Answer C is a red herring: switching to a LEFT JOIN changes nullability behavior for unmatched orders but does nothing to prevent wrong tenants from matching in the first place. Answer D is similarly flawed — GROUP BY order_id would collapse rows together, hiding the problem rather than fixing it, and likely producing aggregated nonsense across tenants. As a study tip: whenever you see a composite foreign key, always verify your join conditions cover every column in that key. A join on a subset of a composite key is a correctness bug that test data often masks.

Question 10

An orders table has one row per order. An order_lines table has multiple rows per order, and a payments table can also have multiple rows per order. A report joins all three tables and groups by customer to calculate merchandise sold and payments received. Orders with multiple lines and multiple payments show inflated totals.

Which change most reliably prevents the duplicate amplification while preserving both measures?

  1. Aggregate lines and payments separately by order, then join both results to orders before grouping by customer. (correct answer)
  2. Join all detail rows first, then apply SUM(DISTINCT amount) separately to line amounts and payment amounts.
  3. Change both detail joins to LEFT JOIN, then group the resulting rows by customer and order identifier.
  4. Select distinct joined rows before grouping, retaining the line and payment identifiers along with both amount columns.
Explanation: When you join a one-to-many table twice — once for order lines and once for payments — you create a Cartesian product between the two detail tables. An order with 3 lines and 2 payments produces 6 joined rows, causing every amount to be counted multiple times. The core fix is to prevent those detail tables from ever "seeing" each other's rows before aggregation. That's exactly what A does: it pre-aggregates each detail table independently at the order level (e.g., SELECT order_id, SUM(amount) FROM order_lines GROUP BY order_id), then joins those single-row-per-order summaries to orders. By the time you group by customer, every order contributes exactly one line total and one payment total — no amplification possible. B is tempting because SUM(DISTINCT ...) sounds like it removes duplicates, but it deduplicates by value, not by row identity. If two line items legitimately have the same dollar amount, they'll be collapsed into one — silently undercounting your revenue. It's unreliable, not just slow. C switches to LEFT JOIN, which controls which orders appear but does nothing to reduce the number of detail rows that cross-join with each other. You'll still get the same Cartesian explosion; you'll just also include orders with no lines or payments. D selecting distinct joined rows only helps if the duplicates are perfectly identical — but because line and payment identifiers differ across the amplified rows, DISTINCT won't collapse them at all. Study tip: Whenever you're aggregating from two independent child tables, pre-aggregate each one to the parent's grain first, then join. This "aggregate-before-joining" pattern is the standard defense against fan-out multiplication in SQL.