SQL EXCEPT

Keeps rows from the FIRST query that do NOT appear in the second — order matters here, unlike every other set operation in this series.

What & Why

EXCEPT returns rows from the first query that don't show up anywhere in the second. This is the one set operation in this series where order matters — A EXCEPT B and B EXCEPT A generally produce completely different results, not just a differently-ordered version of the same rows.

See How It Works

BUSINESS QUESTION

Growth wants desktop users who are not in the 5-to-8 page-view cohort.

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

A EXCEPT B↓

Sessions with 5–8 page views

user_id
101
105
106

Result set · 2 rows

user_id
103
107

Users 103 and 107 have desktop sessions but no session in the 5–8 page-view set.

EXAMPLE QUERY

SELECT DISTINCT
  user_id
FROM growth.sessions
WHERE device = 'desktop'
EXCEPT
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 desktop users who are not in the 5-to-8 page-view cohort.

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