Self join, cross join, semi join আর anti join

১ · Self join — একই টেবিল, দুটি ভূমিকা

পর্ব ১৯-এ (JOIN — INNER, LEFT, RIGHT আর FULL) আপনি দুটি আলাদা টেবিল জুড়েছেন: orders-এর পাশে customers, enrollments-এর পাশে courses. এই পর্বে প্রশ্নগুলো একটু অন্য রকম। “এই শিক্ষকের mentor কে?” — উত্তরটি আছে একই instructors টেবিলে। “কোন গ্রাহক অন্তত একটি অর্ডার পেয়েছেন?” — এখানে অর্ডারের তালিকা চাই না, শুধু হ্যাঁ বা না। “কোন বিভাগে একজনও শিক্ষার্থী নেই?” — এখানে খুঁজছি যা নেই। প্রতিটি প্রশ্নের জন্য join-এর একটি বিশেষ রূপ আছে।

প্রথমে টেবিলটি মনে করুন। db-uni-র instructors টেবিলে কলাম পাঁচটি: instructor_id (শিক্ষকের নম্বর), name, dept_id (বিভাগ), salary (মাসিক বেতন, টাকায়) আর mentor_id — নতুন শিক্ষককে যে সিনিয়র শিক্ষক দেখেন, তাঁর instructor_id. mentor_id নিজের টেবিলের দিকেই একটি foreign key — পর্ব ১০ আর ১১-এ দেখা recursive relationship. ছয়জন শিক্ষকের তিনজনের mentor আছে: Shahana Parvin-এর mentor 1 (Dr. Anisur Rahman), Rumana Islam-এর 3 (Dr. Mahbub Alam), আর Tasnim Chowdhury-র 2 (Shahana Parvin). বাকি তিনজনের mentor_id NULL.

Self join নতুন কোনো keyword নয়। এটি একটি সাধারণ JOIN, যেখানে দুই পাশে একই টেবিল। DBMS-কে আলাদা করে চেনাতে প্রতিটি কপিকে আলাদা alias দিতে হয় — নইলে instructors.name কোন কপির, বোঝা যায় না। নিচের এডিটরে i হলো “শিক্ষক হিসেবে” কপি, m হলো “mentor হিসেবে” কপি। join-এর শর্ত বলে: m-এর instructor_id সমান i-এর mentor_id.

-- INNER self join: শুধু যাঁদের mentor আছে
SELECT i.name AS instructor, m.name AS mentor
FROM instructors i
JOIN instructors m ON m.instructor_id = i.mentor_id;

-- LEFT self join: সব শিক্ষক, mentor না থাকলে NULL
SELECT i.name AS instructor, m.name AS mentor
FROM instructors i
LEFT JOIN instructors m ON m.instructor_id = i.mentor_id
ORDER BY i.instructor_id;

শর্তের দিক উল্টে লিখলে (m.mentor_id = i.instructor_id) প্রশ্নটিই বদলে যায়: তখন i-এর পাশে আসে i যাঁদের mentor, অর্থাৎ mentee-রা। self join-এ কোন alias কোন ভূমিকা নিচ্ছে, তা কলামের নামে (AS mentor) স্পষ্ট লিখে রাখুন — পরে পড়তে গিয়ে নিজেই গুলিয়ে ফেলবেন না।

জোড়া খোঁজা — duplicate ছাড়া

দ্বিতীয় প্রচলিত কাজ: একই বিভাগের শিক্ষার্থীদের সব জোড়া — যেমন প্রজেক্টের জন্য দুজনের দল বানাতে। students টেবিলে student_id, name, dept_id, batch আর cgpa. শর্ত “একই বিভাগ” লিখলেই কাজ শেষ নয়; তখন প্রত্যেকে নিজের সাথেও জোড়া পান, আর প্রতিটি জোড়া দুবার আসে (Arif–Bristy আর Bristy–Arif). সমাধান একটি ছোট কৌশল: a.student_id < b.student_id. নিচের ডায়াগ্রামে CSE-র চারজন শিক্ষার্থী দিয়ে ধাপে ধাপে দেখুন:

