SQL UNION vs UNION ALL

Run the same two session-cohort queries with UNION and UNION ALL, then watch user 101 appear once or twice.

What & Why

The choice between UNION and UNION ALL comes down to one question: does the combined result need every source row preserved exactly, or is a deduplicated set of distinct values what's actually wanted? Toggle between the two below — the source queries and the Venn diagram look identical either way; only the result set changes.

See How It Works

BUSINESS QUESTION

Growth wants to compare the distinct-user count with the complete membership count across the same two session cohorts.

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 2

Desktop session users

user_id
101
103
107

UNION↓

Sessions with 5–8 page views

user_id
101
105
106

Result set · 1 rows

set_operator	row_count
UNION	5

The first summary row counts five distinct users after UNION removes the second 101.

EXAMPLE QUERY

WITH desktop_users AS (
  SELECT DISTINCT user_id
  FROM growth.sessions
  WHERE device = 'desktop'
),
engaged_users AS (
  SELECT DISTINCT user_id
  FROM growth.sessions
  WHERE page_views BETWEEN 5 AND 8
),
union_rows AS (
  SELECT user_id FROM desktop_users
  UNION
  SELECT user_id FROM engaged_users
),
union_all_rows AS (
  SELECT user_id FROM desktop_users
  UNION ALL
  SELECT user_id FROM engaged_users
)
SELECT 'UNION' AS set_operator, COUNT(*) AS row_count FROM union_rows
UNION ALL
SELECT 'UNION ALL' AS set_operator, COUNT(*) AS row_count FROM union_all_rows
ORDER BY row_count;

Now You Try

Practice this concept

Growth wants to compare the distinct-user count with the complete membership count across the same two session cohorts.

Available schema

growth

Prefix tables with growth.table_name.

set_operatorrow_count

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

query.sql

Intermediate business practice