Noos
PT EN
RAN-DBDEA-00230
Ranvier · DBDEA

Exam DBDEA Topics: transformation Difficulty: hard

A reporting query must return every order, joined to its payment when one exists, while showing only approved payment details: ```sql SELECT o.order_id, p.status FROM orders o LEFT JOIN payments p ON o.order_id = p.order_id WHERE p.status = 'APPROVED'; ``` After running it, the analyst notices that orders without any payment are missing from the results. Why did they disappear, and what is the fix?

Options (Reading Mode) Click to filter discussion →
  1. A. The WHERE clause runs after the join: NULL-padded orders fail p.status = 'APPROVED', turning the LEFT JOIN into an inner join — move the predicate to ON
  2. B. ON and WHERE are interchangeable in outer joins, so the missing orders must have been lost by a different filter somewhere upstream
  3. C. Spark automatically rewrites WHERE predicates on the right table into the ON clause, so rewriting the query manually is pointless
  4. D. A LEFT JOIN by definition never returns orders that lack a matching payment; preserving them requires a FULL OUTER JOIN instead
Reveal answer

Correct answer: The WHERE clause runs after the join: NULL-padded orders fail p.status = 'APPROVED', turning the LEFT JOIN into an inner join — move the predicate to ON

Reference: https://docs.databricks.com/aws/en/pyspark/reference

Train this exam on Ranvier →

02 — The Myelin Layer · Canonical Discussion

Where the exam tries to trick you — and how those who passed solved it.

0 Passed

contributed to this question

Answer Distractor Matrix Click an option to filter or open its dissection.
✦ Consolidated Synthesis (Wiki) rev. #1 (baseline) · CC BY-SA 4.0
Start synthesis for this question →

No community synthesis consolidated for this question yet. Below is the verifiable official starting point — if you passed the exam, open the option dissection.

Official Answer (A): The WHERE clause runs after the join: NULL-padded orders fail p.status = 'APPROVED', turning the LEFT JOIN into an inner join — move the predicate to ON

⚓ Official Documentation Anchor: https://docs.databricks.com/aws/en/pyspark/reference
Ranvier · DBDEA

Contribute to this Question

Your contribution builds your Orbital Sheath

Peer contributions

No public contribution yet. Be the first who passed to explain why the distractors trick candidates — your contribution builds your Orbital Sheath.