-- একই বিভাগের শিক্ষার্থী-জোড়া, প্রতিটি একবার
SELECT a.name AS student_1, b.name AS student_2, a.dept_id
FROM students a
JOIN students b ON b.dept_id = a.dept_id
               AND a.student_id < b.student_id
ORDER BY a.dept_id, a.student_id, b.student_id;

-- <> দিলে কত সারি হতো?
SELECT COUNT(*) AS pairs_with_not_equal
FROM students a JOIN students b
  ON b.dept_id = a.dept_id AND a.student_id <> b.student_id;

২ · Self join-এর সীমা — বহু স্তরের hierarchy

mentor_id-এর মতো “নিজের টেবিলের দিকে আঙুল” দিয়ে গাছের মতো কাঠামো রাখাকে বলে adjacency list — প্রতিটি সারি শুধু তার ঠিক ওপরের জনকে চেনে। অফিসের boss–কর্মী, পণ্যের শ্রেণি–উপশ্রেণি, ফেসবুক মন্তব্যের উত্তর, জেলা–উপজেলা–ইউনিয়ন — সবই এমন hierarchical data. এক স্তর ওপরে যেতে একটি self join লাগে। দুই স্তর? দুটি:

-- শিক্ষক → mentor → mentor-এর mentor
SELECT i.name  AS instructor,
       m1.name AS mentor,
       m2.name AS mentors_mentor,
       m2.mentor_id AS level_3_id
FROM instructors i
LEFT JOIN instructors m1 ON m1.instructor_id = i.mentor_id
LEFT JOIN instructors m2 ON m2.instructor_id = m1.mentor_id
ORDER BY i.instructor_id;

এখানেই self join-এর মৌলিক সীমা। প্রতিটি স্তরের জন্য একটি করে join লিখতে হয়, অর্থাৎ গভীরতা কোয়েরি লেখার সময়েই জানা থাকতে হবে। কিন্তু কাল কেউ Tasnim-এর অধীনে নতুন শিক্ষক যোগ করলে চতুর্থ স্তর তৈরি হবে, আর উপরের কোয়েরি চুপচাপ তাকে কেটে দেবে। “Dr. Anisur Rahman-এর অধীনে মোট কতজন, যত স্তরই হোক?” — এমন প্রশ্ন নির্দিষ্ট সংখ্যক join দিয়ে লেখাই যায় না।

শুধু এক ঝলক দেখে রাখুন, পর্ব ২৫-এ কোয়েরিটি কেমন দেখাবে। ধারণাটি: প্রথমে শুরুর মানুষ (anchor), তারপর “আগের ধাপের প্রত্যেকের mentee-রা” — ঠিক একটি self join, কিন্তু নিজের আগের ফলের সাথে:

-- পূর্বাভাস: পর্ব ২৫ — Dr. Anisur Rahman-এর অধীনে সবাই, যত স্তরই হোক
WITH RECURSIVE team(instructor_id, name, level) AS (
  SELECT instructor_id, name, 0 FROM instructors WHERE instructor_id = 1
  UNION ALL
  SELECT i.instructor_id, i.name, t.level + 1
  FROM instructors i JOIN team t ON i.mentor_id = t.instructor_id
)
SELECT * FROM team;

Oracle-এ এর জন্য বহু আগে থেকে নিজস্ব CONNECT BY লেখার ধরনও আছে। আবার কিছু ডিজাইন hierarchy-কে অন্যভাবে রাখে — পুরো পথ একটি কলামে ('/1/2/6/') বা সব পূর্বপুরুষ–উত্তরসূরি জোড়া আলাদা টেবিলে; ভাবনার প্রশ্ন ৩-এ এদের তুলনা।

৩ · CROSS JOIN — সব সম্ভাব্য জোড়া, ইচ্ছে করে

CROSS JOIN-এর কোনো শর্ত নেই: বাঁ টেবিলের প্রতিটি সারির সাথে ডান টেবিলের প্রতিটি সারি। m সারি × n সারি = m·n সারি — পর্ব ৫-এর Cartesian product. সাধারণত এটি একটি দুর্ঘটনা (join-এর শর্ত ভুলে গেলে), কিন্তু কিছু কাজে ঠিক এটিই চাই: যখন উত্তরে প্রতিটি সমন্বয় থাকতে হবে, ডেটায় থাকুক বা না থাকুক।

