Joins combine tables side by side: a row from the left gets columns from the right. Set operations stack results vertically instead. UNION returns rows that appear in either query, INTERSECT rows that appear in both, and EXCEPT rows from the first query that don’t appear in the second. They read like the Venn diagrams from school maths, and most of the time they behave like them. The interesting interview questions live in the places where they don’t: duplicates, NULL, column matching and precedence.
The queries here ran on SQLite through Python’s sqlite3 module, which follows the same rules for everything shown except two PostgreSQL features that SQLite lacks (INTERSECT ALL/EXCEPT ALL, and operator precedence). Those are labelled.
Sample data: a newsletter list and a customer table
A marketing team wants to cross-reference newsletter subscribers with paying customers. The tables are deliberately a bit messy, which is what real exports look like:
CREATE TABLE newsletter (email text, country text);
CREATE TABLE customers (email text, country text);
CREATE TABLE unsubscribed (email text);
INSERT INTO newsletter VALUES
('ana@example.com', 'PT'), ('ben@example.com', 'US'),
('cy@example.com', NULL), ('dee@example.com', 'DE'),
('ben@example.com', 'US'); -- Ben signed up twice
INSERT INTO customers VALUES
('ben@example.com', 'US'), ('cy@example.com', NULL),
('eli@example.com', 'FR'), ('ana@example.com', 'BR');
INSERT INTO unsubscribed VALUES ('dee@example.com'), (NULL);The rules every set operation shares
All three operators require the two queries to return the same number of columns, with compatible types. Get it wrong and PostgreSQL stops immediately with each UNION query must have the same number of columns, or with something like UNION types integer and text cannot be matched when the types disagree. Those errors are the friendly case.
The unfriendly case is when the column count and types agree but the meaning doesn’t. Columns are matched by position, and the result takes its column names from the first query. Swap two text columns in the second query and nothing complains:
SELECT email, country FROM newsletter
UNION
SELECT country, email FROM customers
ORDER BY 1
LIMIT 4;email | country
NULL | cy@example.com
BR | ana@example.com
FR | eli@example.com
US | ben@example.comCountry codes are now sitting under the email heading. When you write set operations over wide queries, list columns explicitly in both halves and in the same order; SELECT * on each side breaks the day someone adds a column to one table.
Two more shared rules. ORDER BY and LIMIT apply to the combined result, not to the last query, and they can only refer to output column names or positions. If you want to sort or limit one branch before combining, wrap it in parentheses or a subquery. And the plain forms remove duplicate rows from the output, which means PostgreSQL has to sort or hash the whole combined result to find them.
UNION and UNION ALL in one paragraph
UNION of the two email columns gives five distinct addresses. UNION ALL gives all nine rows, Ben three times. The second is cheaper because it skips deduplication, and it’s the right default when the branches can’t overlap (rows from orders_2025 and orders_2026, say) or when you’re going to aggregate the result anyway. The separate UNION versus UNION ALL note covers the cost side in more detail.
INTERSECT: rows in both, NULLs included
Which subscribers are also customers in the same country?
SELECT email, country FROM newsletter
INTERSECT
SELECT email, country FROM customers
ORDER BY email;email | country
ben@example.com | US
cy@example.com | NULLAna isn’t there because her country differs (PT versus BR). Ben appears once even though he’s in the newsletter twice, because INTERSECT removes duplicates. And Cy is there with a NULL country.
That last row is the one to understand. A set operation compares rows with “is not distinct from” semantics, so two NULLs count as equal. A join compares with =, where NULL = NULL is unknown. Write the “same” question as a join and you get a different answer:
SELECT n.email, n.country
FROM newsletter n
JOIN customers c ON c.email = n.email AND c.country = n.country;email | country
ben@example.com | US
ben@example.com | USCy disappears and Ben is duplicated. Neither result is “wrong”; they answer different questions. If an interviewer asks you to rewrite an INTERSECT as a join, the faithful version needs c.country IS NOT DISTINCT FROM n.country and a DISTINCT.
PostgreSQL also has INTERSECT ALL, which SQLite doesn’t support, so this part is from the documented rule rather than a run: a row that appears m times on the left and n times on the right appears min(m, n) times. For the email columns, Ben appears twice in the newsletter and once in customers, so INTERSECT ALL would return him once.
EXCEPT and the NOT IN trap
Who should get the next newsletter? Everyone subscribed, minus everyone who unsubscribed:
SELECT email FROM newsletter
EXCEPT
SELECT email FROM unsubscribed
ORDER BY email;email
ana@example.com
ben@example.com
cy@example.comDee is removed, Ben is deduplicated, and the stray NULL row in unsubscribed (an import bug) doesn’t matter. Now the version a lot of people write first:
SELECT DISTINCT email
FROM newsletter
WHERE email NOT IN (SELECT email FROM unsubscribed);It returns zero rows. email NOT IN (x, NULL) expands to email <> x AND email <> NULL, and email <> NULL is unknown for every row, so the WHERE never passes. Nobody gets the newsletter, and no error tells you why. EXCEPT treats the NULL as just another value that happens not to match any address. NOT EXISTS is also safe, because a correlated WHERE u.email = n.email simply finds no match for the NULL row. Which one to pick depends on shape: EXCEPT needs the same columns on both sides, so when you want to return other columns from newsletter (a name, a signup date) while filtering on email alone, NOT EXISTS is the one that fits.
Order matters for EXCEPT in a way it doesn’t for the other two. newsletter EXCEPT customers is Dee; customers EXCEPT newsletter is Eli. And with EXCEPT ALL (PostgreSQL only), counts are subtracted: Ben’s two newsletter rows minus one customer row leaves one Ben, so newsletter EXCEPT ALL customers on emails returns Ben and Dee.
Chaining operators and precedence
You can chain as many set operations as you like, and PostgreSQL gives INTERSECT higher precedence than UNION and EXCEPT. So A UNION B INTERSECT C means A UNION (B INTERSECT C). Not every engine agrees. SQLite evaluates compound selects left to right:
SELECT 1 AS n UNION SELECT 2 INTERSECT SELECT 2;SQLite returns only 2, because it computes (1 ∪ 2) ∩ 2. PostgreSQL returns 1 and 2. Code that’s portable, and code a reviewer can read without looking up a precedence table, uses parentheses whenever it mixes operators.
Practice question: the churn report
“Product wants three numbers for October: customers who were active in September and October (retained), active in September but not October (churned), and active in October but not September (new). You have activity(customer_id, active_on date) with one row per customer per active day. Write it with set operations and explain the duplicate handling.”
Answer
Each month first has to become a set of customer ids, one row per customer, so the per-day rows don’t skew anything:
WITH sep AS (
SELECT customer_id FROM activity
WHERE active_on >= DATE '2026-09-01' AND active_on < DATE '2026-10-01'
),
oct AS (
SELECT customer_id FROM activity
WHERE active_on >= DATE '2026-10-01' AND active_on < DATE '2026-11-01'
)
SELECT
(SELECT count(*) FROM (SELECT customer_id FROM sep INTERSECT SELECT customer_id FROM oct) r) AS retained,
(SELECT count(*) FROM (SELECT customer_id FROM sep EXCEPT SELECT customer_id FROM oct) c) AS churned,
(SELECT count(*) FROM (SELECT customer_id FROM oct EXCEPT SELECT customer_id FROM sep) n) AS new_customers;The plain INTERSECT and EXCEPT deduplicate, which is exactly right here: a customer active on 20 days in September is one customer. If I’d reached for INTERSECT ALL, a customer active 20 days in September and 3 in October would count three times. I used half-open date ranges so time zones and timestamps don’t drop the last day of the month, and I’d check customer_id is NOT NULL; set operations would happily count a NULL id as one “customer”. As a sanity check, retained plus churned must equal September’s distinct count, and retained plus new must equal October’s, which is a cheap assertion to run before the numbers go into a slide.