UNION, INTERSECT, and EXCEPT

Set operations compare or stack result sets. Use them when you already have two compatible lists and need to combine them, find the overlap, or remove one list from another—without joining columns together.

Start with the question, not the keyword

Every example in this lesson uses four small lists of email addresses. customers contains people who have already purchased. prospects contains people who showed interest. event_attendees contains people who came to an event. blocked_emails contains people who must not receive a campaign.

The same address can appear in more than one list. That is not automatically an error: Amina is both a customer and a prospect, and Celia is both a customer and an event attendee. Before writing SQL, say what one result row should mean. For a campaign list, one row means one deliverable address. For an audit, one row means one source occurrence. Those are different questions, so they need different operations.

Set operations work on compatible result shapes

A set operation compares columns by position. Every SELECT must return the same number of columns, and matching positions need compatible types. The output column names usually come from the first SELECT. It does not matter whether the source tables have different names or different extra columns.

• Good: SELECT email FROM customers combined with SELECT email FROM prospects.
• Also good: give both inputs the same two-column shape with an email and a label.
• Not valid: one input returns only an email while the other returns an email and a city.

“Compatible” is about both structure and meaning. You can combine an email address with a city if both are text, but the result has no useful, trustworthy meaning. A safer habit is to give every selected column a role: the first column is always an email address; the second, if present, is always a source label.

UNION makes one distinct combined list

Customers and prospects both contain Amina and Dina. UNION returns each email once, so the result is a clean combined audience. It removes duplicate complete rows; it does not know which source should be preferred.

Trace it before running: customers contributes Amina, Celia, and Dina. Prospects contributes Amina, Bilal, and Dina. Stack those six source rows, then remove repeated complete rows. The final output is Amina, Bilal, Celia, and Dina—four addresses.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT email FROM customers
UNION
SELECT email FROM prospects
ORDER BY email;

Edit the query, predict the rows it will return, then run it.

UNION ALL keeps every source row

UNION ALL appends the results without removing duplicates. This is normally the right choice for event logs, import stages, and auditing because every input row remains visible. The extra audience label shows why the same email can appear more than once: it came from different sources.

In this query there are six rows: three customer records plus three prospect records. Amina and Dina each appear twice, and that is exactly the information the query preserves. Start with UNION ALL when you are investigating data; switch to UNION only when the business rule explicitly calls for a distinct list.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT 'customer' AS audience, email FROM customers
UNION ALL
SELECT 'prospect' AS audience, email FROM prospects
ORDER BY email, audience;

Edit the query, predict the rows it will return, then run it.

Make the output shape explicit

A set operation compares every selected column. Here both inputs return email followed by kind, so the operation is valid. A customer email and attendee email that share the same text are still separate rows because their second column differs.

This is useful for a staging report. You can preserve source context while building one result. Later, if the final requirement is one row per address, select only email from that staging result and apply the appropriate deduplication rule deliberately.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT email, 'customer' AS kind FROM customers
UNION ALL
SELECT email, 'attendee' AS kind FROM event_attendees
ORDER BY kind, email;

Edit the query, predict the rows it will return, then run it.

INTERSECT finds the overlap

INTERSECT returns rows that appear in both inputs. It is useful when the question is genuinely “which values are shared?” This query finds customers who are also prospects: Amina and Dina.

It is helpful to say the two lists aloud. List one is every customer email. List two is every prospect email. The result contains only values that survive both membership tests. INTERSECT is often clearer than a self-explained join when you only need the shared value itself—not columns from both source tables.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT email FROM customers
INTERSECT
SELECT email FROM prospects
ORDER BY email;

Edit the query, predict the rows it will return, then run it.

EXCEPT subtracts the second list from the first

EXCEPT is directional. Read it as “first result, except rows found in the second.” Prospects contains Amina, Bilal, and Dina. Customers contains Amina, Celia, and Dina. The remaining prospect-only email is Bilal.

Reversing the inputs answers a different question: customers who are not prospects. Never assume subtraction is symmetric. Write a short sentence over the query—“prospects not yet customers”—then make sure the first SELECT names the population you want to keep.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT email FROM prospects
EXCEPT
SELECT email FROM customers
ORDER BY email;

Edit the query, predict the rows it will return, then run it.

Build a broader audience, then apply one final ORDER BY

Use multiple set operations to build one result in stages. Put ORDER BY only at the end so it sorts the final combined list. If you need source-specific limits or ordering, wrap that source query in a subquery first.