দ্বিতীয় কাজটি রিপোর্টে খুব কাজের: ক্যালেন্ডার-ছক। “প্রতিটি শ্রেণির প্রতি মাসের বিক্রি” চাইলে শুধু GROUP BY করলে যে মাসে কোনো বিক্রি নেই, সেই ঘরটি ফল থেকে উধাও হয় — অথচ চার্টে সেখানে ০ দেখানো দরকার। সমাধান: আগে শ্রেণি × মাস দিয়ে সম্পূর্ণ ছক বানান, তারপর বিক্রির হিসাব LEFT JOIN করুন। নিচের এডিটর db-shop-এর সাথে তিনটি ছোট টেবিল আগে তৈরি করে নেয়: sizes (৪টি সাইজ), colours (৩টি রং) আর months ('2025-09', '2025-10'). db-shop-এর orders (order_id, customer_id, order_date, status), order_items (order_id, product_id, quantity, unit_price), products (product_id, name, category_id, price, stock) আর categories (category_id, name) পর্ব ১১-এ দেখেছেন; পুরো ডেটা এডিটরের “নমুনা ডেটা” বাক্সে।

-- ১) সব সাইজ × সব রং
SELECT COUNT(*) AS variants FROM sizes CROSS JOIN colours;

-- ২) শ্রেণি × মাসের সম্পূর্ণ ছক, তারপর বিক্রি (বাতিল বাদ)
SELECT c.name AS category, m.month, COALESCE(s.revenue, 0) AS revenue
FROM categories c
CROSS JOIN months m
LEFT JOIN (SELECT p.category_id, substr(o.order_date, 1, 7) AS month,
                  SUM(oi.quantity * oi.unit_price) AS revenue
           FROM orders o
           JOIN order_items oi ON oi.order_id = o.order_id
           JOIN products p ON p.product_id = oi.product_id
           WHERE o.status <> 'cancelled'
           GROUP BY p.category_id, substr(o.order_date, 1, 7)) s
  ON s.category_id = c.category_id AND s.month = m.month
ORDER BY c.category_id, m.month;

আরও কিছু বৈধ ব্যবহার: পরীক্ষার হলে প্রতিটি শিক্ষার্থী × প্রতিটি বিষয়ের খালি নম্বরপত্র তৈরি, প্রতিটি শাখা × প্রতিটি দিনের হাজিরা-ছক, বা একটি এক-সারির টেবিল (যেমন আজকের ডলার রেট) প্রতিটি সারির পাশে জুড়ে দেওয়া। শেষটিতে ১ × n = n, তাই কোনো বিস্ফোরণ নেই।

৪ · Non-equi join — সমতা নয়, পরিসর মিলিয়ে

এ পর্যন্ত প্রায় সব join-এর শর্ত ছিল সমতা (=) — একে বলে equi join. কিন্তু ON-এর ভেতরে যেকোনো শর্ত লেখা যায়: <, >=, BETWEEN, এমনকি ফাংশন। সমতা ছাড়া অন্য শর্তে join-কে বলে non-equi join. অংশ ১-এর a.student_id < b.student_id-ও আসলে এর একটি উদাহরণ। সবচেয়ে প্রচলিত ব্যবহার হলো পরিসরের টেবিল: কোন মান কোন স্তরে পড়ে।

দোকানের মালিক পণ্যগুলো চারটি দামের স্তরে দেখাতে চান। নিচের এডিটর একটি price_bands টেবিল বানায় — band (স্তরের নাম), min_price আর max_price: Budget 0–500, Mid 500–2000, Premium 2000–10000, Luxury 10000–1000000. লক্ষ করুন সীমাগুলো অর্ধ-খোলা: নিচের সীমা অন্তর্ভুক্ত, ওপরেরটি নয় (min_price <= price < max_price). তাই ঠিক 500 টাকার পণ্য শুধু Mid-এ পড়ে, দুই স্তরে নয়।

-- প্রতিটি পণ্য কোন দামের স্তরে
SELECT p.name, p.price, b.band
FROM products p
JOIN price_bands b
  ON p.price >= b.min_price AND p.price < b.max_price
