- Which query correctly joins the composite key and returns both status values without an ambiguous column reference?
Answer: 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);
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.
- Which rewrite preserves the intended two-key relationship across that schema change and keeps the selected status values unambiguous?
Answer: 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;
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.
- Which join predicate implements that rule without treating an arbitrary non-null warehouse identifier as equivalent to null?
Answer: ON s.sku = t.sku AND s.warehouse_id IS NOT DISTINCT FROM t.warehouse_id
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.
- Which query correctly joins the address table twice and avoids ambiguous column references?
Answer: 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;
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.
- Which self-join correctly pairs the two snapshots and avoids ambiguous references?
Answer: 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';
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.
- What result does the written predicate produce for the row in
a, and what change is required? Answer: It produces three matches; replace OR with AND so that both key comparisons must be true.
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.
- Which query implements the required relationship while preserving orders that have no active items?
Answer: 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;
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.
- Which query attaches each aggregate to the correct customer and returns unambiguous identifiers?
Answer: 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;
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.
- 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? Answer: 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;
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.
- Which second join is both unambiguous and complete for the stated relationship?
Answer: JOIN approval a ON r.tenant_id = a.tenant_id AND r.document_id = a.document_id AND r.revision_no = a.revision_no
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.
