UNION, INTERSECT ও EXCEPT

জয়েন টেবিলগুলোকে পাশাপাশি বসায়: কলাম বাড়ে। সেট অপারেশন (set operation) কোয়েরির ফলাফলগুলোকে একটার নিচে আরেকটা বসায়, বা তুলনা করে: কলাম একই থাকে, সারি বাড়ে (বা কমে)। UNION দুটো ফলাফল ওপর-নিচে জুড়ে দেয়, INTERSECT রাখে যে সারিগুলো দুটোতেই আছে, EXCEPT রাখে প্রথমটার সেই সারিগুলো যা দ্বিতীয়টায় নেই। এগুলো সরাসরি এসেছে Maths for AI-তে দেখা সেটের গণিত থেকে: ইউনিয়ন, ইন্টারসেকশন, ডিফারেন্স।

এক লাইনেই এরা ডেটার বেশ কিছু কাজের প্রশ্নের উত্তর দেয়: কোন কাস্টমাররা পরের কোয়ার্টারে ফিরে এসেছেন (রিটেনশন), কারা আসেননি (মডেলের জন্য চার্ন লেবেল), আর — মেশিন লার্নিংয়ের সবচেয়ে জরুরি যাচাইগুলোর একটা — টেস্টের কোনো উদাহরণ ট্রেনিং ডেটাতেও আছে কিনা।

যা শিখবেন

• UNION বনাম UNION ALL, আর কখন কোনটা ঠিক
• INTERSECT আর EXCEPT (Oracle-এ MINUS; MySQL-এ 8.0.31 থেকে)
• নিয়মগুলো: কলামের সংখ্যা সমান, টাইপ মানানসই, নাম আসে প্রথম কোয়েরি থেকে
• জোড়া লাগানো ফলাফলে ORDER BY আর LIMIT
• UNION বনাম JOIN: ওপর-নিচে জোড়া (সারি বাড়ে) বনাম পাশাপাশি জোড়া (কলাম বাড়ে)

ওপর-নিচে জোড়া বনাম পাশাপাশি জোড়া

দুটো ছোট ফলাফল নিন: যে কাস্টমাররা প্রথম কোয়ার্টারে (জানুয়ারি–মার্চ) অর্ডার করেছেন, আর যারা দ্বিতীয়টায় (এপ্রিল–জুন)।

জয়েন করলে হতো সম্পূর্ণ অন্য জিনিস: সারি মিলিয়ে তাদের কলামগুলো পাশাপাশি বসানো। সেট অপারেশন পুরো সারি ধরে মেলায় আর কলাম একই রাখে:

JOIN  (widen):   [order_id | customer_id]  +  [customer_id | name]
                 →  [order_id | customer_id | name]        same rows, more columns

UNION (stack):   [customer_id]   (Q1 rows)
                 [customer_id]   (Q2 rows)
                 →  [customer_id]                          same columns, more rows

UNION আর UNION ALL

SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
UNION
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
|           4 |
|           5 |
|           6 |
|           7 |
+-------------+

দুটো কোয়েরি দেয় ৮টা আর ৬টা সারি (প্রতি অর্ডারে একটা), অথচ ফলাফলে ৭টা। UNION ডুপ্লিকেট সারি মুছে দেয় — দুই ইনপুটের মধ্যে এবং প্রতিটার ভেতরেও। UNION ALL সব রেখে দেয়:

SELECT COUNT(*) AS union_all_rows
FROM (SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
      UNION ALL
      SELECT customer_id FROM orders WHERE order_date >= '2026-04-01') AS t;

+----------------+
| union_all_rows |
+----------------+
|             14 |
+----------------+

ডিফল্ট হিসেবে UNION ALL নিন, আর UNION বাছুন জেনেবুঝে। দুই মাসের বিক্রি জুড়তে UNION দিলে দুটো হুবহু এক বিক্রি চুপচাপ এক হয়ে যায়, আর মোট অঙ্ক কম আসে।

আলাদা টেবিল জুড়ে একটা লম্বা টেবিল