ORDER BY p.price;

-- প্রতিটি স্তরে কয়টি পণ্য (খালি স্তরও দেখাবে)
SELECT b.band, COUNT(p.product_id) AS products
FROM price_bands b
LEFT JOIN products p ON p.price >= b.min_price AND p.price < b.max_price
GROUP BY b.band, b.min_price
ORDER BY b.min_price;

নম্বর থেকে letter grade, letter grade থেকে GPA

বিশ্ববিদ্যালয়ের সবচেয়ে পরিচিত পরিসর-টেবিল হলো grade-এর তালিকা। নিচে একটি grade_points টেবিল — letter, point, min_mark আর max_mark — বাংলাদেশের অনেক বিশ্ববিদ্যালয়ে প্রচলিত একটি স্কেল ধরে: 80–100 A+ (4.00), 75–79 A (3.75), 70–74 A- (3.50), 65–69 B+ (3.25) … 40–44 D (2.00), 0–39 F (0.00). (প্রতিষ্ঠানভেদে সীমা আলাদা হতে পারে — আপনারটি দেখে নেবেন।) নম্বর পূর্ণসংখ্যা বলে এখানে দুই দিক-অন্তর্ভুক্ত BETWEEN নিরাপদ। সাথে একটি ছোট lab_marks টেবিল (student_id, marks): চারজন শিক্ষার্থীর ল্যাব-পরীক্ষার নম্বর 82, 67, 74, 38. প্রথমে দেখুন প্রতিটি নম্বর কীভাবে একটি সীমা খুঁজে পায়:

এবার আসল কাজ: এই একই lookup টেবিল দিয়ে প্রত্যেক শিক্ষার্থীর GPA. পর্ব ১১-এ এই হিসাব করেছিলেন দশ লাইনের একটি CASE দিয়ে; এখন grade-এর point আসে একটি টেবিল থেকে, join করে। স্কেল বদলালে কোয়েরি বদলাতে হবে না, শুধু টেবিলের সারি।

-- ১) non-equi join: নম্বর → letter grade
SELECT m.student_id, m.marks, g.letter, g.point
FROM lab_marks m
JOIN grade_points g ON m.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY m.student_id;

-- ২) equi join: letter → point, তারপর credit দিয়ে ভারিত গড়
SELECT e.student_id,
       ROUND(SUM(c.credits * g.point) / SUM(c.credits), 2) AS gpa
FROM enrollments e
JOIN grade_points g ON g.letter = e.grade
JOIN courses c ON c.course_id = e.course_id
GROUP BY e.student_id
ORDER BY e.student_id;

Non-equi join-এর দাম মনে রাখুন: সমতার join-এ DBMS hash table বানিয়ে প্রতিটি সারির মিল প্রায় O(1)-এ খুঁজতে পারে (পর্ব ৪২), কিন্তু < বা BETWEEN-এ hash কাজ করে না। তখন ভরসা nested loop (m × n তুলনা) বা সাজানো ডেটা আর index-এর ওপর পরিসর-খোঁজ। দশ সারির lookup টেবিলে এটি টেরই পাবেন না; দুটি বড় টেবিলের মধ্যে পরিসর-join-এ পাবেন।

৫ · Semi join — “অন্তত একটি আছে কি?”

প্রশ্ন: “কোন কোন গ্রাহক অন্তত একটি অর্ডার হাতে পেয়েছেন (delivered)?” উত্তরে চাই গ্রাহকের তালিকা — প্রত্যেকে একবার। অর্ডারের কোনো কলাম চাই না; orders টেবিলটি শুধু একটি পরীক্ষা: মিল আছে কি না। এমন join-কে বলে semi join. SQL-এ এর আলাদা keyword নেই; লেখা হয় EXISTS বা IN দিয়ে।

সাধারণ JOIN দিয়ে লিখলে সমস্যাটি কোথায়, ধাপে ধাপে দেখুন। প্রতিটি গ্রাহকের পাশে তাঁর delivered অর্ডারগুলো লেখা আছে; বাঁ কলামে JOIN-এর ফল, ডানে semi join-এর:

-- ১) সাধারণ JOIN: যতগুলো মিল, ততবার
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered';

