SQL Quiz: Multi Key Joins
10 questions · exam conditions
0:00
Multi Key JoinsQuestion 1 of 10

An orders row is identified by (tenant_id, order_id). The order_items table contains the same key columns and an active flag. A report must show every order, together with its active items when any exist. Orders having no active items must still appear.

Which query implements the required relationship while preserving orders that have no active items?

SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id AND i.active = 1;
SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id WHERE i.active = 1;
SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.order_id = i.order_id AND i.active = 1;
SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id WHERE i.active = 1 OR i.item_id IS NULL;
← Back to quizzes

SQL Quiz

SQL Quiz: Multi Key Joins

Practice Multi Key Joins 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 Multi Key Joins, 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 row is identified by (tenant_id, order_id). The order_items table contains the same key columns and an active flag. A report must show every order, together with its active items when any exist. Orders having no active items must still appear.

Which query implements the required relationship while preserving orders that have no active items?

  1. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id AND i.active = 1; (correct answer)
  2. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id WHERE i.active = 1;
  3. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.order_id = i.order_id AND i.active = 1;
  4. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o LEFT JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id WHERE i.active = 1 OR i.item_id IS NULL;
Explanation: When working with LEFT JOIN, the most critical concept to internalize is where filtering happens. A LEFT JOIN guarantees that every row from the left table appears in the result — but that guarantee can be silently broken depending on where you place your filter conditions. Answer A is correct because it places the active = 1 filter directly in the ON clause. This means the join condition says: "match rows where the keys align and the item is active." When no active items exist for an order, the join simply finds no match, and SQL returns the order row with NULL values for all i.* columns — exactly the behavior the report requires. Answer B is the most dangerous trap. It moves active = 1 into a WHERE clause. After the LEFT JOIN produces rows with NULL for inactive or missing items, the WHERE filter discards any row where i.active is not 1 — including those NULL rows. This effectively converts your LEFT JOIN into an INNER JOIN, silently dropping orders with no active items. Answer C joins only on order_id, omitting tenant_id from the condition. In a multi-tenant database where order_id alone isn't unique, this creates a cross-join-like explosion, matching orders across different tenants incorrectly. Answer D attempts to recover the broken logic from B by adding OR i.item_id IS NULL, which does restore unmatched orders. However, it still filters out orders that have items but none are active, making it logically incomplete. Study tip: Whenever you see a LEFT JOIN with a filter, ask yourself: "Is this condition in ON or WHERE?" Conditions in ON restrict which rows join; conditions in WHERE restrict which joined rows survive — a crucial difference.

Question 2

The orders table has one row for (tenant_id, order_id) = ('T1', 7) and one for ('T2', 7). The order_items table has two rows for each of those composite keys. Order numbers are unique only within a tenant.

A report currently joins the tables using only order_id and returns eight rows instead of four. Which rewrite returns each item with its owning order without creating cross-tenant matches or ambiguous output columns?

  1. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o JOIN order_items i ON o.order_id = i.order_id AND o.tenant_id = i.tenant_id; (correct answer)
  2. SELECT tenant_id, order_id, i.item_id FROM orders o JOIN order_items i ON o.order_id = i.order_id AND o.tenant_id = i.tenant_id;
  3. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o JOIN order_items i ON o.order_id = i.order_id OR o.tenant_id = i.tenant_id;
  4. SELECT o.tenant_id, o.order_id, i.item_id FROM orders o JOIN order_items i ON o.order_id = i.order_id AND o.tenant_id <> i.tenant_id;
