Set Operations

Most SQL queries work with a single table or join several tables together. But sometimes the question you want to answer is inherently about combining or comparing two separate result sets: "Give me all the rows from query A plus all the rows from query B", or "Give me only the rows that appear in both results", or "Give me rows from query A that are missing from query B." This is exactly what set operations do.

SQL gives you three operators for this: UNION, INTERSECT, and EXCEPT. They treat each SELECT statement as a set of rows and perform the corresponding mathematical set operation on the results. Knowing when to reach for these operators — instead of a complex join or nested subquery — leads to cleaner, more expressive queries.

What You'll Learn

By the end of this lesson you will answer "who is on both lists?" and "who is on this list and not that one?" across two tables, with one query per question and no join in sight. The skill that makes those queries trustworthy is knowing exactly how many rows each one returns before you run it, because every one of these operators except UNION ALL removes duplicates silently — and a row count that drops for a reason you did not intend is the commonest way a reconciliation report goes quietly wrong.

This lesson has no milestone of its own, but the warehouse capstone does use one of these operators, and it is worth knowing which and why. After seeding its five tables it runs a verification query that stacks five COUNT(*) branches with UNION ALL — one per table, each labelled with the table's name — so a single result set reports every row count side by side and a wrong number is visible at a glance. The label on each branch is doing more work than it looks: it names the row for the reader, and because it differs on every branch it also guarantees no two rows can ever be identical, which is what makes the choice between UNION and UNION ALL moot there. Drop those labels and the difference bites immediately — two tables holding the same number of rows produce the same single-column row, and plain UNION keeps only one of them, silently turning five counts into four. You arrive able to select columns from a table and filter and sort them (Selecting Data, and Filtering and Sorting); what is new is that a whole SELECT can become one operand of a larger expression, and that the rules governing what may be combined are about column count and position rather than about the tables involved.

The Rules

Before diving in, every set operation has one hard requirement and one soft one:

1. Same number of columns — both SELECT statements must return the same number of columns. This one is enforced, and breaking it is one of the few errors set operations produce. See the demonstration below.
2. Corresponding columns are matched by position, not by name. The first column of the top branch is paired with the first column of the bottom branch whatever they are called, and the output takes its names from the first branch.

Notice what is not on that list: matching types. SQLite will happily combine a text column with a numeric one, so this succeeds and returns two rows:

SELECT 'abc' AS v
UNION
SELECT 42
ORDER BY v;

The result puts 42 before abc, because on this engine values of different storage classes have a defined order — numbers sort ahead of text. That is a SQLite behaviour and other engines are stricter about it, so pairing a text column with a numeric one is still a mistake worth catching; it simply is not a mistake this engine will catch for you.

The mismatch it does refuse is the column count. The fence below is deliberately broken — two columns above the operator, one below. Open it with its Open in Playground pill and press Run Code: no rows appear, because the engine rejects the statement while preparing it and never reaches the point of reading any, and the message arrives on the error stream instead:

SELECT 1 AS a, 2 AS b
UNION
SELECT 3;

Error: SELECTs to the left and right of UNION do not have the same number of result columns

Note where that check happens. The column count is a property of the statement's shape rather than of the data, so it is settled before a single row is read — which makes this one of the few set-operation mistakes you find out about immediately rather than from a wrong answer arriving quietly. Note also what the message does not say: it tells you the two sides disagree, not which side you meant, so on a long query the fix still starts with counting the columns yourself.

That is the whole of the type-and-shape rulebook: count enforced, types not. Any ORDER BY clause applies to the combined result and must go at the very end, after all the branches.

UNION and UNION ALL

UNION merges two result sets into one, automatically removing duplicate rows. UNION ALL does the same merge but keeps every row, including duplicates. If you know there are no duplicates — or you deliberately want to preserve them — reach for UNION ALL: it does not have to compare rows against each other to find duplicates, which is work UNION cannot avoid. (How much time that saves depends on the data and the engine; on the few-row tables in this lesson it is not measurable, and you should not claim a speed-up you have not measured on your own data.)

-- Combine two separate sales channels into a single report
CREATE TABLE online_orders (
    order_id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product  TEXT NOT NULL,
    amount   REAL NOT NULL
);

CREATE TABLE store_orders (
    order_id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product  TEXT NOT NULL,
    amount   REAL NOT NULL
);

INSERT INTO online_orders VALUES
    (1, 'Alice', 'Keyboard', 89.99),
    (2, 'Bob',   'Monitor',  349.00),
    (3, 'Carol', 'Keyboard', 89.99);

INSERT INTO store_orders VALUES
    (1, 'David', 'Mouse',   29.99),
    (2, 'Alice', 'Headset', 79.00),
    (3, 'Bob',   'Monitor', 349.00);

-- UNION removes exact duplicate rows across both sets
SELECT customer, product, amount, 'online' AS channel
FROM online_orders
UNION
SELECT customer, product, amount, 'store'
FROM store_orders
ORDER BY customer;

Notice that every row here carries a channel column, so no two rows are truly identical even if the same customer bought the same product in both places. Deduplication only kicks in when every column in a row matches exactly.

