Set Operations — UNION, INTERSECT, EXCEPT

Set operation — UNION, INTERSECT, EXCEPT

Read: ~25 min Easy 12 practice problems Live SQLite runner

1. SQL Was Built on Set Theory

Edgar F. Codd's 1970 relational model was, fundamentally, an application of set theory to data. A table is a set of rows; a query result is a new set. SQL's set operations — UNION, INTERSECT, and EXCEPT — are the direct SQL equivalents of union (∪), intersection (∩), and difference (−) from the math you learned in school.

E. F. Codd-এর ১৯৭০-এর relational model আসলে set theory-র ওপর ভিত্তি করে তৈরি। একটি table মানে row-এর একটি set, query-র ফলাফলও একটি set। তাই SQL-এ set operation তিনটি — UNION (∪), INTERSECT (∩), EXCEPT (−) — গণিতের set-এর ক্রিয়াগুলোর সরাসরি অনুবাদ।

Figure 18.1 — UNION, INTERSECT ও EXCEPT-এর ভেন (Venn) ডায়াগ্রাম।

Key difference from JOIN JOIN combines tables horizontally (a row from A is glued to a row from B to form a wider row). Set operations combine vertically — the two query results are stacked, and rows must match in column count and type.

JOIN দু-টি table-কে পাশাপাশি জোড়া দেয় (row চওড়া হয়); set operation দু-টি query-র ফলাফলকে উপরে-নিচে গাঁথে (row-এর সংখ্যা বাড়ে)। তাই column-count ও type মিলতে হয়।

2. UNION vs UNION ALL — The Speed Difference

UNION stacks two result sets and removes duplicates. UNION ALL stacks them without removing duplicates. The latter is dramatically faster on large datasets because the database does not have to sort/hash all rows to find duplicates.

UNION দু-টি result-কে একসাথে বসায় এবং duplicate বাদ দেয়। UNION ALL duplicate বাদ দেয় না — তাই অনেক দ্রুত। যদি আপনি জানেন duplicate আসবে না, সবসময় UNION ALL ব্যবহার করুন।

union.sql

-- UNION removes duplicates: Rafi appears once
SELECT name FROM online_customers
UNION
SELECT name FROM store_customers
ORDER BY name;

-- UNION ALL keeps duplicates: Rafi appears twice
SELECT name FROM online_customers
UNION ALL
SELECT name FROM store_customers
ORDER BY name;

Aspect	UNION	UNION ALL
Removes duplicates?	Yes	No
Cost	Sort or hash to deduplicate	Just stack — O(n+m)
Use when	You actually want a set semantics	You know inputs are disjoint, or duplicates are wanted
Bengali	duplicate বাদ দেয়	সব row রাখে, দ্রুত

Real-world tip Most analytics queries that combine "today's data" + "yesterday's data" are inherently disjoint by date — use UNION ALL. The performance gap on millions of rows can be 5×–10×.

"আজকের data" + "গতকালের data" — যেহেতু তারিখ আলাদা, duplicate কখনও আসবে না। তাই UNION ALL ব্যবহার করলে query অনেক দ্রুত হবে।

3. INTERSECT — Rows Common to Both Sides

INTERSECT returns rows that appear in both query results. It also deduplicates by default. SQLite supports it directly; some other engines provide an explicit INTERSECT ALL variant.

INTERSECT দু-টি query-র ফলাফলে যেসব row একসাথে আছে, শুধু সেগুলোই ফেরত দেয়। duplicate বাদ যায়। গণিতের A ∩ B-এর ঠিক equivalent।

intersect.sql

-- Customers who shop both online AND in-store
SELECT id, name FROM online_customers
INTERSECT
SELECT id, name FROM store_customers
ORDER BY id;

INTERSECT compares rows column-by-column. For two rows to be considered equal, every selected column must match. If you only want to compare on, say, id, only put id in the SELECT lists.

4. EXCEPT — A − B (Sometimes Called MINUS)

EXCEPT returns rows that appear in the first query but not in the second. Oracle calls this MINUS — exact same operation, different keyword.

EXCEPT বাঁ-পাশের query-তে আছে কিন্তু ডান-পাশে নেই — এমন row ফিরিয়ে দেয়। Oracle-এ এটিকে MINUS বলা হয় (একই কাজ, ভিন্ন keyword)।

except.sql

-- Online-only customers (online minus in-store)
SELECT id, name FROM online_customers
EXCEPT
SELECT id, name FROM store_customers
ORDER BY id;

EXCEPT is asymmetric Unlike UNION and INTERSECT, EXCEPT is not commutative: A EXCEPT B ≠ B EXCEPT A. Always read it as "A minus what is also in B."