Explanation: When joining tables that share a composite primary key — meaning uniqueness depends on multiple columns together — your JOIN condition must include all parts of that key. If you join on only one column, rows from different "namespaces" (here, different tenants) incorrectly match each other, multiplying your result set. That's exactly the bug in the original query: joining solely on order_id causes T1's order 7 and T2's order 7 to match each other's items, producing 2×2×2 = eight rows instead of four. The fix is to add AND o.tenant_id = i.tenant_id to the ON clause, restricting each order to only its own tenant's items. A is correct because it joins on the full composite key (order_id AND tenant_id) and qualifies every selected column with a table alias, eliminating both the cross-tenant fan-out and any ambiguous column references. You get exactly four rows — two items per tenant order. B is wrong despite having the correct JOIN logic. Writing tenant_id and order_id without a table alias is ambiguous — both tables have those columns, and many SQL engines will raise an error or return unpredictable results. C is wrong because using OR instead of AND drastically loosens the join condition. A row matches if either the order ID or the tenant ID is equal — creating even more cross-tenant matches than the original broken query. D is wrong because o.tenant_id <> i.tenant_id deliberately excludes same-tenant matches, meaning you'd only return cross-tenant pairings — the exact opposite of what's needed. As a rule of thumb: whenever a table's primary key is composite, every JOIN involving that table should reference the entire composite key in the ON clause.

Question 3

Both orders and order_items contain tenant_id, order_id, and status. The first two columns form the relationship key, while each table's status has a different meaning.

Which query correctly joins the composite key and returns both status values without an ambiguous column reference?

  1. SELECT tenant_id, order_id, o.status AS order_status, i.status AS item_status FROM orders o JOIN order_items i USING (tenant_id, order_id); (correct answer)
  2. SELECT tenant_id, order_id, status AS order_status, status AS item_status FROM orders o JOIN order_items i USING (tenant_id, order_id);
  3. SELECT tenant_id, order_id, o.status AS order_status, i.status AS item_status FROM orders o JOIN order_items i ON o.tenant_id = i.tenant_id AND o.order_id = i.order_id;
  4. SELECT tenant_id, order_id, o.status AS order_status, i.status AS item_status FROM orders o JOIN order_items i USING (tenant_id);
Explanation: When joining tables that share column names, you need to decide between two JOIN syntaxes — USING and ON — and understand how each handles column references in the SELECT clause. With USING (col1, col2), SQL merges the listed columns into a single output column, so you reference them without a table alias (just tenant_id, not o.tenant_id). Crucially, columns not listed in USING — like status — still belong to their respective tables, so you can prefix them with aliases (o.status, i.status). Answer A does exactly this: it joins on the composite key (tenant_id, order_id) using USING, references both shared columns cleanly, and retrieves both status values with unambiguous table-prefixed aliases. That's the correct approach. Answer B is a trap: it uses the correct USING syntax but then writes status twice without a table prefix. Since both tables have status, this creates an ambiguous column reference — the database doesn't know which table's status to use, so it throws an error. Answer C would actually work logically, but it uses ON instead of USING for the composite key. More importantly, the question specifically asks which query "correctly joins the composite key" in the context where USING is the cleaner, intended tool — and answer A satisfies all conditions more precisely as written. Answer D uses USING (tenant_id) alone, which is an incomplete composite key. Joining only on tenant_id could match rows across different orders, producing incorrect results. Study tip: Remember that USING merges shared columns (no alias prefix needed), while non-listed columns still require table prefixes to avoid ambiguity — this distinction appears frequently in SQL join questions.

Question 4

Two staging tables are related by (sku, warehouse_id). In this data model, a null warehouse_id represents a shared warehouse and must match another null warehouse_id. The database supports IS NOT DISTINCT FROM.

Which join predicate implements that rule without treating an arbitrary non-null warehouse identifier as equivalent to null?

  1. ON s.sku = t.sku AND s.warehouse_id IS NOT DISTINCT FROM t.warehouse_id (correct answer)
  2. ON s.sku = t.sku AND s.warehouse_id = t.warehouse_id
  3. ON s.sku = t.sku AND COALESCE(s.warehouse_id, -1) = COALESCE(t.warehouse_id, -1)
  4. ON s.sku = t.sku AND (s.warehouse_id = t.warehouse_id OR s.warehouse_id IS NULL OR t.warehouse_id IS NULL)