Two changes to make to that fence — open it with its Open in Playground pill, then edit and Run Code — predicting each before you run it. First, drop the channel column from both branches, leaving SELECT customer, product, amount: the result falls from six rows to five, because Bob's Monitor at 349.0 now appears identically in both branches and UNION keeps one copy. That single vanishing row is the whole behaviour. Second, put the column back and change UNION to UNION ALL: you are back to six rows, and now removing the channel column changes nothing at all, because UNION ALL never compares rows to each other. Between those two runs you have seen the only difference between the operators.

CREATE TABLE web_signups (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_signups (email TEXT NOT NULL, source TEXT NOT NULL);
INSERT INTO web_signups VALUES ('ada@example.com','web'),('bo@example.com','web'),('ada@example.com','web');
INSERT INTO event_signups VALUES ('cy@example.com','event'),('bo@example.com','event');
SELECT email FROM web_signups
UNION
SELECT email FROM event_signups
ORDER BY email;

CREATE TABLE web_signups (email TEXT NOT NULL, source TEXT NOT NULL);
CREATE TABLE event_signups (email TEXT NOT NULL, source TEXT NOT NULL);
INSERT INTO web_signups VALUES ('ada@example.com','web'),('bo@example.com','web'),('ada@example.com','web');
INSERT INTO event_signups VALUES ('cy@example.com','event'),('bo@example.com','event');
SELECT email FROM web_signups
UNION
SELECT email FROM event_signups
ORDER BY email;

3 row(s) affected
2 row(s) affected
email
-----
ada@example.com
ada@example.com
bo@example.com
bo@example.com
cy@example.com

INTERSECT

INTERSECT returns only the rows that appear in both result sets. Think of it as finding the overlap — the products that exist in both catalogs, the customers who show up on two different lists.

-- Find products sold through BOTH the online store and the physical store
CREATE TABLE online_products (name TEXT PRIMARY KEY, price REAL);
CREATE TABLE store_products  (name TEXT PRIMARY KEY, price REAL);

INSERT INTO online_products VALUES
    ('Keyboard', 89.99),
    ('Monitor',  349.00),
    ('Webcam',   59.99),
    ('Headset',  79.00);

INSERT INTO store_products VALUES
    ('Monitor',  349.00),
    ('Headset',  79.00),
    ('Desk Lamp', 34.99),
    ('Mouse',    29.99);

-- Only products carried by both channels
SELECT name, price FROM online_products
INTERSECT
SELECT name, price FROM store_products
ORDER BY name;

INTERSECT is a readable alternative to a subquery using IN. The equivalent WHERE name IN (SELECT name FROM store_products) works, but the INTERSECT version makes the set-theory intent immediately visible to anyone reading the query later.

INTERSECT deduplicates too, and this is easy to miss because UNION gets all the attention. If a value appears three times in the left branch and twice in the right, the result contains it exactly once. That matters whenever you are counting rather than listing: "how many orders appear in both systems" is not a question INTERSECT can answer, because it has collapsed the repeats before you can count them. And unlike UNION, there is no duplicate-preserving variant to reach for: this engine has no INTERSECT ALL — writing one gives Error: near "ALL": syntax error.

EXCEPT

EXCEPT returns rows that are in the first result set but not in the second. It is the set difference: "everything in A that is not in B." It deduplicates on the same terms as the other two: a row appearing four times on the left and never on the right comes back once.

Other engines spell this operator differently. Oracle's SQL language reference states that "the EXCEPT operator is a synonym for MINUS and has the exact same semantics" (Oracle Database SQL Language Reference: the UNION ALL, INTERSECT and MINUS operators), so an Oracle query may use either name. SQLite knows only EXCEPT: writing MINUS in place of it here is a syntax error. Do not read too much into which word the message names — swap MINUS into the query below and the error reports near "SELECT", pointing at the word after it rather than at the offending one, because the parser only discovers the problem once it reaches the token it cannot reconcile. That is a general habit worth carrying: a syntax error names where the parser gave up, which is often just past where you went wrong. The portability runs in one direction only — EXCEPT travels to Oracle, MINUS does not travel here.

-- Find products available online but NOT stocked in the physical store
CREATE TABLE online_items (name TEXT, category TEXT);
CREATE TABLE store_items  (name TEXT, category TEXT);

INSERT INTO online_items VALUES
    ('Keyboard',  'Peripherals'),
    ('Monitor',   'Displays'),
    ('Webcam',    'Peripherals'),
    ('Headset',   'Audio'),
    ('USB Hub',   'Accessories');

INSERT INTO store_items VALUES
    ('Monitor',  'Displays'),
    ('Headset',  'Audio'),
    ('Desk Lamp','Lighting'),
    ('Mouse',    'Peripherals');

-- Items the online store carries that the physical store does not
SELECT name, category FROM online_items
EXCEPT
SELECT name, category FROM store_items
ORDER BY name;

The order of the two branches matters with EXCEPT. Swap them and you get the opposite result: items the store stocks that are not available online. This asymmetry is what distinguishes EXCEPT from INTERSECT, which is symmetric — swapping the branches gives the same answer.

Where the Clauses Go in a Set Operation

A set operation is not two queries next to each other; it is one statement, and the clause positions follow from that. A WHERE belongs to the branch it sits in and filters only that branch's rows. An ORDER BY belongs to the whole statement, so there is exactly one of them and it goes at the very end — after the last branch, not after the first. The five lines below are one query over these two tables:

CREATE TABLE trial_users (email TEXT PRIMARY KEY);
CREATE TABLE paid_users  (email TEXT PRIMARY KEY);
INSERT INTO trial_users VALUES ('ada@x.com'),('bo@x.com'),('cy@x.com');
INSERT INTO paid_users  VALUES ('bo@x.com'),('di@x.com');

Try It Yourself

A company tracks newsletter subscribers and paying customers in separate tables, keyed by email. Two questions need answering: who is on both lists, and who subscribed but never bought anything. This is a build task. The starter below runs and prints a list, but it is the wrong list — it reports every subscriber and consults the customers table not at all. Work the two TODOs so it answers both questions, as two result sets one after the other.

CREATE TABLE subscribers (email TEXT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE customers (email TEXT PRIMARY KEY, name TEXT NOT NULL, spent_cents INTEGER NOT NULL);
INSERT INTO subscribers VALUES
('alice@example.com','Alice'),('bob@example.com','Bob'),('carol@example.com','Carol'),('dan@example.com','Dan'),('eva@example.com','Eva');
INSERT INTO customers VALUES
('bob@example.com','Bob',25000),('carol@example.com','Carol',8950),('frank@example.com','Frank',41000),('eva@example.com','Eva',7499);
-- TODO 1: replace the query below with one that returns the people on BOTH lists,
--         as email and name, sorted by email. Bob, Carol and Eva qualify; Frank does not,
--         because he is a customer who never subscribed.
-- TODO 2: add a SECOND query after it returning the subscribers who are NOT customers,
--         as email and name, sorted by email. Alice and Dan qualify.
SELECT email, name FROM subscribers ORDER BY email;

5 row(s) affected
4 row(s) affected
email | name
------------
bob@example.com | Bob
carol@example.com | Carol
eva@example.com | Eva
email | name
------------
alice@example.com | Alice
dan@example.com | Dan

Before you submit the version that passes, try two variations on it in the block's editor — an unsolved run is never limited, and each one shows you its output beside the expected lines. Predict each before you run it, then put the passing version back and run that last. First, swap the branch order in the second query to customers EXCEPT subscribers: the answer changes from Alice and Dan to Frank alone, because you have asked the mirror-image question. Second, change the first query's operator from INTERSECT to UNION: you go from three rows to six, and the result now includes Frank and everyone else — a reminder that these three operators differ only in which rows they keep, so choosing the wrong one produces a perfectly well-formed answer to a question you did not ask.

CREATE TABLE subs (email TEXT PRIMARY KEY);
CREATE TABLE custs (email TEXT);
INSERT INTO subs VALUES ('a@x'),('b@x'),('c@x');
INSERT INTO custs VALUES ('b@x'),('d@x');
SELECT email FROM subs EXCEPT SELECT email FROM custs ORDER BY email;
SELECT s.email FROM subs s WHERE s.email NOT IN (SELECT email FROM custs) ORDER BY s.email;
SELECT s.email FROM subs s LEFT JOIN custs c ON c.email = s.email WHERE c.email IS NULL ORDER BY s.email;

Key Takeaways

• UNION combines two result sets and removes duplicate rows — including duplicates that were already repeated within one branch; UNION ALL keeps every row. Prefer UNION ALL whenever the repetition is data, as it is in any audit, stack or reconciliation.
• INTERSECT returns only the rows that exist in both result sets and is the most readable way to express "find the overlap between two queries." It deduplicates as well, and this engine has no INTERSECT ALL to opt out of that.
• EXCEPT returns rows from the first result set that do not appear in the second — order matters; swapping the branches produces the inverse result. It deduplicates too.
• All set operations require the same number of columns, and pair them by position rather than by name; column names in the output always come from the first SELECT branch. SQLite does not require matching types — combining text with a number succeeds here — so that is a mistake to catch by reading, not one the engine reports.
• The three operators compare whole rows by distinctness, not by =, so two NULLs in the same column count as the same value for deduplication — the opposite of how NULL behaves in a WHERE clause.
• A single ORDER BY clause goes at the very end of the entire combined statement and sorts the final merged result.
• Set operations can replace correlated subqueries and IN / NOT IN patterns in many common scenarios, often producing queries that are easier to read and reason about.

Pro Tip: When you need to understand why rows appear in one set but not another, add a label column to each branch — 'online' AS source — and switch from EXCEPT to UNION ALL. Instead of just seeing what is missing, you get a combined view of both sets side by side, which makes discrepancies immediately visible and much easier to debug.

Next Steps

With set operations in your toolkit, the next lesson dives into data types and casting — understanding how SQL stores values and how to convert between types safely to avoid subtle bugs.