EXCEPT-এ order গুরুত্বপূর্ণ। A EXCEPT B মানে "A থেকে B-তে থাকা row বাদ দাও" — উল্টে দিলে অর্থ পাল্টে যাবে।

5. Compatibility Rules — What Must Match?

For any set operation between two queries, both sides must satisfy strict compatibility rules. These come from the underlying set-algebra: you cannot meaningfully take the union of "shapes" of different shapes.

যেকোনো set operation চালাতে হলে দু-পাশের query-র structure মিলতে হবে — column-এর সংখ্যা এক, type মিলযোগ্য, এবং পজিশন অনুযায়ী মান। নাম একই হওয়া বাধ্যতামূলক নয়, কিন্তু row-এর "আকৃতি" অভিন্ন থাকতে হবে।

Same number of columns in each SELECT.
Compatible types column-by-column (numeric ↔ numeric, text ↔ text). SQLite's flexible typing makes this lenient — but don't rely on it.
Column names come from the first query. Aliases on the second side are ignored.
An overall ORDER BY applies to the combined result and must come at the very end. LIMIT the same way.

compat.sql

-- Column NAMES come from the first query (id, name, salary)
SELECT id, name, salary FROM staff
UNION ALL
SELECT num, person, stipend FROM intern
ORDER BY salary DESC;

Try changing the number of columns on either side and re-running — SQLite will refuse with a clear error.

6. Ordering, Parentheses & Precedence

When you chain three or more set operations, the precedence is: INTERSECT binds tighter than UNION and EXCEPT. To avoid surprises, always use parentheses.

একাধিক set operation একসাথে ব্যবহার করলে INTERSECT-এর priority বেশি — অন্য দু-জনের চেয়ে আগে evaluate হয়। বিভ্রান্তি এড়াতে সবসময় parenthesis দিন।

precedence.sql

-- Implicit precedence: INTERSECT first → A UNION (B INTERSECT C)
SELECT x FROM a
UNION
SELECT x FROM b
INTERSECT
SELECT x FROM c
ORDER BY x;

-- Explicit (A UNION B) INTERSECT C — different result!
SELECT x FROM (
    SELECT x FROM a
    UNION
    SELECT x FROM b
)
INTERSECT
SELECT x FROM c;

The first query yields {1,2,3,4} ∪ ({2,3} ∩ {3,5}) = {1,2,3,4} ∪ {3} = {1,2,3,4}. The second yields ({1,2,3,4} ∪ {2,3}) ∩ {3,5} = {1,2,3,4} ∩ {3,5} = {3}.

ORDER BY position Only one ORDER BY is allowed, and it must be on the final query in the chain. It applies to the combined result, not to either input.

পুরো chain-এর শেষে একটি মাত্র ORDER BY চলে — মাঝের কোনো query-তে আলাদা ORDER BY দেওয়া যাবে না।

7. Common Practical Patterns

7.1 — "Symmetric difference" (rows in exactly one side)

SQL has no SYMMETRIC DIFFERENCE keyword, but it is just (A − B) ∪ (B − A):

symdiff.sql

SELECT x FROM a EXCEPT SELECT x FROM b
UNION
SELECT x FROM b EXCEPT SELECT x FROM a
ORDER BY x;

7.2 — Merging history tables for reporting

Old transactions in archive + recent in live → combined timeline:

merge_history.sql

SELECT t, msg, 'archive' AS src FROM archive
UNION ALL
SELECT t, msg, 'live'    FROM live
ORDER BY t;

7.3 — Detecting drift between two snapshots

Schema audit: which tables exist on prod but not on staging?

drift.sql

SELECT name AS prod_only FROM prod_tables
EXCEPT
SELECT name            FROM staging_tables;

উপরের তিনটি pattern বাস্তবে অহরহ লাগে — symmetric difference, history merge, ও schema drift detection।

8. When to Reach for Set Ops vs JOIN / EXISTS

✅ Use Set Ops (কখন ব্যবহার করবেন)

Inputs already have the same shape
You think in set terms (∪, ∩, −)
Combining multiple history tables
Comparing whole rows across snapshots

⚠️ Prefer JOIN / EXISTS (কখন বরং JOIN ভালো)

Result needs columns from both sides
Filter is per-row not per-set
One side is small and used for lookup
You want to keep duplicates with full context

9. Practice Problems

12 problems covering UNION (ALL), INTERSECT, EXCEPT, and chaining patterns.

১২টি অনুশীলনী — UNION (ALL), INTERSECT, EXCEPT এবং chaining। প্রতিটির runnable answer আছে।

List every distinct customer name across two channels.

দু-চ্যানেলের সব unique customer name।
✨ Show Answer
a1.sql
```
SELECT name FROM web
UNION
SELECT name FROM app
ORDER BY name;
```
List ALL names (with duplicates) across two channels.