Explanation: Whenever you see a join condition involving nullable columns, ask yourself: "How does SQL handle NULL comparisons, and does my predicate match the business rule exactly?" SQL's standard equality operator treats NULL as unknown — meaning NULL = NULL evaluates to UNKNOWN, not TRUE. So any join predicate using = on a nullable column will silently drop rows where both sides are NULL. That's the core trap here. Answer A, IS NOT DISTINCT FROM, is the correct choice because it implements "null-safe equality" — it returns TRUE when both sides are NULL, TRUE when both values are equal non-nulls, and FALSE otherwise. This maps perfectly to the business rule: two null warehouse_id values represent the same shared warehouse and should match. Answer B uses standard =, which fails when both warehouse_id values are NULL because NULL = NULL is UNKNOWN, causing those rows to be excluded from the join entirely — violating the rule that nulls must match each other. Answer C substitutes a sentinel value (-1) for NULL via COALESCE. This is a classic anti-pattern: if any legitimate warehouse ever has ID -1, you've created a false equivalence between real data and the null-substitution. It "works" until it catastrophically doesn't. Answer D is dangerously overbroad. The condition OR s.warehouse_id IS NULL OR t.warehouse_id IS NULL means any row with a null on either side matches, regardless of the other column — joining unrelated records together. Study tip: Whenever a column is nullable and must participate in a join or equality check, reach for IS NOT DISTINCT FROM instead of =. It's the null-safe equality operator and eliminates an entire class of subtle bugs.

Question 5

A query uses accounts NATURAL JOIN account_events. Initially, the only identically named columns are tenant_id and account_id, which together define the relationship. Later, both tables gain an unrelated column named status, causing the query's results to change.

Which rewrite preserves the intended two-key relationship across that schema change and keeps the selected status values unambiguous?

  1. SELECT a.status AS account_status, e.status AS event_status FROM accounts a JOIN account_events e USING (tenant_id, account_id, status);
  2. SELECT a.status AS account_status, e.status AS event_status FROM accounts a JOIN account_events e ON a.tenant_id = e.tenant_id AND a.account_id = e.account_id; (correct answer)
  3. SELECT a.status AS account_status, e.status AS event_status FROM accounts a JOIN account_events e ON a.account_id = e.account_id;
  4. SELECT a.status AS account_status, e.status AS event_status FROM accounts a NATURAL JOIN account_events e;
Explanation: When working with JOIN strategies, the key question is: how tightly coupled is your join condition to the schema? NATURAL JOIN automatically joins on all identically named columns — which feels convenient but creates a hidden dependency on column names. The moment a new shared column appears (like status here), the join silently adds that column to the condition, filtering rows you didn't intend to filter and breaking your query without any error message. Option B is correct because it uses an explicit ON clause that pins the join to exactly tenant_id and account_id — nothing more, nothing less. No matter what columns are added to either table later, this join condition stays stable. Selecting a.status and e.status with aliases then unambiguously retrieves both status values from their respective tables. Option A looks clever but is actually wrong: adding status to the USING clause means you're joining on it, which forces both tables' status values to match — exactly the unintended filtering behavior you're trying to avoid. Option C only joins on account_id, dropping tenant_id from the condition entirely. This breaks the intended two-key relationship and could return incorrect cross-tenant matches. Option D simply restores the original NATURAL JOIN, which is the root of the problem — it will again silently include status as a join key once that column exists in both tables. The study tip here: never use NATURAL JOIN in production-quality SQL. Always use explicit ON or USING clauses so your join conditions are visible, intentional, and immune to future schema changes.

Question 6

The tables have these keys: document(tenant_id, document_id), revision(tenant_id, document_id, revision_no), and approval(tenant_id, document_id, revision_no, approver). The query has already joined document d to revision r on tenant_id and document_id. It must next join each revision to its approvals.

