SQL Sets and Set Operations

Concept. Set operators combine two query results: UNION (in either), INTERSECT (in both), EXCEPT (in the first but not the second). UNION removes duplicates; UNION ALL keeps them.

Intuition. Stack the genres Mickey played onto the genres he was recommended. UNION merges them into one deduplicated set, INTERSECT keeps the genres in both, EXCEPT keeps the ones he played but was not recommended.

Sets vs Multi-sets

Key concept: SQL tables are multi-sets (bags).

Mathematical set

  • No duplicates allowed
  • {1, 2, 3}: each element once
  • Order doesn't matter

SQL multi-set (bag)

  • Duplicates ARE allowed
  • {1, 1, 2, 3, 3, 3}: elements can repeat
  • Each duplicate counts separately

SQL Set Operations Overview

The four set operations as row tables. Query A is Mickey and Minnie, Query B is Mickey and Daffy; Mickey (teal) is in both, Minnie (blue) only A, Daffy (violet) only B. UNION returns the three distinct users; UNION ALL keeps the duplicate Mickey (four rows); INTERSECT returns only Mickey; EXCEPT (A minus B) returns only Minnie.

Figure 1. Set A and Set B are two example user sets; the panels show what each operation returns.

UNION: Combine and Remove Duplicates

Creates a true set from two queries. Duplicate rows appear only once.

-- Artists from Pop songs
SELECT DISTINCT artist                -- Query A: Pop artists
FROM Songs 
WHERE genre = 'Pop'

UNION                                 -- UNION: Combines and removes duplicates

-- Artists from Rock songs
SELECT DISTINCT artist                -- Query B: Rock artists
FROM Songs 
WHERE genre = 'Rock'                  -- Result: All artists from EITHER genre

Result: Taylor Swift, Ed Sheeran (each artist appears once)

UNION ALL: Combine and Keep Duplicates

UNION ALL keeps every row, duplicates and all.

-- All Pop listens
SELECT user_id, song_id 
FROM Listens l
JOIN Songs s ON l.song_id = s.song_id
WHERE genre = 'Pop'                   -- First result set

UNION ALL                             -- UNION ALL: Keeps ALL rows (duplicates too!)

-- All Classic listens
SELECT user_id, song_id 
FROM Listens l
JOIN Songs s ON l.song_id = s.song_id
WHERE genre = 'Classic'               -- If user listened to both, appears twice
Operation Duplicates Use When
UNION Removed Want unique results
UNION ALL Kept Need all rows for counting/aggregation

Performance: UNION ALL skips the dedup pass, so it avoids the sort UNION needs.

INTERSECT: Find Common Rows

Returns only rows that appear in BOTH queries.

-- Users who listened to Pop
SELECT DISTINCT user_id 
FROM Listens l
JOIN Songs s ON l.song_id = s.song_id
WHERE genre = 'Pop'                   -- Query A: Pop listeners

INTERSECT                             -- INTERSECT: Only rows in BOTH results

-- Users who listened to Classic
SELECT DISTINCT user_id 
FROM Listens l
JOIN Songs s ON l.song_id = s.song_id
WHERE genre = 'Classic'               -- Result: Users who like BOTH genres

Result: Users 1, 2, and 3 (Mickey, Minnie, and Daffy all listened to both Pop and Classic).

EXCEPT: Find Difference

Returns rows from first query that are NOT in second query.

-- All songs
SELECT song_id, title FROM Songs       -- Query A: All songs in database

EXCEPT                                 -- EXCEPT: A minus B (set difference)

-- Songs that have been listened to
SELECT DISTINCT s.song_id, s.title 
FROM Songs s
JOIN Listens l ON s.song_id = l.song_id  -- Query B: Songs with listens
                                         -- Result: Songs NEVER listened to

Result: Songs 3, 4, 5, 9, 10 (Shape of You, Photograph, Shivers, Bad Blood, DJ Mix). Never played.

Performance Tips

  1. Index columns used in set operations for better performance

  2. Rewrite as a JOIN where it fits: an INTERSECT can be rewritten as a JOIN, and EXCEPT as a LEFT JOIN ... WHERE IS NULL; on indexed keys the join is usually faster.

Practice: Multi-set vs Set Behavior

-- This table has duplicates (multi-set)
SELECT genre FROM Songs;              -- Raw data: Duplicates preserved
-- Result: Pop, Pop, Rock, Rock, Rock, Classic, Classic, Classic, Rock, NULL

-- Remove duplicates to get a set
SELECT DISTINCT genre FROM Songs;     -- DISTINCT: Each value once only
-- Result: Pop, Rock, Classic, NULL   -- NULL counts as a distinct value!

-- UNION removes duplicates (set operation)
SELECT genre FROM Songs WHERE artist = 'Taylor Swift'
UNION                                 -- Set operation: Removes duplicates
SELECT genre FROM Songs WHERE artist = 'Ed Sheeran';
-- Result: Pop, Rock (no duplicates)

-- UNION ALL keeps duplicates (multi-set operation)  
SELECT genre FROM Songs WHERE artist = 'Taylor Swift'
UNION ALL                             -- Multi-set: Preserves ALL duplicates
SELECT genre FROM Songs WHERE artist = 'Ed Sheeran';
-- Result: Pop, Pop, Rock, Rock, Rock, Rock (all occurrences)