-- ২) semi join, EXISTS দিয়ে
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id
                AND o.status = 'delivered');

-- ৩) semi join, IN দিয়ে
SELECT customer_id, name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders
                      WHERE status = 'delivered');

“তাহলে JOIN-এর পরে DISTINCT দিলেই তো হয়?” এই ছোট ডেটায় হয়। কিন্তু তিনটি কারণে semi join-ই সঠিক প্রকাশ। এক, DISTINCT পুরো সারি দেখে: দুজন আলাদা গ্রাহকের নাম আর শহর এক হলে, আর আপনি customer_id না দেখালে, তাঁরা এক হয়ে যাবেন। দুই, DISTINCT আগে সব duplicate তৈরি করে, তারপর সাজিয়ে বা hash করে মোছে — একজন গ্রাহকের হাজারটি অর্ডার থাকলে হাজার সারি বানিয়ে ৯৯৯টি ফেলে দেওয়া। তিন, JOIN-এর পর SUM বা COUNT করলে duplicate গোনায় ঢুকে যায় (এক সারির মান অনেকবার যোগ — fan-out), আর DISTINCT সেটি বাঁচাতে পারে না। নিয়মটি সহজ: ডান টেবিলের কোনো কলাম ফলে না লাগলে semi join; লাগলে JOIN.

৬ · Anti join — “একটিও নেই” আর NOT IN-এর NULL ফাঁদ

Semi join-এর উল্টো প্রশ্ন: “কোন বিভাগে একজনও শিক্ষার্থী নেই?”, “কোন পণ্য কখনো বিক্রি হয়নি?”, “কোন গ্রাহক কখনো অর্ডার দেননি?” — বাঁ টেবিলের সেই সারিগুলো, যাদের ডান টেবিলে কোনো মিল নেই। একে বলে anti join. SQL-এ এটি লেখার তিনটি প্রচলিত ধরন আছে, আর তিনটি সবসময় একই ফল দেয় না।

db-uni-তে চারটি বিভাগ; শিক্ষার্থীদের dept_id-গুলো 1, 1, 2, 1, 3, 2, 1 আর Habib Ullah-র NULL. অর্থাৎ উত্তর হওয়া উচিত শুধু English (4). তিনটি ধরন একসাথে চালান:

-- ১) NOT EXISTS
SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM students s WHERE s.dept_id = d.dept_id);

-- ২) LEFT JOIN … IS NULL (primary key কলাম পরীক্ষা)
SELECT d.name FROM departments d
LEFT JOIN students s ON s.dept_id = d.dept_id
WHERE s.student_id IS NULL;

-- ৩) NOT IN — তালিকায় Habib-এর NULL
SELECT d.name FROM departments d
WHERE d.dept_id NOT IN (SELECT s.dept_id FROM students s);

-- ৪) NOT IN, NULL বাদ দিয়ে
SELECT d.name FROM departments d
WHERE d.dept_id NOT IN (SELECT s.dept_id FROM students s
                        WHERE s.dept_id IS NOT NULL);

কেন একটি NULL পুরো ফল মুছে দেয় — প্রমাণ

x NOT IN (a, b, c)-এর সংজ্ঞাই হলো x <> a AND x <> b AND x <> c. English-এর জন্য x = 4, আর তালিকা (1, 1, 2, 1, 3, 2, 1, NULL):

• 4 <> 1, 4 <> 2, 4 <> 3 — সবই TRUE.
• 4 <> NULL — UNKNOWN, কারণ NULL মানে “অজানা”; অজানা মানটি 4 হতেও পারে।
• তিন-মানের যুক্তিতে TRUE AND UNKNOWN = UNKNOWN. একটি UNKNOWN থাকলে AND-এর ফল কখনো TRUE হতে পারে না — বড়জোর UNKNOWN, কোনো FALSE থাকলে FALSE.
• WHERE শুধু TRUE সারি রাখে। তাই কোনো x-এর জন্যই শর্ত TRUE হয় না: সাবকোয়েরির ফলে অন্তত একটি NULL থাকলে NOT IN কখনো কোনো সারি দেয় না। ∎