Which second join is both unambiguous and complete for the stated relationship?

  1. JOIN approval a ON r.tenant_id = a.tenant_id AND r.document_id = a.document_id
  2. JOIN approval a ON tenant_id = a.tenant_id AND document_id = a.document_id AND revision_no = a.revision_no
  3. JOIN approval a ON r.tenant_id = a.tenant_id AND r.document_id = a.document_id AND r.revision_no = a.revision_no (correct answer)
  4. JOIN approval a ON d.tenant_id = a.tenant_id AND d.document_id = a.document_id AND d.revision_no = a.revision_no
Explanation: When joining tables that share column names across multiple aliases, two things must be true: every column in the composite key must be included, and every column reference must be unambiguous (tied to a specific table alias). The approval table's primary key is (tenant_id, document_id, revision_no), meaning all three columns are required to uniquely identify a row. When joining revision r to approval a, you need to match on all three of those columns using the r alias, since revision is the table you're linking from. C does exactly this — r.tenant_id = a.tenant_id AND r.document_id = a.document_id AND r.revision_no = a.revision_no — fully qualifying every column and including all three key parts. This is your correct answer. A fails because it omits revision_no. Without it, every approval across all revisions of a document gets joined to every revision of that document, producing a Cartesian-style explosion of rows. The relationship is incomplete. B includes all three columns but uses unqualified names (tenant_id, document_id, revision_no). Since both r and d have tenant_id and document_id, the database engine cannot determine which table the bare column names refer to, causing an ambiguity error at runtime. D uses the d (document) alias for revision_no, but document has no revision_no column — that column belongs to revision. This would produce an error or a logically wrong reference. Study tip: Whenever multiple joined tables share column names, always prefix every column with its table alias. Make it a habit to count the columns in a composite key and verify your ON clause matches them all.

Question 7

The customers table stores tenant_id, billing_address_id, and shipping_address_id. The addresses table is keyed by (tenant_id, address_id) and contains city. Address identifiers may repeat across tenants. A report must display both the billing city and shipping city.

Which query correctly joins the address table twice and avoids ambiguous column references?

  1. SELECT c.tenant_id, b.city AS billing_city, s.city AS shipping_city FROM customers c JOIN addresses b ON c.billing_address_id = b.address_id JOIN addresses s ON c.shipping_address_id = s.address_id;
  2. SELECT c.tenant_id, b.city AS billing_city, s.city AS shipping_city FROM customers c JOIN addresses b ON c.tenant_id = b.tenant_id AND c.billing_address_id = b.address_id JOIN addresses s ON c.tenant_id = s.tenant_id AND c.shipping_address_id = s.address_id; (correct answer)
  3. SELECT c.tenant_id, b.city AS billing_city, s.city AS shipping_city FROM customers c JOIN addresses b ON c.tenant_id = b.tenant_id AND c.billing_address_id = b.address_id JOIN addresses s ON c.tenant_id = s.tenant_id AND c.billing_address_id = s.address_id;
  4. SELECT c.tenant_id, city AS billing_city, city AS shipping_city FROM customers c JOIN addresses b ON c.tenant_id = b.tenant_id AND c.billing_address_id = b.address_id JOIN addresses s ON c.tenant_id = s.tenant_id AND c.shipping_address_id = s.address_id;