duplicate-সহ সব name।
✨ Show Answer
a2.sql
```
SELECT name FROM web
UNION ALL
SELECT name FROM app
ORDER BY name;
```
Find customers who use BOTH the web and the app.

যারা web ও app উভয়েই ব্যবহার করে।
✨ Show Answer
a3.sql
```
SELECT name FROM web
INTERSECT
SELECT name FROM app;
```
Find customers who use the web but NOT the app.

শুধু web ব্যবহারকারী।
✨ Show Answer
a4.sql
```
SELECT name FROM web
EXCEPT
SELECT name FROM app;
```
Symmetric difference: customers who use exactly one channel.

যারা শুধু একটিমাত্র চ্যানেল ব্যবহার করে।
✨ Show Answer
a5.sql
```
SELECT name FROM web EXCEPT SELECT name FROM app
UNION
SELECT name FROM app EXCEPT SELECT name FROM web
ORDER BY name;
```
Combine archive and live transactions chronologically with a source-tag column.

archive + live transaction একসাথে — সাথে source-এর tag।
✨ Show Answer
a6.sql
```
SELECT t,amount,'archive' AS src FROM archive
UNION ALL
SELECT t,amount,'live'    FROM live
ORDER BY t;
```
Find tables present on prod but missing from staging (schema drift).

prod-এ আছে, staging-এ নেই — এমন table।
✨ Show Answer
a7.sql
```
SELECT t FROM p
EXCEPT
SELECT t FROM s;
```
Why is UNION ALL usually faster than UNION? Explain in 2 sentences.

UNION-এর তুলনায় UNION ALL কেন দ্রুত — ব্যাখ্যা।

✨ Show Answer

Answer: UNION must remove duplicates, which forces the engine to sort or hash the entire combined result before returning it. UNION ALL simply concatenates the two streams — no extra work, often O(n+m) with constant memory.

UNION-এ duplicate বাদ দিতে পুরো result-কে sort/hash করতে হয়। UNION ALL-এ শুধু দু-টি stream পাশাপাশি জুড়ে দেওয়া — কাজ অনেক কম।
Combine three category lists, removing duplicates, and order alphabetically.

তিনটি category-list union করে duplicate বাদ দিন।
✨ Show Answer
a9.sql
```
SELECT c FROM c1
UNION SELECT c FROM c2
UNION SELECT c FROM c3
ORDER BY c;
```
Find product codes available in 2024 but discontinued in 2025.

২০২৪-এ ছিল কিন্তু ২০২৫-এ নেই — এমন product code।
✨ Show Answer
a10.sql
```
SELECT code FROM p2024
EXCEPT
SELECT code FROM p2025;
```
Build a bKash report: top-up senders OR receivers (any participant), de-duplicated.

bKash-এ যেকোনো রূপে অংশ নেওয়া (sender বা receiver) সব phone।
✨ Show Answer
a11.sql
```
SELECT sender AS phone FROM tx
UNION
SELECT receiver           FROM tx
ORDER BY phone;
```
Why must both sides of a set operation have the same number of columns?

দু-পাশের column-সংখ্যা সমান কেন হতে হবে?

✨ Show Answer

Answer: Set operations compare and combine rows. A row with three columns and a row with five columns are not the same shape — there is no consistent way to decide if they are "the same row" or how to merge them. SQL therefore enforces matching column counts, and column types must be compatible position-by-position.

দু-টি ভিন্ন আকৃতির row-এর মধ্যে "একই row কি না" সিদ্ধান্ত নেওয়া অসম্ভব। তাই column-সংখ্যা ও type অবশ্যই মিলতে হবে।

Summary — Module 18

SQL's three set operations come straight from set theory. UNION takes the union (distinct), UNION ALL stacks without dedup (faster), INTERSECT takes the common rows, and EXCEPT (alias MINUS) takes the difference. Both sides must agree on column count and compatible types; column names come from the first query; ORDER BY goes only at the very end and applies to the combined result. INTERSECT binds tighter than UNION/EXCEPT — use parentheses to be explicit.

UNION (distinct), UNION ALL (দ্রুত, duplicate রাখে), INTERSECT (common), EXCEPT (difference) — চারটি operation। দু-পাশে column-সংখ্যা ও type মিলতে হবে; column-নাম প্রথম query থেকে নেওয়া হয়; ORDER BY শুধু একদম শেষে। বড় data-তে UNION ALL অনেক দ্রুত।

Next Module → Views, CTEs & Recursive Queries — পুনঃব্যবহারযোগ্য ও পঠনযোগ্য SQL।

Previous Next: Views & CTEs