সেট অপারেশনে দুই দিকে একই টেবিল লাগে না, লাগে শুধু একই গড়ন (একই সংখ্যক কলাম, একই ক্রমে)। এখানে অর্ডার আর পেমেন্ট মিলিয়ে অর্ডার ৬-এর একটা ইভেন্ট টাইমলাইন বানানো হয়েছে, সাথে একটা লেবেল কলাম, যা বলে দেয় কোন সারি কোথা থেকে এসেছে:

SELECT order_id, order_date AS event_date, 'order placed' AS event, NULL AS amount
FROM orders
WHERE order_id = 6
UNION ALL
SELECT order_id, paid_on, 'payment (' || method || ')', amount
FROM payments
WHERE order_id = 6
ORDER BY event_date, event;

+----------+------------+-----------------+--------+
| order_id | event_date | event           | amount |
+----------+------------+-----------------+--------+
|        6 | 2026-03-08 | order placed    | NULL   |
|        6 | 2026-03-08 | payment (bkash) |   5000 |
|        6 | 2026-03-10 | payment (card)  |  10000 |
+----------+------------+-----------------+--------+

• কলামের নাম আসে প্রথম কোয়েরি থেকে (event_date, event); দ্বিতীয় কোয়েরির নাম উপেক্ষা করা হয়।
• প্রথম টেবিলে amount কলাম নেই, তাই NULL AS amount দিয়ে জায়গাটা ভরা হয়েছে, যাতে দুই দিকেই চারটা করে কলাম থাকে।
• জোড়া লাগানোর পর কোন সারি কোন উৎসের, সেটা ধরে রাখার উপায় এই ফিক্সড লেবেল। এমন "লম্বা" ইভেন্ট টেবিল সিকোয়েন্স ফিচার আর ইউজার-অ্যাক্টিভিটি লগের খুব প্রচলিত ইনপুট। (pandas-এ একই কাজ pd.concat; UNION মানে concat-এর পর drop_duplicates।)

INTERSECT আর EXCEPT

যে কাস্টমাররা দুই কোয়ার্টারেই অর্ডার করেছেন:

SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+

আর যারা Q1-এ অর্ডার করেছেন কিন্তু Q2-তে নয়। EXCEPT-এর ফলাফল আইডির একটা তালিকা, তাই নাম আনতে এটাকে একটা IN সাবকোয়েরিতে বসানো যায়:

SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (
    SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
    EXCEPT
    SELECT customer_id FROM orders WHERE order_date >= '2026-04-01')
ORDER BY c.customer_id;

+-------------+-----------------+
| customer_id | name            |
+-------------+-----------------+
|           4 | Rafiq Islam     |
|           5 | Sadia Chowdhury |
+-------------+-----------------+

EXCEPT-এ ক্রম গুরুত্বপূর্ণ: A EXCEPT B আর B EXCEPT A এক নয় (দ্বিতীয়টা দেয় নতুন কাস্টমার ৬ আর ৭)। চার্ন লেবেলের কাঁচামাল ঠিক এটাই: "গত পিরিয়ডে সক্রিয়, এই পিরিয়ডে নিষ্ক্রিয়"। UNION-এর মতো INTERSECT আর EXCEPT-ও ডুপ্লিকেট বাদ দিয়ে (distinct) সারি ফেরত দেয়।

ডায়ালেক্ট

SQLite আর PostgreSQL অনেক দিন ধরেই INTERSECT আর EXCEPT সমর্থন করে। MySQL এগুলো যোগ করেছে 8.0.31-এ (২০২২); পুরোনো MySQL-এ এর বদলে IN / NOT EXISTS লিখতে হয়। Oracle প্রথাগতভাবে EXCEPT-কে বলে MINUS (21c সংস্করণ দুটোই নেয়)। MySQL 8.0.31 বা তার নতুন সংস্করণে একই কোয়েরি কোনো বদল ছাড়াই চলে:

-- MySQL
SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
INTERSECT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;
SELECT customer_id FROM orders WHERE order_date < '2026-04-01'
EXCEPT
SELECT customer_id FROM orders WHERE order_date >= '2026-04-01'
ORDER BY customer_id;

+-------------+
| customer_id |
+-------------+
|           1 |
|           2 |
|           3 |
+-------------+
+-------------+
| customer_id |
+-------------+
|           4 |
|           5 |
+-------------+

