UNION versus UNION ALL in monthly sales files

UNION combines compatible query results and removes duplicate output rows. UNION ALL combines them while retaining every row. The choice is about meaning before it is about performance: should repeated-looking rows survive, and what makes two records the same event?

Monthly extracts often overlap because of reruns, delivery retries or a reporting window that intentionally includes recent history. Using UNION can hide the overlap, while UNION ALL exposes it. Neither operation, on its own, defines the correct business deduplication rule.

The following examples are self-contained SQLite queries. You can run them in the commerce lab's in-memory database; they create their inputs with CTEs rather than modifying stored tables.

Start with two deliveries that overlap

WITH first_delivery(order_id, amount_paise) AS (
    VALUES ('A', 10000), ('B', 15000)
), second_delivery(order_id, amount_paise) AS (
    VALUES ('B', 15000), ('C', 12000)
)
SELECT order_id, amount_paise FROM first_delivery
UNION ALL
SELECT order_id, amount_paise FROM second_delivery;

Four rows are retained, including B twice. The combined amount is 52,000 paise. If B represents the same unchanged order delivered twice, that total overstates unique order value.

Replacing UNION ALL with UNION produces three distinct output rows, totalling 37,000 paise. That looks like a convenient repair, but it only works because the repeated business record has an identical selected payload.

The SQL compound-query rules require compatible column counts and describe the duplicate handling of each operator. Engine-specific type and ordering rules should also be checked when combining extracts. SQLite compound SELECT.

A changed record exposes the hidden decision

Now imagine the second delivery contains B at 16,000 paise instead of 15,000. UNION retains both B rows because their selected amounts differ. A distinct-row operation cannot decide whether the new amount is an authorized correction, an error or a separate transaction.

You need a source contract: a stable business key, version or update timestamp, and a policy for conflicting versions. If the source promises immutable orders, a changed amount might be a defect. If it sends snapshots, a later version might replace an earlier one.

Those are different ingestion designs. Selecting UNION because it makes one test file look clean leaves the conflict unresolved.

Preserve delivery provenance

For investigation, retain the source label:

WITH first_delivery(order_id, amount_paise) AS (
    VALUES ('A', 10000), ('B', 15000)
), second_delivery(order_id, amount_paise) AS (
    VALUES ('B', 15000), ('C', 12000)
), combined AS (
    SELECT 'first' AS source, order_id, amount_paise
    FROM first_delivery
    UNION ALL
    SELECT 'second' AS source, order_id, amount_paise
    FROM second_delivery
)
SELECT order_id, COUNT(*) AS deliveries,
       MIN(amount_paise) AS min_amount,
       MAX(amount_paise) AS max_amount
FROM combined
GROUP BY order_id
HAVING COUNT(*) > 1;

The result identifies B with two deliveries and equal minimum/maximum amounts of 15,000. Equal amounts are only a partial payload check; a real event may have other fields whose differences matter.

Adding the source label also changes DISTINCT semantics. Even an otherwise identical record differs across sources, so UNION over all those columns will no longer collapse it. This is another reason to separate raw collection from business-key resolution.

Distinguish snapshots from new transactions

If every monthly file is a full snapshot, appending them creates repeated observations of the same entity. You may need a latest-state table or a history table with an explicit snapshot date.

If each file contains new transactions only, appending is appropriate, but replay protection is still needed. If files contain corrections, cancellation events or deletes, a simple append may fail to represent current state.

Ask the producer what a row represents and how updates are delivered. A filename such as sales_february.csv does not establish whether the file contains February events, a February snapshot or all records loaded during February.

Validate the combination before publishing

Record source filenames, delivery timestamps, row counts and checksums where appropriate. Compare the combined raw count with the sum of source counts. Then report unique keys, repeated keys and conflicting payloads under the chosen rule.

After deduplication or version resolution, reconcile the final count and amount against a reference from the source system if available. A lower row count is not automatically a cleaner dataset.

Do not rely on the apparent output sequence of UNION or UNION ALL. Add a final ORDER BY when display order matters. Similarly, avoid assuming that UNION is always the right performance choice; deduplication has work to do, but actual plans depend on the engine and data.

Exercise: add two different orders with the same amount. Confirm that selecting only amount_paise with UNION collapses those equal values even though the order IDs differ. Explain how the selected columns determine the duplicate definition.

The Data Analytics with Generative AI programme links SQL with repeatable data pipelines. Combining files reliably means preserving provenance and resolving identity deliberately, so downstream dashboards and AI summaries are built on a population you can explain.

Continue learning

This article is part of the SQL foundations for reliable analysis sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL DISTINCT: when it fixes a count and when it hides a bug.
• Continue with SQL subqueries versus CTEs: make an audit-friendly query.