SQL Combining Query Results

A quick summary of all four set operations side by side — the same two source queries, four different outcomes.

What & Why

Every operation in this series answers a different question about the same two queries. Seeing all four outcomes listed together, from the exact same source data, makes the differences concrete.

See How It Works

BUSINESS QUESTION

Growth wants signups and activations in one chronological activity feed.

id	created_at	country	channel	plan	activated_at	churned_at
101	2024-01-15 09:10:00+00	US	organic	pro	2024-01-16 14:25:00+00	NULL
102	2024-02-10 11:05:00+00	CA	paid	free	NULL	2024-03-20 10:00:00+00
103	2024-03-12 16:35:00+00	GB	referral	pro	2024-03-13 08:15:00+00	NULL
104	2024-04-01 13:20:00+00	US	organic	free	2024-04-03 12:00:00+00	NULL

EXAMPLE QUERY

SELECT
  id AS user_id,
  created_at AS occurred_at,
  'signup' AS activity_type
FROM growth.users
UNION ALL
SELECT
  id AS user_id,
  activated_at AS occurred_at,
  'activation' AS activity_type
FROM growth.users
WHERE activated_at IS NOT NULL
ORDER BY occurred_at, user_id, activity_type;

RESULT — exact output from the displayed Queryflo rows

user_id	occurred_at	activity_type
101	2024-01-15 09:10:00+00	signup
101	2024-01-16 14:25:00+00	activation
102	2024-02-10 11:05:00+00	signup
103	2024-03-12 16:35:00+00	signup
103	2024-03-13 08:15:00+00	activation
104	2024-04-01 13:20:00+00	signup
104	2024-04-03 12:00:00+00	activation

UNION ALL keeps four signups and the three non-null activation timestamps.

Now You Try

Practice this concept

Growth wants signups and activations in one chronological activity feed.

Available schema

growth

Prefix tables with growth.table_name.

user_idoccurred_atactivity_type

growth.users

Column	Type
id	integer
created_at	timestamp with time zone
country	text
channel	text
plan	text
activated_at	timestamp with time zone
churned_at	timestamp with time zone
legacy_user_code	text

query.sql

Intermediate business practice