Reconciling Two Customer Extracts with UNION and MINUS
Two systems feed a customer master. LEGACY_CUSTOMERS has 18,420 rows; NEW_CUSTOMERS has 18,395. The migration team knows 25 customer IDs exist only in LEGACY and none exist only in NEW. An analyst runs:
SELECT customer_id, customer_name FROM legacy_customers UNION SELECT customer_id, customer_name FROM new_customers;
The result has 18,430 rows, which the team reads as 10 duplicate customers. In fact, 10 IDs have a corrected spelling in NEW. LEGACY.CUSTOMER_ID is NUMBER while NEW.CUSTOMER_ID is VARCHAR2(10), and some NEW values carry leading zeros such as 00123. A UNION ALL version returns 36,815 rows. The team needs a one-row-per-differing-ID exception report, an exact count reconciliation, and a decision on whether to normalize the key type before comparing.
1. Explain the 18,430 result and write an exception report that shows the 25 IDs only in LEGACY and the 10 IDs whose names differ.
Read the worked answer
UNION removes duplicate complete rows, not duplicate customer IDs. If an ID has the same name in both sources, the two rows collapse to one. For the 10 IDs with corrected spelling, the two rows differ in CUSTOMER_NAME, so both survive. The arithmetic is 18,420 legacy rows plus 10 extra distinct name variants from NEW, giving 18,430. The team is seeing attribute changes, not duplicate IDs.
IDs only in LEGACY:
SELECT customer_id
FROM legacy_customers
MINUS
SELECT customer_id
FROM new_customers;
Name differences for IDs present in both sources require a join, with explicit key normalization:
SELECT l.customer_id,
l.customer_name AS legacy_name,
n.customer_name AS new_name
FROM legacy_customers l
JOIN new_customers n
ON TO_NUMBER(n.customer_id) = l.customer_id
WHERE l.customer_name <> n.customer_name;
TO_NUMBER assumes every NEW value is numeric; otherwise the query fails. A safer staging step is to validate with VALIDATE_CONVERSION or a regex check before conversion. The pattern also shows why the key type decision matters: comparing NUMBER to VARCHAR2 forces implicit conversion and can hide malformed values.
2. Explain what the 36,815-row UNION ALL result proves, and write a reconciliation that reports total rows by source and the 25 missing IDs without relying on UNION row counts.
Read the worked answer
UNION ALL returns every input row and performs no duplicate elimination: 18,420 + 18,395 = 36,815. That proves nothing about duplicates by itself; it only confirms both inputs were fully read. It is the right operator when duplicate rows are meaningful or when a later GROUP BY will perform controlled aggregation.
A source-tagged reconciliation:
WITH all_customers AS (
SELECT 'LEGACY' AS source, customer_id, customer_name
FROM legacy_customers
UNION ALL
SELECT 'NEW', customer_id, customer_name
FROM new_customers
)
SELECT source, COUNT(*) AS row_count
FROM all_customers
GROUP BY source;
To find IDs only in LEGACY, use MINUS on the key, not on the full row, because a changed name would otherwise appear as a missing ID. A reverse MINUS should return zero rows as expected. For a single exception report, label each ID as ONLY_LEGACY, ONLY_NEW, or NAME_CHANGED. Avoid using UNION output row count as a key count; use COUNT(DISTINCT customer_id) or compare keys explicitly. If both sources can contain duplicate keys internally, decide the business rule before deduplicating.
3. Explain how operator precedence and ORDER BY work in a compound query, and write a query that returns IDs present in both sources but with different names, sorted by customer_id.
Read the worked answer
In Oracle, INTERSECT has higher precedence than UNION, UNION ALL, and MINUS. Operators with equal precedence are evaluated left to right, so parentheses should be used whenever the intent is not obvious. ORDER BY is allowed only once, at the end of the entire compound query, and it can reference column names or positions from the first SELECT list.
One approach uses a join and then sorts:
SELECT l.customer_id
FROM legacy_customers l
JOIN new_customers n
ON TO_NUMBER(n.customer_id) = l.customer_id
WHERE l.customer_name <> n.customer_name
ORDER BY l.customer_id;
A set-based approach must compare the keys separately from the attributes. INTERSECT on (customer_id, customer_name) returns matching rows, not changed rows. To isolate changed rows, subtract the identical pairs from the shared keys, or use the join above. If a set operator is required, use parentheses around each branch and apply ORDER BY only after the final closing parenthesis. Also normalize the key explicitly before the comparison; relying on implicit NUMBER-to-VARCHAR2 conversion can prevent index use and can raise an error on a nonnumeric NEW value.