lesson 27 Joins & sets · intermediate

INTERSECT & EXCEPT

Find values shared by two results or present in only one.

the idea

INTERSECT keeps distinct rows appearing in both results. EXCEPT keeps distinct rows from the first result that are absent from the second. Column positions and types should align.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT viewer_id
FROM watch_events
WHERE video_id=1
INTERSECT
SELECT viewer_id
FROM watch_events
WHERE video_id=2;

Watch for: UNION means either set, which answers a different question.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   List viewers of video 1. INTERSECT will keep viewers who also watched video 2. EXCEPT would keep viewers of video 1 who never watched video 2.

   step-1.sql Run

   sql›

   SELECT viewer_id
   FROM watch_events
   WHERE video_id = 1
   ORDER BY viewer_id;

2. 2

   Keep viewers in both sets. INTERSECT returns distinct rows present in both SELECT results. Column positions must align.

   step-2.sql Run

   sql›

   SELECT viewer_id
   FROM watch_events
   WHERE video_id = 1
   INTERSECT
   SELECT viewer_id
   FROM watch_events
   WHERE video_id = 2;