SQL UNION ALL

Combines two queries' results and keeps every row, duplicates included — faster than UNION, since there's no deduplication work to do.

What & Why

UNION ALL stacks the rows from two queries together exactly like UNION, but skips the deduplication step entirely. Every row from both queries survives into the result, even if the exact same row appears in both.

See How It Works

BUSINESS QUESTION

Growth wants every membership in the desktop and 5-to-8 page-view cohorts, including users who belong to both.

id	user_id	started_at	ended_at	page_views	device
5001	101	2024-01-16 14:20:00+00	2024-01-16 14:52:00+00	8	desktop
5002	102	2024-02-10 11:10:00+00	2024-02-10 11:18:00+00	3	mobile
5003	103	2024-03-13 08:10:00+00	2024-03-13 09:04:00+00	12	desktop
5004	104	2024-04-03 12:00:00+00	NULL	1	tablet
5005	105	2024-04-04 09:00:00+00	2024-04-04 09:21:00+00	7	mobile
5006	106	2024-04-05 10:15:00+00	2024-04-05 10:42:00+00	5	tablet
5007	107	2024-04-06 13:05:00+00	2024-04-06 13:38:00+00	10	desktop

Watch compatible tables produce a set resultStep 1 of 3

Desktop session users

user_id
101
103
107

UNION ALL↓

Sessions with 5–8 page views

user_id
101
105
106

Result set · 0 rows

user_id

Read the first compatible one-column result from growth.sessions.

EXAMPLE QUERY

SELECT DISTINCT
  user_id
FROM growth.sessions
WHERE device = 'desktop'
UNION ALL
SELECT DISTINCT
  user_id
FROM growth.sessions
WHERE page_views BETWEEN 5 AND 8
ORDER BY user_id;

Now You Try

Practice this concept

Growth wants every membership in the desktop and 5-to-8 page-view cohorts, including users who belong to both.

Available schema

growth

Prefix tables with growth.table_name.

user_id

growth.sessions

Column	Type
id	bigint
user_id	integer
started_at	timestamp with time zone
ended_at	timestamp with time zone
page_views	integer
device	text
internal_session_score	integer

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