Explanation: When you need to join the same table multiple times in SQL — a technique called a self-join on a lookup table — two things matter: using distinct aliases for each join instance, and including all columns that form the composite key in your join condition. Here, the addresses table uses a composite key of (tenant_id, address_id). This means address IDs are only unique within a tenant — the same address ID could belong to different tenants. So joining on address_id alone is insufficient; you must also match tenant_id to avoid pulling addresses from the wrong tenant. Option B correctly aliases the addresses table twice (b for billing, s for shipping), joins on both tenant_id and the respective address ID for each alias, and qualifies the city column with each alias (b.city, s.city). This is the complete, unambiguous solution. Option A fails because it only joins on address_id, ignoring tenant_id. In a multi-tenant system, this can match addresses from the wrong tenant entirely — a silent data integrity bug. Option C uses the correct join structure for the billing alias but then accidentally reuses c.billing_address_id in the shipping join condition instead of c.shipping_address_id. This means both aliases would pull the billing city, never the shipping city. Option D references city without a table alias in the SELECT clause. With two joined instances of addresses, the database cannot determine which city column you mean, causing an ambiguous column error at runtime. A good rule of thumb: whenever you join a table more than once, always qualify every column reference with the specific alias, and always include the full composite key in your join conditions.

Question 8

The assignment_snapshot table has columns snapshot_date, employee_id, project_id, and allocation. An employee can be assigned to several projects. A report must compare each assignment on 2026-06-30 with the same employee-project assignment on 2026-05-31.

Which self-join correctly pairs the two snapshots and avoids ambiguous references?

  1. SELECT c.employee_id, c.project_id, c.allocation, p.allocation FROM assignment_snapshot c JOIN assignment_snapshot p ON c.project_id = p.project_id AND c.snapshot_date = p.snapshot_date WHERE c.snapshot_date = DATE '2026-06-30' AND p.snapshot_date = DATE '2026-05-31';
  2. SELECT c.employee_id, c.project_id, c.allocation, p.allocation FROM assignment_snapshot c JOIN assignment_snapshot p ON c.employee_id = p.employee_id WHERE c.snapshot_date = DATE '2026-06-30' AND p.snapshot_date = DATE '2026-05-31';
  3. SELECT employee_id, project_id, c.allocation, p.allocation FROM assignment_snapshot c JOIN assignment_snapshot p ON c.employee_id = p.employee_id AND c.project_id = p.project_id WHERE snapshot_date = DATE '2026-06-30';
  4. SELECT c.employee_id, c.project_id, c.allocation, p.allocation FROM assignment_snapshot c JOIN assignment_snapshot p ON c.employee_id = p.employee_id AND c.project_id = p.project_id WHERE c.snapshot_date = DATE '2026-06-30' AND p.snapshot_date = DATE '2026-05-31'; (correct answer)
Explanation: When writing a self-join to compare two snapshots of the same table, you need to think about two things: the join condition must uniquely identify matching rows across both aliases, and every column reference in the SELECT and WHERE clauses must be unambiguous. A self-join works by aliasing the same table twice — here as c (current) and p (previous). To correctly pair each employee-project assignment across two dates, the join must match on both employee_id and project_id. Then, the WHERE clause must independently filter each alias to its respective snapshot date. D does exactly this: it joins on c.employee_id = p.employee_id AND c.project_id = p.project_id, ensuring you're pairing the same employee-project combination. The WHERE clause then filters c to June 30 and p to May 31. Every column in the SELECT is prefixed with an alias, so there's no ambiguity. This is the correct answer. A fails because the join condition includes c.snapshot_date = p.snapshot_date, which forces both aliases to share the same date — making it impossible for the WHERE clause to filter them to different dates. The join logic contradicts the goal. B joins only on employee_id, ignoring project_id. Since an employee can be assigned to multiple projects, this produces a cross-product of all their projects between the two dates — incorrect pairings. C has two problems: employee_id, project_id, and snapshot_date appear without aliases in the SELECT and WHERE clause, creating ambiguous references that most databases will reject or misinterpret. As a quick rule: in any self-join, alias every column reference and make sure your join condition captures the full natural key of the entity you're matching.

Question 9

Customer identifiers are unique only within a tenant. A common table expression named totals groups sales by tenant_id and customer_id, producing total_amount. The customers table uses the same two columns as its composite key.