PostgreSQL আর MySQL-এ INTERSECT ALL আর EXCEPT ALL-ও আছে, যা ডুপ্লিকেট মুছে না দিয়ে গুনে হিসাব করে; SQLite-এ আছে শুধু সাধারণ রূপগুলো।

এখানে NULL-কে সমান ধরা হয়

WHERE-এ NULL = NULL সত্য নয়। কিন্তু সেট অপারেশন, DISTINCT আর GROUP BY-এর মতোই, দুটো NULL-কে একই মান ধরে:

SELECT NULL AS x INTERSECT SELECT NULL;

+------+
| x    |
+------+
| NULL |
+------+

এ কারণেই যেখানে NOT IN নিরাপদ ছিল না, সেখানে EXCEPT নিরাপদ। আগের পাতায় Gift Card-এর NULL-এর জন্য NOT IN খালি ক্যাটাগরিটা খুঁজে পায়নি; EXCEPT পায়:

SELECT category_id FROM categories
EXCEPT
SELECT category_id FROM products;

+-------------+
| category_id |
+-------------+
|           5 |
+-------------+

ML-এর একটা যাচাই: টেস্ট সেট কি ট্রেনিংয়ে ঢুকে গেছে?

টেস্ট সেটের কোনো উদাহরণ যদি ট্রেনিং সেটেও থাকে, মডেল উত্তরটা আগেই দেখে ফেলেছে, আর আপনার টেস্ট স্কোর বাস্তবের চেয়ে ভালো দেখায়। প্রম্পটের দুটো টেবিল থাকলে INTERSECT দিয়েই যাচাইটা হয়ে যায়:

CREATE TABLE train_prompts (prompt TEXT NOT NULL);
CREATE TABLE test_prompts (prompt TEXT NOT NULL);
INSERT INTO train_prompts (prompt) VALUES
    ('How do I reset my password?'),
    ('Can I pay with bKash?'),
    ('How do I cancel my order?'),
    ('Do you deliver to Sylhet?');
INSERT INTO test_prompts (prompt) VALUES
    ('Can I pay with bKash?'),
    ('Where is my parcel?'),
    ('how do i reset my password?');
SELECT prompt FROM test_prompts
INTERSECT
SELECT prompt FROM train_prompts;

+-----------------------+
| prompt                |
+-----------------------+
| Can I pay with bKash? |
+-----------------------+

একটা লিক পাওয়া গেল — কিন্তু সেট অপারেশন মান হুবহু মেলায়, আর ছোট হাতের অক্ষরে লেখা পাসওয়ার্ডের প্রশ্নটাও আসলে একই প্রশ্ন। তুলনার আগে দুই দিককে একইভাবে নরমালাইজ করুন:

SELECT LOWER(TRIM(prompt)) AS leaked FROM test_prompts
INTERSECT
SELECT LOWER(TRIM(prompt)) FROM train_prompts
ORDER BY leaked;

+-----------------------------+
| leaked                      |
+-----------------------------+
| can i pay with bkash?       |
| how do i reset my password? |
+-----------------------------+

টেস্টের তিনটা প্রম্পটের দুটোই ট্রেনিংয়ে ছিল। EXCEPT দেয় পরিষ্কার টেস্ট সেট (শুধু "where is my parcel?" টিকে থাকে)। বাস্তবের ডিডুপ্লিকেশন আরও এক ধাপ এগোয় — এমবেডিং দিয়ে প্রায়-ডুপ্লিকেট খোঁজে — কিন্তু হুবহু মেলানোর এই যাচাই সস্তা, আর লোকে যতটা ভাবে তার চেয়ে অনেক বেশি লিক ধরে।

নিয়মগুলো