উল্টো দিকে IN নিরাপদ: x IN (…) মানে OR-এর শিকল, আর TRUE OR UNKNOWN = TRUE — মিল থাকলে সারিটি আসে, না থাকলে UNKNOWN হয়, যা WHERE-এ FALSE-এর মতোই বাদ। তাই semi join-এ IN আর EXISTS সমান, কিন্তু anti join-এ NOT IN আর NOT EXISTS সমান নয়। NOT EXISTS প্রতিটি সারির জন্য শুধু জিজ্ঞেস করে “এমন সারি আছে কি?” — যার উত্তর সবসময় TRUE বা FALSE, কখনো UNKNOWN নয়।

তাহলে কি NOT IN কখনো লিখবেন না? সাবকোয়েরির কলামটি NOT NULL হলে (যেমন db-shop-এর orders.customer_id) NOT IN সঠিক ফল দেয়: “কখনো অর্ডার দেননি” প্রশ্নে উত্তর Tanvir Ahmed. কিন্তু আজ NOT NULL কলাম কাল কেউ বদলে দিতে পারেন, আর তখন কোয়েরিটি কোনো ত্রুটি ছাড়াই ভুল ফল দেবে। পেশাদার অভ্যাস: anti join মানেই NOT EXISTS. হাতে লেখা মানের তালিকায় (NOT IN ('cancelled', 'pending')) NULL নেই জানলে NOT IN ঠিক আছে।

৭ · একাধিক কলামে join, grouped subquery আর LATERAL

৭.১ · Composite key-তে join — সব কলাম মেলাতে হবে

যখন কোনো সারিকে চিনতে একাধিক কলাম লাগে (composite key), join-এর শর্তেও সবগুলো লিখতে হয়। একটি উদাহরণ: বিশ্ববিদ্যালয় নিশ্চিত হতে চায়, শিক্ষার্থীরা পূর্বশর্ত-কোর্স (prerequisite) পাস করে তারপর উঁচু কোর্স নিয়েছেন কি না। নিচের এডিটর একটি prerequisites টেবিল বানায় — course_id আর prereq_id, দুটি মিলে primary key: CSE201-এর আগে CSE101, CSE301 আর CSE302-এর আগে CSE201. এখন enrollments-কে নিজের সাথে join করতে হবে: “একই শিক্ষার্থী আর পূর্বশর্ত-কোর্সটি”।

SELECT e.student_id, e.course_id, p.prereq_id,
       done.term AS prereq_term, done.grade AS prereq_grade
FROM enrollments e
JOIN prerequisites p ON p.course_id = e.course_id
LEFT JOIN enrollments done
       ON done.student_id = e.student_id
      AND done.course_id  = p.prereq_id
      AND done.grade IS NOT NULL AND done.grade <> 'F'
ORDER BY e.student_id, e.course_id;

-- student_id মেলাতে ভুলে গেলে?
SELECT COUNT(*) AS rows_if_student_id_forgotten
FROM enrollments e
JOIN prerequisites p ON p.course_id = e.course_id
LEFT JOIN enrollments done ON done.course_id = p.prereq_id;

যখন দুই টেবিলে কলামগুলোর নাম হুবহু এক, তখন সংক্ষেপে JOIN … USING (student_id, course_id) লেখা যায় (SQL Server ছাড়া প্রধান DBMS-গুলোতে)। কিন্তু NATURAL JOIN এড়িয়ে চলুন: এটি একই নামের সব কলামে নিজে থেকে join করে, আর কাল কেউ দুই টেবিলে created_at যোগ করলে কোয়েরির অর্থ নিঃশব্দে বদলে যাবে।

৭.২ · Grouped subquery-র সাথে join — প্রতিজনের সর্বশেষ অর্ডার

“প্রতিটি গ্রাহকের সর্বশেষ অর্ডারটি — order_id আর status সহ।” শুধু GROUP BY customer_id আর MAX(order_date) দিলে তারিখ পাবেন, কিন্তু সেই অর্ডারের id বা status নয় (সেগুলো group-এর ভেতরে অনেকগুলো)। কৌশল: প্রথমে একটি derived table (FROM-এর ভেতরের subquery)-তে প্রতি গ্রাহকের সর্বশেষ তারিখ বের করুন, তারপর সেই ফলকে orders-এর সাথে দুই কলামে join করে আসল সারিটি ফিরিয়ে আনুন:

