DAA-C01 Question 48
Single answerPerform table joins between parent/child tablesA retail company stores order headers in ORDERS and line items in ORDER_ITEMS. ORDERS contains one row per order with columns ORDER_ID, CUSTOMER_ID, and ORDER_TS. ORDER_ITEMS contains one row per item with columns ORDER_ID, PRODUCT_ID, QUANTITY, and UNIT_PRICE. An analyst needs a result set that includes every order, even if an order was created before any line items were loaded, along with the total order value when items exist. Which SQL approach best meets this requirement?
- A
Select from ORDERS o LEFT OUTER JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID, then GROUP BY the order-level columns and calculate SUM(i.QUANTITY * i.UNIT_PRICE).
- B
Select from ORDERS o INNER JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID, then GROUP BY the order-level columns and calculate SUM(i.QUANTITY * i.UNIT_PRICE).
- C
Select from ORDERS o RIGHT OUTER JOIN ORDER_ITEMS i ON o.ORDER_ID = i.ORDER_ID, then GROUP BY the order-level columns and calculate SUM(i.QUANTITY * i.UNIT_PRICE).
- D
Select from ORDERS o CROSS JOIN ORDER_ITEMS i, then filter rows with WHERE o.ORDER_ID = i.ORDER_ID and GROUP BY the order-level columns.
Show answer and explanation
Correct answer: A
Explanation
In parent/child joins, the key design decision is which side's unmatched rows must be preserved. Because ORDERS is the parent and the analyst must return every order regardless of whether child rows exist in ORDER_ITEMS, the correct pattern is a LEFT OUTER JOIN from ORDERS to ORDER_ITEMS on ORDER_ID. This preserves parent rows and allows aggregation of child values. In Snowflake, standard ANSI join semantics apply: INNER JOIN returns only matched rows, LEFT OUTER JOIN preserves all rows from the left table, and RIGHT OUTER JOIN preserves all rows from the right table. For reporting, analysts often pair this pattern with COALESCE(SUM(...), 0) if they want zero instead of NULL for orders without items. This aligns with SQL best practices and Snowflake documentation on joins and aggregations.
- A. Correct.
Correct. A LEFT OUTER JOIN from the parent table ORDERS to the child table ORDER_ITEMS preserves all rows from ORDERS, including orders that do not yet have matching child rows. Aggregating SUM(i.QUANTITY * i.UNIT_PRICE) after the join returns the total order value for matched items, while unmatched orders remain in the result with a NULL aggregate unless explicitly converted with COALESCE. This is the standard pattern when the business requirement is to keep all parent rows and optionally include child data.
- B. Incorrect.
Incorrect. An INNER JOIN returns only orders that have at least one matching row in ORDER_ITEMS. That means orders created before line items were loaded would be excluded entirely, which violates the requirement to include every order.
- C. Incorrect.
Incorrect. A RIGHT OUTER JOIN here preserves all rows from ORDER_ITEMS, not all rows from ORDERS. Since the requirement is to retain every parent order, this join direction is the opposite of what is needed. Although the query could be rewritten with tables reversed, as written it does not best meet the requirement.
- D. Incorrect.
Incorrect. A CROSS JOIN produces a Cartesian product before filtering, which is unnecessary and inefficient for a parent/child relationship. While the WHERE clause can make the final result logically similar to an INNER JOIN, it still excludes orders without items and therefore fails the requirement. It also reflects a poor join pattern compared with explicit ANSI joins.