• প্রতিটা কোয়েরিতে কলামের সংখ্যা সমান হতে হবে; কলাম মেলে অবস্থান ধরে, নাম ধরে নয়।
• প্রতিটা অবস্থানে মানানসই টাইপ। PostgreSQL কড়া, MySQL রূপান্তর করে নেয়, SQLite সবই মেনে নেয় (নিচের ভুলগুলো দেখুন)।
• নাম আসে প্রথম কোয়েরি থেকে। অ্যালিয়াসগুলো সেখানেই দিন।
• একটাই ORDER BY, একদম শেষে; এটা পুরো জোড়া লাগানো ফলাফল সাজায়, ফলাফলের কলামের নাম (বা অবস্থান) দিয়ে। LIMIT-ও তেমনি পুরো ফলাফলে খাটে।
• অগ্রাধিকার: PostgreSQL আর MySQL-এ INTERSECT, UNION আর EXCEPT-এর চেয়ে আগে হিসাব হয়; SQLite সোজা বাঁ থেকে ডানে হিসাব করে। তিন বা তার বেশি অংশ থাকলে ডিরাইভড টেবিল দিয়ে ক্রমটা স্পষ্ট করে দিন।

একটা অংশকে সাজানো বা সীমিত করা

সবচেয়ে সস্তা আর সবচেয়ে দামি প্রোডাক্ট এক ফলাফলে। SQLite-এ একটা অংশের ভেতরে ORDER BY … LIMIT চলে না:

SELECT name, price FROM products ORDER BY price DESC LIMIT 1
UNION ALL
SELECT name, price FROM products ORDER BY price LIMIT 1;

Error: ORDER BY clause should come after UNION ALL not before

প্রতিটা অংশকে একটা ডিরাইভড টেবিলে মুড়ে দিন, যা তিন ডায়ালেক্টেই চলে:

SELECT * FROM (SELECT name, price FROM products ORDER BY price DESC LIMIT 1) AS top_price
UNION ALL
SELECT * FROM (SELECT name, price FROM products ORDER BY price LIMIT 1) AS bottom_price;

+-----------------+-------+
| name            | price |
+-----------------+-------+
| 27-inch Monitor | 18500 |
| Wireless Mouse  |   900 |
+-----------------+-------+

PostgreSQL আর MySQL প্রতিটা অংশকে সাধারণ ব্র্যাকেটেও নেয়:

-- PostgreSQL
(SELECT name, price FROM products ORDER BY price DESC LIMIT 1)
UNION ALL
(SELECT name, price FROM products ORDER BY price LIMIT 1);

+-----------------+-------+
| name            | price |
+-----------------+-------+
| 27-inch Monitor | 18500 |
| Wireless Mouse  |   900 |
+-----------------+-------+

সাধারণ ভুল

• কলামের সংখ্যা আলাদা।
  SQLকপি

  SELECT name, city FROM customers
  UNION
  SELECT name FROM employees;

  আউটপুটকপি

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

  সমাধান: একটা জায়গা-ভরাট মান দিন, যেমন SELECT name, NULL FROM employees, অথবা বাঁ দিকে কম কলাম নিন।
• কলামের ভুল ক্রম। মেলানো হয় অবস্থান দিয়ে, নাম দিয়ে নয়। SQLite খুশিমনে আইডির নিচে একটা নাম বসিয়ে দেয়:
  SQLকপি

  SELECT customer_id, name FROM customers WHERE customer_id = 1
  UNION ALL
  SELECT name, employee_id FROM employees WHERE employee_id = 1;

  আউটপুটকপি

  +-----------------+--------------+
  | customer_id     | name         |
  +-----------------+--------------+
  |               1 | Nadia Rahman |
  | Ayesha Siddiqua |            1 |
  +-----------------+--------------+

  PostgreSQL এটা ধরে ফেলে, আর MySQL কোনো আপত্তি ছাড়াই দুটো কলামকেই টেক্সট বানিয়ে দেয়:
  SQLকপি

  -- PostgreSQL
  SELECT customer_id, name FROM customers WHERE customer_id = 1
  UNION ALL
  SELECT name, employee_id FROM employees WHERE employee_id = 1;

  আউটপুটকপি

  Error: UNION types integer and character varying cannot be matched

  সমাধান: দুই দিকে কলাম একই ক্রমে লিখুন, আর ফলাফলের প্রথম কয়েকটা সারি পড়ে দেখুন।