SELECT c.name, o.order_id, o.order_date, o.status
FROM customers c
JOIN (SELECT customer_id, MAX(order_date) AS last_date
      FROM orders
      GROUP BY customer_id) lo ON lo.customer_id = c.customer_id
JOIN orders o ON o.customer_id = lo.customer_id
             AND o.order_date  = lo.last_date
ORDER BY o.order_date DESC;

৭.৩ · LATERAL আর APPLY — প্রতিটি সারির জন্য আলাদা subquery

উপরের derived table-টি একবারই হিসাব হয়, আর বাইরের কোনো সারি “দেখতে” পায় না। কিন্তু ধরুন প্রতিটি গ্রাহকের শেষ দুটি অর্ডার চাই। তখন সবচেয়ে স্বাভাবিক চিন্তা: “প্রতিটি গ্রাহকের জন্য, তাঁর অর্ডারগুলো তারিখ অনুযায়ী সাজিয়ে প্রথম দুটি নাও।” এর জন্য FROM-এর subquery-কে বাঁ পাশের সারি দেখতে দিতে হয় — একে বলে lateral join. SQL:1999 মানে এর নাম LATERAL; PostgreSQL (9.3 থেকে), MySQL (8.0.14 থেকে) আর Oracle (12c থেকে) এটি চালায়। SQL Server-এ একই ধারণার নাম CROSS APPLY আর OUTER APPLY. SQLite-এ এটি নেই, তাই নিচের কোড পাতায় চলবে না:

-- প্রতিটি গ্রাহকের শেষ দুটি অর্ডার
SELECT c.name, t.order_id, t.order_date
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- বাঁ পাশের সারি দেখছে
    ORDER BY o.order_date DESC
    LIMIT 2
) AS t;
-- অর্ডারহীন গ্রাহককেও রাখতে: LEFT JOIN LATERAL (...) AS t ON true

SELECT c.name, t.order_id, t.order_date
FROM customers c
CROSS APPLY (
    SELECT TOP (2) o.order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) AS t;
-- অর্ডারহীন গ্রাহককেও রাখতে: OUTER APPLY

ফলটি হতো: Rahim Uddin-এর 1009 আর 1003, Karima Begum-এর 1007 আর 1002, আর বাকি পাঁচজনের একটি করে অর্ডার — মোট ৯টি সারি; Tanvir শুধু LEFT JOIN LATERAL বা OUTER APPLY-তে, NULL-সহ। ধারণাগতভাবে LATERAL হলো “প্রতিটি বাঁ-সারির জন্য একবার চলা একটি correlated subquery, যা একাধিক সারি আর কলাম ফেরত দিতে পারে”। SQLite আর অন্য সব DBMS-এ একই কাজ পর্ব ২৬-এর window function দিয়েও করা যায়।

৮ · Join-এর ক্রম আর relational algebra-র চোখে join

Inner join-এর ক্রম মুক্ত, outer join-এর নয়

তিনটি টেবিল A JOIN B JOIN C — আগে A আর B জুড়ে তারপর C, নাকি আগে B আর C? Inner join-এ উত্তর একই। কারণ inner join আসলে “Cartesian product, তারপর সব শর্ত দিয়ে ছাঁকা”, আর ছাঁকার শর্তগুলোর AND যে ক্রমেই প্রয়োগ করুন, একই সারি টেকে: σ_p(σ_q(A × B × C)) = σ_{p ∧ q}(A × B × C), আর × নিজেও (কলামের ক্রম বাদে) বিনিময়যোগ্য ও সংযোজনযোগ্য। তাই optimizer inner join-গুলো যে ক্রমে সস্তা, সেই ক্রমে চালাতে পারে (পর্ব ৪৩)। কিন্তু outer join NULL দিয়ে সারি “বাঁচিয়ে রাখে”, আর পরের একটি inner join সেই বাঁচানো সারি আবার ফেলে দিতে পারে। নিচে প্রশ্ন: “প্রতিটি বিভাগ, তার কোর্স আর শিক্ষক — কোর্স বা শিক্ষক না থাকলেও বিভাগটি দেখাও।”