Without the final ORDER BY, SQL does not promise a display order. A result can look sorted in a small test and arrive in a different order later. Treat ordering as part of the output contract, not as a visual accident.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT email FROM customers
UNION
SELECT email FROM prospects
UNION
SELECT email FROM event_attendees
ORDER BY email;

Edit the query, predict the rows it will return, then run it.

Use a nested result when an operation has a clear stage

First create a distinct combined audience. Then remove blocked email addresses. The parentheses give the combined list a clear boundary before EXCEPT applies. This is easier to review than trying to reason about a long ungrouped chain.

Read the query from the inside out. The inner result has five distinct addresses: Amina, Bilal, Celia, Dina, and Elias. The outer EXCEPT compares that result with the one blocked address, Dina. The final result has four addresses. Naming these intermediate populations is the same disciplined thinking you used with CTEs and subqueries.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT email FROM (
  SELECT email FROM customers
  UNION
  SELECT email FROM prospects
  UNION
  SELECT email FROM event_attendees
)
EXCEPT
SELECT email FROM blocked_emails
ORDER BY email;

Edit the query, predict the rows it will return, then run it.

Clean values before comparing them

Set operations compare the values you select. If one table stores Amina@example.com and another stores amina@example.com, they may not be treated as the same value depending on the database and column collation. The same problem appears with leading spaces, different phone-number formats, and inconsistent identifiers.

Decide where normalization belongs: at write time with validation and constraints, or in a carefully named query stage using functions such as LOWER and TRIM. Do not silently clean one source but not the other; apply the same definition of “same address” to every input.

Audit sources before deduplicating them

Before publishing a UNION result, use UNION ALL with a source label to see what each source contributes. This simple audit makes it clear whether repeated rows represent mistakes, expected overlaps, or an important event history.

The query below returns one count per source. Extend it with COUNT(DISTINCT email) and compare the two counts. If they differ, that source contains repeated addresses. That might be a data-quality problem, or it might represent several meaningful events. The SQL cannot decide; the report's purpose decides.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
SELECT audience, COUNT(*) AS source_rows
FROM (
  SELECT 'customer' AS audience, email FROM customers
  UNION ALL
  SELECT 'prospect', email FROM prospects
  UNION ALL
  SELECT 'attendee', email FROM event_attendees
) AS source_rows
GROUP BY audience
ORDER BY audience;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Using UNION when duplicates are facts you need to keep. Start with UNION ALL for audits and event data.
• Combining different column counts or mismatched meanings just because the data types fit.
• Forgetting that EXCEPT depends on input order.
• Adding ORDER BY to a middle query rather than the complete set-operation result.
• Using a set operation when a join is needed to bring related columns into one row.

Independent lab: publish a safe campaign list

Publish one alphabetized send list from customers, prospects, and event attendees, but exclude blocked emails. The result should contain Amina, Bilal, Celia, and Elias exactly once. First check each source, then build the distinct combined list, and finally subtract blocked addresses.

CREATE TABLE customers (email TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE prospects (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_attendees (email TEXT NOT NULL, event_name TEXT NOT NULL);
CREATE TABLE blocked_emails (email TEXT NOT NULL);
INSERT INTO customers VALUES ('amina@example.com','Rabat'),('celia@example.com','Fes'),('dina@example.com','Rabat');
INSERT INTO prospects VALUES ('amina@example.com','guide'),('bilal@example.com','webinar'),('dina@example.com','demo');
INSERT INTO event_attendees VALUES ('bilal@example.com','SQL night'),('celia@example.com','SQL night'),('elias@example.com','SQL night');
INSERT INTO blocked_emails VALUES ('dina@example.com');
-- Build a distinct send list from all three sources, then remove blocked emails.
SELECT email FROM (
  SELECT email FROM customers
  UNION
  SELECT email FROM prospects
  UNION
  SELECT email FROM event_attendees
)
EXCEPT
SELECT email FROM blocked_emails
ORDER BY email;

Edit the query, predict the rows it will return, then run it.

Lesson review

Set operations answer list questions. Define the meaning and shape of each input, inspect the inputs independently, decide whether duplicates are noise or facts, and then choose UNION, UNION ALL, INTERSECT, or EXCEPT based on the precise relationship between the lists.

• I can state what one output row means before choosing a set operation.
• I can explain the difference between stacking rows with a set operation and combining columns with a join.
• I can make inputs compatible by selecting the same number of meaningful columns.
• I can choose between UNION and UNION ALL deliberately.
• I can use INTERSECT for shared rows and directional EXCEPT for subtraction.
• I can audit and normalize source values before a distinct output hides important details.