Choose UNION Or UNION ALL Intentionally

`UNION` removes duplicates, while `UNION ALL` keeps them. Picking the wrong one silently changes row counts.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Preserve repeated names across two event sources

Bob appears in both sources, so UNION ALL returns both rows. If this query used plain UNION, one of the Bob rows would be removed. That difference matters whenever duplicate rows carry meaning, such as repeated events, log entries, or transactions.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE web_signups (name VARCHAR(50));

CREATE TABLE store_signups (name VARCHAR(50));

INSERT INTO
  web_signups (name)
VALUES
  ('Alice'),
  ('Bob');

INSERT INTO
  store_signups (name)
VALUES
  ('Bob'),
  ('Carol');

Validated query Shared across supported engines.

SQL

SELECT
  name
FROM
  web_signups
UNION ALL
SELECT
  name
FROM
  store_signups
ORDER BY
  name;

Expected result Returned rows for the shared example.

name
Alice
Bob
Bob
Carol

This example uses `UNION ALL` specifically to show that the second `Bob` is preserved.

Useful when

Where this command helps.

preserving duplicate events or records from multiple sources
avoiding accidental deduplication when combining query branches

Explanation

What the command is doing.

A common set-operation mistake is using UNION when duplicates are meaningful, or using UNION ALL when deduplication is required. UNION performs duplicate elimination across the combined result, while UNION ALL simply stacks rows. That affects both correctness and performance.

Choose UNION Or UNION ALL Intentionally

Preserve repeated names across two event sources

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.