বাস্তব নিয়ম: একবার LEFT JOIN শুরু করলে, সেই টেবিলের ওপর নির্ভরশীল পরের join-গুলোও সাধারণত LEFT হওয়া উচিত — নইলে প্রশ্ন করুন, “আমি কি সত্যিই চাই NULL-যুক্ত সারিগুলো এখানে বাদ পড়ুক?” একই কারণে LEFT JOIN-এর ডান টেবিলের শর্ত WHERE-এ লিখলেও outer join নিঃশব্দে inner হয়ে যায়।

Relational algebra-র ভাষায় — পর্ব ৩৩-এর পূর্বাভাস

পর্ব ৩২–৩৩-এ relational algebra আনুষ্ঠানিকভাবে আসবে; এই পর্বের join-গুলো সেখানে এভাবে লেখা হয় (R বাঁ, S ডান relation, θ শর্ত):

ছোট প্রমাণ — semi join কখনো সারি বাড়ায় না: সংজ্ঞা অনুযায়ী R ⋉ S-এর প্রতিটি tuple হলো R ⋈ S-এর কোনো tuple-এর R-অংশ, অর্থাৎ R-এরই একটি tuple. তাই R ⋉ S ⊆ R, ফলে |R ⋉ S| ≤ |R|. অন্যদিকে R ⋈ S-এ R-এর একটি tuple যতগুলো S-tuple-এর সাথে মেলে, ততবার আসে — Karima-র দুটি delivered অর্ডার মানে দুটি সারি। (SQL-এর bag semantics-এ semi join R-এর প্রতিটি সারিকে ঠিক যতবার R-এ আছে ততবার রাখে, মিলের সংখ্যা যা-ই হোক।) আর R ▷ S = R − (R ⋉ S) থেকে সরাসরি পাওয়া যায় (R ⋉ S) ∪ (R ▷ S) = R, আর দুটি পরস্পর-বিচ্ছিন্ন: প্রতিটি বাঁ-সারি ঠিক একটিতে পড়ে — “অন্তত একটি মিল আছে” অথবা “একটিও নেই”। এই বিভাজন NOT EXISTS-এর জন্য সবসময় সত্য, কিন্তু NULL-যুক্ত NOT IN-এর জন্য নয় — অংশ ৬-এ English দুই দিকের কোনোটিতেই পড়েনি।

৯ · AI-এর যুগে advanced join

“প্রতিটি গ্রাহক যিনি কখনো delivered অর্ডার পাননি” — AI কয়েক সেকেন্ডে একটি কোয়েরি দেবে, আর সেটি চলবেও। কিন্তু join-এর ভুলগুলো ত্রুটি দেয় না; দেয় দেখতে বিশ্বাসযোগ্য, অথচ ভুল সংখ্যা। এই পর্বের বিষয়ে AI-এর প্রচলিত ভুল:

AI-এর লেখা join কোয়েরি হাতে পেলে — যাচাইয়ের চেকলিস্ট

• প্রশ্নটি “অন্তত একটি”, “একটিও নেই” না “প্রতিটি মিলের জন্য একটি সারি” — সেই অনুযায়ী EXISTS, NOT EXISTS বা JOIN হয়েছে
• ফলের সারির সংখ্যা হাতে হিসাব করা সংখ্যার সাথে মিলিয়েছি (বাঁ টেবিল, জোড়া, পরিসরের যোগফল)
• সাবকোয়েরিতে NOT IN থাকলে কলামটি NOT NULL কি না দেখেছি
• প্রতিটি LEFT JOIN-এর পরের join আর WHERE শর্তগুলো NULL-সারি ফেলে দিচ্ছে না
• composite key-র সব কলাম ON-এ আছে; কোনো অনিচ্ছাকৃত CROSS JOIN বা কমা-join নেই
• মিলহীন, একাধিক মিলের আর NULL-যুক্ত — তিন রকম পরীক্ষা-সারি দিয়ে চালিয়েছি
• বড় টেবিলে EXPLAIN দেখেছি: semi/anti join হচ্ছে, join কলামে index আছে