• UNION ALL দরকার, অথচ লিখেছেন UNION। দুই উৎসের হুবহু এক দুটো বিক্রির সারি এক হয়ে যায়। সমাধান: যে রেকর্ডের প্রতিটা গোনা দরকার, তার জন্য UNION ALL।
• এক্সপ্রেশন দিয়ে সাজানো। UNION-এর পর ORDER BY CASE WHEN label = 'TOTAL' … SQLite-এ ("1st ORDER BY term does not match any column in the result set") আর PostgreSQL-এ ব্যর্থ হয়। সমাধান: প্রতিটা অংশে একটা সাজানোর কলাম দিন (0 AS is_total / 1) আর সেটা দিয়ে সাজান — কঠিন অনুশীলনটা দেখুন।
• কলাম যোগ করতে UNION। কাস্টমারের নাম যদি তাঁর অর্ডারের পাশে চান, সেটা জয়েনের কাজ, ইউনিয়নের নয়।

নিজে চেষ্টা করুন

1. সহজ: EXCEPT দিয়ে কখনো বিক্রি না হওয়া প্রোডাক্টের আইডিগুলো দেখান।
2. মাঝারি: একটা UNION দিয়ে "ঝুঁকিতে থাকা" তালিকা বানান: যাদের কোনো ক্যানসেল অর্ডার আছে, আর যারা কখনো অর্ডারই করেননি। customer_id আর নাম দেখান, customer_id ক্রমে।
3. কঠিন: পেমেন্ট মেথডের একটা রিপোর্ট বানান: প্রতি মেথডে এক সারি (লেবেল, পেমেন্টের সংখ্যা, মোট টাকা) আর শেষে একটা ALL METHODS সারি; মেথডগুলো মোট টাকা অনুযায়ী বেশি থেকে কম, আর সারাংশের সারিটা সবার শেষে।

উত্তর

-- 1
SELECT product_id FROM products
EXCEPT
SELECT product_id FROM order_items
ORDER BY product_id;

-- 2
SELECT o.customer_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'cancelled'
UNION
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY customer_id;

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           4 | Rafiq Islam |
|           8 | Karim Uddin |
+-------------+-------------+

-- 3
SELECT method AS label, COUNT(*) AS payments, SUM(amount) AS total, 0 AS is_total
FROM payments
GROUP BY method
UNION ALL
SELECT 'ALL METHODS', COUNT(*), SUM(amount), 1
FROM payments
ORDER BY is_total, total DESC;

+-------------+----------+-------+----------+
| label       | payments | total | is_total |
+-------------+----------+-------+----------+
| card        |        6 | 44950 |        0 |
| bkash       |        5 | 18000 |        0 |
| cash        |        2 |  7100 |        0 |
| ALL METHODS |       13 | 70050 |        1 |
+-------------+----------+-------+----------+

সারসংক্ষেপ

• সেট অপারেশন পুরো ফলাফলগুলোকে সারি ধরে জোড়ে: UNION (সব সারি, ডুপ্লিকেট ছাড়া), UNION ALL (সব সারি, ডুপ্লিকেটসহ), INTERSECT (দুটোতেই আছে), EXCEPT (শুধু প্রথমটায় আছে)।
• সত্যিই আলাদা মানের তালিকা না চাইলে UNION ALL নিন; এটা দ্রুতও।
• প্রতিটা অংশে কলামের সংখ্যা সমান হতে হবে আর একই অবস্থানের টাইপ মানানসই হতে হবে (মেলে অবস্থান ধরে); নাম আসে প্রথম অংশ থেকে।
• শেষের একটা ORDER BY/LIMIT পুরো ফলাফলে খাটে; একটা অংশ সীমিত করতে সেটাকে ডিরাইভড টেবিলে মুড়ে দিন।
• MySQL-এ INTERSECT/EXCEPT আছে শুধু 8.0.31 থেকে; Oracle-এ EXCEPT-এর প্রথাগত নাম MINUS। সেট অপারেশন NULL-গুলোকে সমান ধরে।

এরপর: প্রজেক্ট: BitByte Shop-এর বিক্রয় বিশ্লেষণ রিপোর্ট এই অধ্যায়ের সবকিছু — জয়েন, অ্যাগ্রিগেট, সাবকোয়েরি আর সেট অপারেশন — একসাথে কাজে লাগিয়ে একটা পূর্ণাঙ্গ রিপোর্ট বানায়, যা চলে Python থেকে আর চার্টের জন্য এক্সপোর্ট হয়।