During the monthly ETL job, you are asked to merge the quarterly invoice tables Q1_2025 and Q2_2025, which share an identical schema and a primary key column named invoice_id. The finance dashboard that consumes the merged data must count each invoice exactly once, while still retaining every invoice that appears in either quarter. Which data-wrangling operation should you implement in the SQL transformation layer to meet these requirements with minimal additional processing?
Use UNION ALL to append the two tables without further deduplication.
Use a LEFT JOIN from Q1_2025 to Q2_2025 on invoice_id.
Use an INNER JOIN on invoice_id to combine the two tables.
Use a UNION of Q1_2025 and Q2_2025 selecting the same ordered columns.
A set-based UNION returns all rows that appear in either table but removes any duplicates that share the same values across every selected column, so an invoice present in both quarterly tables will appear only once in the merged result. An INNER JOIN would retain only invoices that exist in both quarters, dropping invoices unique to one table. UNION ALL concatenates the tables and preserves duplicates, causing double-counting of invoices that appear in both quarters. A LEFT JOIN from Q1_2025 to Q2_2025 would omit invoices that occur only in Q2_2025, violating the requirement to keep all records.
Ask Bash
Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.
What is the difference between UNION and UNION ALL in SQL?
Open an interactive chat with Bash
What does a primary key column like invoice_id ensure in a database?
Open an interactive chat with Bash
Why is an INNER JOIN inappropriate for merging invoice tables in this scenario?