SQL UNION ALL: Combine Rows Without Erasing Repetitions

The practice task

Combine morning and afternoon help-desk enquiries. Two enquiries share the same category. Compare the number of enquiry events retained by UNION ALL with the number of distinct category values retained by UNION.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE morning(category TEXT);
CREATE TABLE afternoon(category TEXT);
INSERT INTO morning VALUES ('access'),('billing');
INSERT INTO afternoon VALUES ('access'),('delivery');

WITH all_events AS (
  SELECT category FROM morning UNION ALL SELECT category FROM afternoon
), distinct_categories AS (
  SELECT category FROM morning UNION SELECT category FROM afternoon
)
SELECT 'all enquiry events' AS measure, COUNT(*) AS total FROM all_events
UNION ALL
SELECT 'unique categories', COUNT(*) FROM distinct_categories
ORDER BY measure;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The first total is four because both access enquiries remain in all_events. The second total is three because UNION keeps only the distinct category values. The totals answer different questions about exactly the same source rows.

If you add a different session label to each input, even the access rows differ as complete tuples; UNION then retains all four. Check column positions too. A category in the first column of one branch and a session label in that position in the other creates a misleading result.

Adding UNION ALL does not establish that two source systems contain independent events. If the same event can appear in both sources, identify it with a stable event ID and define a reconciliation policy. Do not rely on matching text descriptions to decide that two occurrences are one event.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Check column order as well as column count.
• Use a final ORDER BY for the combined display.
• A UNION result is a distinct list, which may differ from an event ledger.