Which query attaches each aggregate to the correct customer and returns unambiguous identifiers?

  1. SELECT c.tenant_id, c.customer_id, t.total_amount FROM customers c JOIN totals t ON c.tenant_id = t.customer_id AND c.customer_id = t.tenant_id;
  2. SELECT c.tenant_id, c.customer_id, t.total_amount FROM customers c JOIN totals t ON c.customer_id = t.customer_id;
  3. SELECT tenant_id, customer_id, t.total_amount FROM customers c JOIN totals t ON c.tenant_id = t.tenant_id AND c.customer_id = t.customer_id;
  4. SELECT c.tenant_id, c.customer_id, t.total_amount FROM customers c JOIN totals t ON c.tenant_id = t.tenant_id AND c.customer_id = t.customer_id; (correct answer)
Explanation: When joining on a composite key — one made of multiple columns — every column in that key must appear in the ON clause, matched to its correct counterpart. Missing even one column, or swapping columns, produces wrong or ambiguous results. Answer D is correct because it joins on both columns in the right order: c.tenant_id = t.tenant_id AND c.customer_id = t.customer_id. This guarantees each customer row is paired with the aggregate that belongs to that exact tenant-and-customer combination. It also uses table aliases (c., t.) on every selected column, making the output unambiguous even if both tables share column names. Answer A swaps the join columns — it matches c.tenant_id to t.customer_id and c.customer_id to t.tenant_id. Unless tenant IDs and customer IDs happen to overlap in value (which they generally won't), this cross-match produces incorrect or empty results. Answer B joins only on customer_id, ignoring tenant_id entirely. Since the passage states customers are unique only within a tenant, the same customer_id can exist in multiple tenants. A single-column join would mix aggregates across tenants — a classic partial-key trap. Answer C uses the correct ON clause but writes bare tenant_id and customer_id in the SELECT without a table alias. Because both c and t contain those columns, this is ambiguous and could cause an error depending on the database engine — or silently return unexpected values. Study tip: Whenever a table has a composite primary key, treat the entire key as a unit. Your ON clause must match every component column to its correct partner — never drop one, and never swap them.

Question 10

Table a contains one row with (key1, key2) = (1, 2). Table b contains rows (1, 9), (8, 2), and (1, 2). The intended relationship requires both key values to match. A developer wrote ON a.key1 = b.key1 OR a.key2 = b.key2.

What result does the written predicate produce for the row in a, and what change is required?

  1. It produces two matches; add parentheses around the two comparisons while retaining the OR operator.
  2. It produces one match; retain OR because either comparison identifies the same composite-key row.
  3. It produces three matches; replace OR with AND so that both key comparisons must be true. (correct answer)
  4. It produces no matches; reverse the second comparison so that the key positions are cross-matched.
Explanation: When joining tables on a composite key (a key made of multiple columns), every component of that key must match simultaneously — not just one or the other. This is the core concept being tested here. Think through what ON a.key1 = b.key1 OR a.key2 = b.key2 actually does for the single row a = (1, 2). It checks each row in b and returns a match if either condition is true:
  • b = (1, 9): key1 matches (1=1) ✓ → included
  • b = (8, 2): key2 matches (2=2) ✓ → included
  • b = (1, 2): both match ✓ → included
That's three matches, when only one row truly satisfies the composite relationship. The fix is replacing OR with AND, forcing both key1 and key2 to match simultaneously — which correctly returns only (1, 2). C is correct. A is wrong because parentheses don't change the logic of two simple comparisons joined by OR — the operator itself is the problem, not grouping. B is wrong because the predicate produces three matches, not one, and OR fundamentally cannot enforce a composite-key relationship. D is wrong because there are actually three matches (not zero), and cross-matching key positions would create a logically meaningless join. Study tip: Whenever you see a join on multiple columns, instinctively reach for AND between conditions. Using OR in a join predicate almost always signals a bug — it drastically expands your result set by matching rows that share any key value rather than all key values.