Advanced Joins and Sets

Union All

Combine Compatible Rows

UNION ALL stacks result sets that have the same number of columns without removing duplicates.

Program

Play the script to choose a starting day and combine signups with purchases into one activity feed.

min_day
union_all_activity.sql
Replay: real traced execution (multi-file project)
CREATE TABLE signups (event_day TEXT, user_name TEXT);
INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');
CREATE TABLE purchases (event_day TEXT, user_name TEXT);
INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');
WITH params(min_day) AS (VALUES ('2026-02-02')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;

CREATE TABLE signups (event_day TEXT, user_name TEXT);
INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');
CREATE TABLE purchases (event_day TEXT, user_name TEXT);
INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');
WITH params(min_day) AS (VALUES ('2026-02-01')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;

CREATE TABLE signups (event_day TEXT, user_name TEXT);
INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');
CREATE TABLE purchases (event_day TEXT, user_name TEXT);
INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');
WITH params(min_day) AS (VALUES ('2026-02-03')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;

  1. tables ← 1 row

    1CREATE TABLE signups (event_day TEXT, user_name TEXT);2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');
    values this step1 rowtables

  2. signups ← 2 rows

    1CREATE TABLE signups (event_day TEXT, user_name TEXT);2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');3CREATE TABLE purchases (event_day TEXT, user_name TEXT);
    values this step2 rowssignups

  3. tables ← 2 rows

    2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');3CREATE TABLE purchases (event_day TEXT, user_name TEXT);4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');
    values this step2 rowstables

  4. purchases ← 2 rows

    3CREATE TABLE purchases (event_day TEXT, user_name TEXT);4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');5WITH params(min_day) AS (VALUES ('2026-02-02')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;
    values this step2 rowspurchases

  5. result ← 3 rows

    4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');5WITH params(min_day) AS (VALUES ('2026-02-02')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE signups (event_day TEXT, user_name TEXT);2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');
    values this step1 rowtables

  2. signups ← 2 rows

    1CREATE TABLE signups (event_day TEXT, user_name TEXT);2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');3CREATE TABLE purchases (event_day TEXT, user_name TEXT);
    values this step2 rowssignups

  3. tables ← 2 rows

    2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');3CREATE TABLE purchases (event_day TEXT, user_name TEXT);4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');
    values this step2 rowstables

  4. purchases ← 2 rows

    3CREATE TABLE purchases (event_day TEXT, user_name TEXT);4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');5WITH params(min_day) AS (VALUES ('2026-02-01')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;
    values this step2 rowspurchases

  5. result ← 4 rows

    4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');5WITH params(min_day) AS (VALUES ('2026-02-01')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;
    values this step4 rowsresult

  1. tables ← 1 row

    1CREATE TABLE signups (event_day TEXT, user_name TEXT);2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');
    values this step1 rowtables

  2. signups ← 2 rows

    1CREATE TABLE signups (event_day TEXT, user_name TEXT);2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');3CREATE TABLE purchases (event_day TEXT, user_name TEXT);
    values this step2 rowssignups

  3. tables ← 2 rows

    2INSERT INTO signups VALUES ('2026-02-01', 'Ada'), ('2026-02-03', 'Lin');3CREATE TABLE purchases (event_day TEXT, user_name TEXT);4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');
    values this step2 rowstables

  4. purchases ← 2 rows

    3CREATE TABLE purchases (event_day TEXT, user_name TEXT);4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');5WITH params(min_day) AS (VALUES ('2026-02-03')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;
    values this step2 rowspurchases

  5. result ← 2 rows

    4INSERT INTO purchases VALUES ('2026-02-02', 'Ada'), ('2026-02-04', 'Nia');5WITH params(min_day) AS (VALUES ('2026-02-03')) SELECT event_day, user_name, 'signup' AS event_type FROM signups WHERE event_day >= (SELECT min_day FROM params) UNION ALL SELECT event_day, user_name, 'purchase' AS event_type FROM purchases WHERE event_day >= (SELECT min_day FROM params) ORDER BY event_day, user_name, event_type;
    values this step2 rowsresult
UNION ALL `UNION ALL` keeps every row from both inputs.
compatible columns Both sides return day, user, and event type in the same positions.
event label A literal label records which source table produced each row.