Subquery — nested, correlated আর EXISTS

১ · Subquery কী — প্রশ্নের ভেতরে প্রশ্ন

মুদি দোকানের মালিক হিসাবরক্ষককে বললেন, “যেসব পণ্যের দাম আমাদের গড় দামের চেয়ে বেশি, সেগুলোর তালিকা দাও।” হিসাবরক্ষক দুই ধাপে কাজটি করবেন। প্রথমে খাতা দেখে গড় দাম বের করবেন, ধরা যাক ৩,০০৮ টাকা। তারপর খাতার প্রতিটি পণ্য সেই সংখ্যার সাথে মেলাবেন। প্রথম ধাপের উত্তরটি দ্বিতীয় ধাপের প্রশ্নের অংশ হয়ে যায়।

SQL-এ এই দুই ধাপ একটি কোয়েরিতেই লেখা যায়। ভেতরের প্রশ্নটি বন্ধনীতে বসে বাইরের প্রশ্নের ভেতরে:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);   -- বন্ধনীর ভেতরেরটি subquery

বন্ধনীর ভেতরের SELECT-টিকে বলে subquery (বা nested query, inner query). যে কোয়েরির ভেতরে এটি বসে, সেটি outer query. পর্ব ১৫-এ SELECT আর WHERE, পর্ব ১৮-এ aggregate function আর GROUP BY, আর পর্ব ১৯–২০-এ JOIN দেখেছেন। Subquery এদের সবগুলোকে জুড়ে বড় প্রশ্ন বানানোর উপায়। মূল সত্যটি সহজ: SELECT-এর ফলও একটি টেবিল। তাই যেখানে একটি মান, একটি তালিকা বা একটি টেবিল লেখা যায়, সেখানে প্রায়ই একটি SELECT-ও বসানো যায়।

Subquery কোথায় বসতে পারে

ফলের আকার অনুযায়ী subquery তিন রকম। Scalar subquery ফেরত দেয় একটি সারি আর একটি কলাম, অর্থাৎ একটিমাত্র মান। Row subquery ফেরত দেয় একটি সারি কিন্তু একাধিক কলাম। Table subquery ফেরত দেয় যেকোনো সংখ্যক সারি আর কলাম। কোন জায়গায় কোন আকার চলে, সেটি না মেলালেই ভুল হয়।

এই পর্বের লাইভ SQL চলে আগের পর্বগুলোর দুটি নমুনা ডেটাবেসে। db-shop একটি দোকান: customers (customer_id, name, city, phone, joined_on — ৮ জন), categories (৪টি শ্রেণি: 1 Electronics, 2 Books, 3 Clothing, 4 Grocery), products (product_id, name, category_id, price, stock — ১০টি পণ্য), orders (order_id, customer_id, order_date, status — ১০টি অর্ডার) আর order_items (order_id, product_id, quantity, unit_price). db-uni একটি বিশ্ববিদ্যালয়: departments (dept_id, name, building — CSE, EEE, BBA, English), instructors, students (student_id, name, dept_id, batch, cgpa — ৮ জন; একজনের বিভাগ NULL, একজনের cgpa NULL), courses আর enrollments. প্রতিটি এডিটরের নিচে “নমুনা ডেটা” বাক্সে পুরো টেবিল দেখতে পাবেন। প্রতিবার চালালে ডেটাবেস নতুন করে তৈরি হয়।

২ · Scalar subquery — একটিমাত্র মান

সবচেয়ে সহজ subquery একটি সংখ্যা ফেরত দেয়। সেই সংখ্যাটি তারপর একটি সাধারণ মানের মতো ব্যবহার হয়: তুলনায়, হিসাবে, SELECT তালিকায়। নিচের প্রথম কোয়েরি গড় দাম দেখায়, দ্বিতীয়টি সেই গড় দিয়ে ছাঁকে, আর তৃতীয়টি দুই স্তরের subquery দিয়ে দ্বিতীয় সর্বোচ্চ দাম বের করে — চাকরির ইন্টারভিউর একটি পুরোনো প্রিয় প্রশ্ন।

-- গড় দাম কত?
SELECT AVG(price) AS avg_price FROM products;

-- গড়ের চেয়ে দামি পণ্য
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- দ্বিতীয় সর্বোচ্চ দাম: সর্বোচ্চের চেয়ে কম দামগুলোর সর্বোচ্চ
SELECT MAX(price) AS second_highest FROM products
WHERE price < (SELECT MAX(price) FROM products);

একাধিক স্তরের subquery কোন ক্রমে মূল্যায়ন হয়? নিয়মটি হলো, যে subquery বাইরের কিছুর ওপর নির্ভর করে না, তার মান আগে জানা দরকার, তাই যুক্তির দিক থেকে সবচেয়ে ভেতরেরটি আগে। নিচে দ্বিতীয় সর্বোচ্চ দামের পণ্যটির নাম বের করার তিন স্তরের কোয়েরিটি ধাপে ধাপে দেখুন:

যখন “একটি মান” আসলে অনেকগুলো

Scalar subquery-র চুক্তি হলো “আমি একটির বেশি সারি দেব না”। চুক্তিটি ভাঙলে কী হয়? কোনো সারি না এলে সব DBMS-এ ফল NULL. কিন্তু একাধিক সারি এলে আচরণ আলাদা। নিচের দ্বিতীয় কোয়েরিতে লেখক ভেবেছিলেন Electronics-এর “দাম” একটি, অথচ সেখানে চারটি পণ্য:

-- SELECT তালিকায় scalar subquery: প্রতিটি বইয়ের দাম গড় থেকে কত দূরে
SELECT name, price,
       price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products WHERE category_id = 2;

-- ভুল: Electronics-এর দাম একটি নয়, চারটি
SELECT name, price FROM products
WHERE price = (SELECT price FROM products WHERE category_id = 1);

-- উদ্দেশ্য যদি "যেকোনো একটির সমান" হয়: IN
SELECT name, price FROM products
WHERE price IN (SELECT price FROM products WHERE category_id = 1);

SELECT name, price FROM products
WHERE price = (SELECT price FROM products WHERE category_id = 1);

-- PostgreSQL:  ERROR: more than one row returned by a subquery used as an expression
-- MySQL:       ERROR 1242 (21000): Subquery returns more than 1 row
-- SQL Server:  Msg 512: Subquery returned more than 1 value. ...
-- Oracle:      ORA-01427: single-row subquery returns more than one row

আরেকটি সীমা: scalar subquery-তে একটির বেশি কলাম থাকলে SQLite-ও ত্রুটি দেয় — sub-select returns 2 columns - expected 1. সারির সংখ্যায় SQLite শিথিল, কলামের সংখ্যায় নয়।

৩ · Row আর table subquery — IN আর NOT IN

Table subquery-র সবচেয়ে প্রচলিত ব্যবহার IN-এর সাথে। হাতে লেখা তালিকার IN আপনি চেনেন, যেমন city IN ('Dhaka', 'Khulna') (পর্ব ১৬). এখানে তালিকাটি হাতে না লিখে একটি SELECT থেকে আসে। x IN (subquery) সত্য হয়, যদি subquery-র অন্তত একটি সারির মান x-এর সমান হয়। এক কলামের বদলে কয়েকটি কলাম একসাথে মেলাতে চাইলে row subquery লাগে: (customer_id, order_date) IN (SELECT …). SQLite এটি ৩.১৫ সংস্করণ (২০১৬) থেকে বোঝে, PostgreSQL, MySQL আর Oracle-ও বোঝে, কিন্তু SQL Server এমন বহু-কলামের IN সমর্থন করে না — সেখানে EXISTS লিখতে হয়। অংশ ৬-এ এর একটি চালানো উদাহরণ পাবেন।

-- অন্তত একটি অর্ডার দিয়েছেন এমন গ্রাহক
SELECT customer_id, name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- কখনো অর্ডার দেননি
SELECT customer_id, name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- দুই স্তর: ডেলিভারি হওয়া অর্ডারে যেসব পণ্য ছিল
SELECT name FROM products
WHERE product_id IN (SELECT product_id FROM order_items
                     WHERE order_id IN (SELECT order_id FROM orders
                                        WHERE status = 'delivered'));

NOT IN আর NULL — পুরোনো কিন্তু ভয়ংকর ফাঁদ

উপরের NOT IN ঠিক কাজ করেছে, কারণ orders.customer_id-এ NOT NULL আছে। এবার পর্ব ১৬-এর তিন-মানের যুক্তি মনে করুন। x NOT IN (a, b, c) আসলে x <> a AND x <> b AND x <> c. তালিকায় একটি NULL থাকলে x <> NULL হয় UNKNOWN. আর AND-এর মধ্যে একটি UNKNOWN থাকলে পুরো ফল কখনো TRUE হয় না — হয় FALSE, নয়তো UNKNOWN. WHERE শুধু TRUE সারি রাখে। তাই subquery-র ফলে একটিও NULL থাকলে NOT IN কোনো সারিই ফেরত দেয় না। ত্রুটিও দেয় না — শুধু ফাঁকা ফল, যা দেখে মনে হয় “এমন কেউ নেই”। পরের অংশে db-uni-তে এটি নিজের চোখে দেখবেন।

৪ · EXISTS আর NOT EXISTS — “একটিও আছে কি?”

EXISTS একটি subquery নেয় আর একটিমাত্র প্রশ্নের উত্তর দেয়: এই subquery কি অন্তত একটি সারি ফেরত দেয়? দিলে TRUE, না দিলে FALSE. সারির ভেতরে কী আছে — NULL হোক বা যা-ই হোক — তাতে কিছু যায় আসে না। তাই EXISTS কখনো UNKNOWN হয় না; এটি পুরোপুরি দুই-মানের। এজন্য রীতি হলো ভেতরে SELECT 1 লেখা — SELECT তালিকা এখানে উপেক্ষিত, SELECT * লিখলেও একই ফল।

EXISTS প্রায় সবসময় correlated: ভেতরের WHERE বাইরের সারির কলাম ব্যবহার করে, যেমন WHERE s.dept_id = d.dept_id. বাংলায় পড়ুন: “এই বিভাগের (d) কোনো শিক্ষার্থী (s) আছে কি?” আর DBMS প্রথম মিল পেলেই থামতে পারে — বাকি সারি দেখার দরকার নেই। এবার db-uni-তে প্রশ্ন: কোন বিভাগে কোনো শিক্ষার্থী নেই? মনে রাখুন, Habib Ullah-র dept_id NULL.

-- কোন বিভাগে কোনো শিক্ষার্থী নেই? (NOT IN)
SELECT name FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM students);

-- একই প্রশ্ন NOT EXISTS দিয়ে
SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM students s WHERE s.dept_id = d.dept_id);

-- NOT IN বাঁচানোর উপায়: ভেতর থেকে NULL বাদ দিন
SELECT name FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM students WHERE dept_id IS NOT NULL);

-- EXISTS: যে বিভাগে অন্তত একজনের cgpa 3.5 বা বেশি
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM students s
              WHERE s.dept_id = d.dept_id AND s.cgpa >= 3.5);

দুটি পথ কেন আলাদা ফল দিল, ধাপে ধাপে দেখুন:

৫ · ANY আর ALL — তালিকার সাথে তুলনা

IN শুধু সমতা দেখে। “সব বইয়ের চেয়ে দামি” বা “অন্তত একটি Electronics পণ্যের চেয়ে দামি” ধরনের প্রশ্নে লাগে ANY আর ALL. x > ALL (subquery) সত্য, যদি x প্রতিটি মানের চেয়ে বড় হয়। x > ANY (subquery) সত্য, যদি অন্তত একটির চেয়ে বড় হয় (SOME হলো ANY-র আরেক নাম)। দুটি সমতুল্যতা মনে রাখুন: = ANY মানে IN, আর <> ALL মানে NOT IN — NULL-এর ফাঁদসহ।

-- সব বইয়ের (category 2) চেয়ে দামি পণ্য
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category_id = 2);

-- অন্তত একটি Electronics পণ্যের (category 1) চেয়ে দামি
SELECT name, price FROM products
WHERE price > ANY (SELECT price FROM products WHERE category_id = 1);

SQLite-এ ANY, ALL বা SOME নেই — উপরের কোয়েরি চালালে near "ALL": syntax error আসে। কিন্তু প্রায় সব ক্ষেত্রে MAX আর MIN দিয়ে একই প্রশ্ন লেখা যায়: > ALL মানে “সর্বোচ্চটির চেয়েও বড়”, অর্থাৎ > MAX; আর > ANY মানে “সর্বনিম্নটির চেয়ে বড়”, অর্থাৎ > MIN. একইভাবে < ALL = < MIN আর < ANY = < MAX.

-- > ALL (সব বইয়ের চেয়ে দামি) = > MAX
SELECT name, price FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 2);

-- > ANY (অন্তত একটি Electronics-এর চেয়ে দামি) = > MIN
SELECT name, price FROM products
WHERE price > (SELECT MIN(price) FROM products WHERE category_id = 1);

-- ফাঁকা সেট: category 9 নেই, তাই MAX হয় NULL
SELECT COUNT(*) AS n FROM products
WHERE price > (SELECT MAX(price) FROM products WHERE category_id = 9);

৬ · Correlated subquery — প্রতিটি সারির জন্য নতুন প্রশ্ন

এ পর্যন্ত বেশিরভাগ subquery নিজে নিজে চলতে পারত: “গড় দাম কত?” প্রশ্নের উত্তর বাইরের কোনো সারির ওপর নির্ভর করে না। কিন্তু “নিজের বিভাগের গড়ের চেয়ে বেশি cgpa কার?” প্রশ্নে ভেতরের গড়টি নির্ভর করে বাইরের শিক্ষার্থীটি কোন বিভাগের। যে subquery বাইরের কোয়েরির কোনো কলাম ব্যবহার করে, তাকে বলে correlated subquery. যুক্তির দিক থেকে এটি বাইরের প্রতিটি সারির জন্য আলাদাভাবে, সেই সারির মান বসিয়ে, নতুন করে চলে।

SELECT s.name, s.cgpa
FROM students s                                  -- বাইরের সারি: s
WHERE s.cgpa > (SELECT AVG(s2.cgpa)
                FROM students s2                 -- ভেতরের সারি: s2
                WHERE s2.dept_id = s.dept_id);   -- s.dept_id = বাইরের রেফারেন্স

একই টেবিল দুবার ব্যবহার হচ্ছে, তাই দুটি আলাদা alias জরুরি: s বাইরের, s2 ভেতরের। alias না দিলে dept_id = dept_id লেখা হতো, আর SQL নামটি সবচেয়ে কাছের (ভেতরের) টেবিলে খুঁজত — শর্তটি হয়ে যেত “নিজের সাথে নিজের তুলনা”, যা প্রায় সব সারিতে TRUE. এবার আটজন শিক্ষার্থীর জন্য ভেতরের প্রশ্নটি কীভাবে বদলায়, দেখুন:

এবার নিজে চালান। SELECT তালিকাতেও একটি correlated scalar subquery রাখা হয়েছে, যাতে প্রতিটি সারির পাশে তার বিভাগের গড় দেখা যায়। শেষে EXPLAIN QUERY PLAN দেখায় SQLite নিজে কোয়েরিটি কীভাবে চালানোর পরিকল্পনা করছে:

-- নিজের বিভাগের গড়ের চেয়ে বেশি cgpa
SELECT s.name, s.dept_id, s.cgpa,
       (SELECT ROUND(AVG(s2.cgpa), 2) FROM students s2
        WHERE s2.dept_id = s.dept_id) AS dept_avg
FROM students s
WHERE s.cgpa > (SELECT AVG(s2.cgpa) FROM students s2
                WHERE s2.dept_id = s.dept_id);

-- SQLite কীভাবে চালাবে?
EXPLAIN QUERY PLAN
SELECT s.name FROM students s
WHERE s.cgpa > (SELECT AVG(s2.cgpa) FROM students s2
                WHERE s2.dept_id = s.dept_id);

প্রতিটি গ্রাহকের সর্বশেষ অর্ডার

Correlated subquery-র আরেকটি ক্লাসিক কাজ “প্রতিটি দলের সেরা সারি” (greatest-per-group). প্রশ্ন: প্রতিটি গ্রাহকের সবচেয়ে নতুন অর্ডার কোনটি? শুধু GROUP BY customer_id আর MAX(order_date) দিলে তারিখটি পাওয়া যায়, কিন্তু order_id নয়। নিচে দুই পথ: correlated subquery, আর অংশ ৩-এর row subquery.

-- correlated: এই গ্রাহকের সর্বশেষ তারিখের অর্ডারটিই রাখো
SELECT o.customer_id, o.order_id, o.order_date
FROM orders o
WHERE o.order_date = (SELECT MAX(o2.order_date) FROM orders o2
                      WHERE o2.customer_id = o.customer_id)
ORDER BY o.customer_id;

-- row subquery: (customer_id, order_date) জোড়া মেলানো
SELECT customer_id, order_id, order_date FROM orders
WHERE (customer_id, order_date) IN
      (SELECT customer_id, MAX(order_date) FROM orders GROUP BY customer_id)
ORDER BY customer_id;

৭ · FROM-এ subquery — derived table

কিছু প্রশ্নে দুবার aggregate করতে হয়: “প্রতিটি গ্রাহকের গড় অর্ডারের মূল্য কত?” প্রথমে প্রতিটি অর্ডারের মোট টাকা (order_items-এ SUM), তারপর গ্রাহকভিত্তিক সেই মোটগুলোর গড় (AVG). AVG(SUM(…)) লেখা যায় না — একই স্তরে দুটি aggregate চলে না। সমাধান: প্রথম ধাপটি FROM-এ একটি subquery বানান। এর ফল একটি অস্থায়ী টেবিলের মতো আচরণ করে, যাকে বলে derived table (SQL Server-এ “derived table”, Oracle-এ “inline view”).

-- ভেতরে: প্রতিটি অর্ডারের মোট টাকা; বাইরে: গ্রাহকভিত্তিক গড়
SELECT t.customer_id, COUNT(*) AS n_orders, ROUND(AVG(t.total), 2) AS avg_value
FROM (SELECT o.order_id, o.customer_id,
             SUM(i.quantity * i.unit_price) AS total
      FROM orders o JOIN order_items i ON i.order_id = o.order_id
      GROUP BY o.order_id, o.customer_id) AS t
GROUP BY t.customer_id
-- HAVING-এ subquery: সব অর্ডারের গড়ের চেয়ে বেশি
HAVING AVG(t.total) > (SELECT AVG(total)
                       FROM (SELECT SUM(quantity * unit_price) AS total
                             FROM order_items GROUP BY order_id))
ORDER BY t.customer_id;

যে অর্ডার গ্রাহকের নিজের গড়ের চেয়ে বড়

এবার derived table আর correlated subquery একসাথে: প্রতিটি অর্ডার তার নিজের গ্রাহকের গড় অর্ডারের সাথে মেলানো হবে। লক্ষ করুন, “অর্ডারের মোট” derived table-টি দুবার লিখতে হচ্ছে — একবার বাইরে, একবার ভেতরে:

SELECT o.order_id, o.customer_id, t.total
FROM orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS total
      FROM order_items GROUP BY order_id) AS t ON t.order_id = o.order_id
WHERE t.total > (SELECT AVG(t2.total)
                 FROM orders o2
                 JOIN (SELECT order_id, SUM(quantity * unit_price) AS total
                       FROM order_items GROUP BY order_id) AS t2
                   ON t2.order_id = o2.order_id
                 WHERE o2.customer_id = o.customer_id)
ORDER BY o.order_id;

ঠিক এই সমস্যার সমাধান CTE (common table expression). WITH দিয়ে derived table-টিকে একবার নাম দিয়ে উপরে লেখা যায়, তারপর যতবার খুশি ব্যবহার করা যায়। পর্ব ২৫-এ বিস্তারিত দেখবেন; এখানে শুধু চেহারাটি দেখুন:

WITH order_totals AS (
  SELECT o.order_id, o.customer_id, SUM(i.quantity * i.unit_price) AS total
  FROM orders o JOIN order_items i ON i.order_id = o.order_id
  GROUP BY o.order_id, o.customer_id
)
SELECT order_id, customer_id, total
FROM order_totals ot
WHERE total > (SELECT AVG(total) FROM order_totals
               WHERE customer_id = ot.customer_id)
ORDER BY order_id;

৮ · Subquery বনাম JOIN — আর পারফরম্যান্স

অনেক subquery JOIN দিয়েও লেখা যায়, আর উল্টোটাও। কিন্তু দুটি সবসময় হুবহু এক অর্থ দেয় না। সবচেয়ে বড় পার্থক্য duplicate: IN আর EXISTS বাইরের প্রতিটি সারি সর্বোচ্চ একবার রাখে (semi join), কিন্তু JOIN প্রতিটি মিলের জন্য একটি করে সারি বানায়।

-- subquery: ৭টি সারি, প্রতিজন একবার
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- JOIN: ১০টি সারি — Rahim ৩ বার, Karima ২ বার
SELECT c.name FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

-- JOIN-কে সমান করতে DISTINCT লাগে (৭টি সারি)
SELECT DISTINCT c.customer_id, c.name FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

-- NOT EXISTS
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- LEFT JOIN … IS NULL (পর্ব ১৯–২০)
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- SELECT তালিকায় correlated subquery
SELECT name,
       (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.customer_id) AS n_orders
FROM customers c;

-- LEFT JOIN + GROUP BY
SELECT c.name, COUNT(o.order_id) AS n_orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

কোনটি দ্রুত?

“Subquery ধীর, সবসময় JOIN লিখুন” — এই পরামর্শ এখনো শোনা যায়। এর উৎস মূলত পুরোনো MySQL. MySQL ৪.১ সংস্করণে (২০০৪-এর কাছাকাছি) subquery সমর্থন আসে, কিন্তু ৫.৬ (২০১৩)-এর আগে IN (subquery) প্রায়ই বাইরের প্রতিটি সারির জন্য নতুন করে চলত (EXPLAIN-এ “DEPENDENT SUBQUERY”). বড় টেবিলে তা ভয়ংকর ধীর। আধুনিক optimizer-গুলো এখন কোয়েরির অর্থ না বদলে তার গঠন বদলায়:

• Unnesting / decorrelation: IN আর EXISTS-কে semi join-এ, NOT EXISTS-কে anti join-এ বদলানো, যাতে hash join বা merge join (পর্ব ৪২) ব্যবহার করা যায়। PostgreSQL, SQL Server, Oracle আর আধুনিক MySQL (৮.০) এগুলো করে।
• একবার হিসাব: non-correlated subquery একবার চালিয়ে ফল রেখে দেওয়া। PostgreSQL-এর EXPLAIN-এ এটি InitPlan, আর প্রতি সারিতে চলা correlated subquery SubPlan.
• FROM-এর subquery মিশিয়ে ফেলা: সহজ derived table-কে বাইরের কোয়েরির সাথে এক করে ফেলা (SQLite-এর ভাষায় “query flattening”).

কিন্তু সব রূপান্তর সবসময় সম্ভব নয়। NOT IN-কে anti join-এ বদলানো কঠিন, কারণ NULL থাকলে অর্থ আলাদা — উপরের EXPLAIN-এর মতো চালালে SQLite NOT IN-এর জন্য একটি “LIST SUBQUERY” বানায়, PostgreSQL একটি hashed SubPlan, আর সেটি মেমরিতে না আঁটলে প্রতি সারিতে পুরো তালিকা খোঁজে। আর অংশ ৬-এর মতো aggregate-ওয়ালা correlated subquery অনেক DBMS এখনো প্রতি সারিতে চালায়। তাই নিয়ম: যেটি পড়তে পরিষ্কার, সেটি লিখুন; তারপর বড় ডেটায় query plan দেখে নিন। EXPLAIN পড়া পর্ব ৪১ আর ৪৩-এর বিষয়।

৯ · UPDATE আর DELETE-এ subquery

পর্ব ১৪-এর UPDATE আর DELETE-এর WHERE-এও subquery বসে। এতে অন্য টেবিলের তথ্য দেখে কোন সারি বদলাবে, তা ঠিক করা যায়। নিচে দুটি কাজ: যে পণ্য কখনো কোনো অর্ডারে আসেনি, তার দাম ১০% বাড়ানো (ধরুন সরবরাহকারী নতুন দাম দিয়েছে, আর পুরোনো দামে কোনো বিক্রির হিসাব নেই); আর বাতিল অর্ডারের লাইনগুলো মুছে ফেলা।

-- কখনো বিক্রি হয়নি এমন পণ্যের দাম ১০% বাড়ান
UPDATE products SET price = ROUND(price * 1.10)
WHERE product_id NOT IN (SELECT product_id FROM order_items);

SELECT product_id, name, price FROM products WHERE product_id = 103;

-- বাতিল (cancelled) অর্ডারের লাইনগুলো মুছুন
DELETE FROM order_items
WHERE order_id IN (SELECT order_id FROM orders WHERE status = 'cancelled');

SELECT COUNT(*) AS items_left FROM order_items;

১০ · AI-এর যুগে subquery

AI-কে ইংরেজি বা বাংলায় প্রশ্ন বললে সে প্রায়ই nested subquery দিয়ে উত্তর লেখে, আর কোয়েরিটি চলেও। সমস্যা হলো, subquery-র ভুল চোখে পড়ে কম: কোনো ত্রুটি আসে না, শুধু কয়েকটি সারি কম বা বেশি আসে। নিচের ভুলগুলো বারবার দেখা যায়:

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

• প্রতিটি subquery আলাদা করে চালিয়ে দেখেছি সেটি কয়টি সারি আর কলাম দেয়
• প্রতিটি NOT IN-এর ভেতরের কলাম NOT NULL, নয়তো NOT EXISTS-এ বদলেছি
• correlated subquery-র প্রতিটি কলামের আগে alias আছে, আর ২–৩টি সারি হাতে trace করেছি
• subquery আর JOIN সংস্করণের সারি-সংখ্যা মিলিয়েছি
• খালি ফল, NULL আর সমান মান (tie) — এই তিন কিনারায় পরীক্ষা করেছি
• বড় টেবিলে EXPLAIN দেখে নিশ্চিত হয়েছি correlated অংশ index ব্যবহার করছে

SELECT p.name, p.stock FROM products p
WHERE p.stock = (SELECT COUNT(*) FROM order_items i
                 WHERE i.product_id = p.product_id);

SELECT p.name, p.stock FROM products p
JOIN (SELECT product_id, COUNT(*) AS n FROM order_items
      GROUP BY product_id) t ON t.product_id = p.product_id
WHERE p.stock = t.n;

SELECT s.name FROM students s
WHERE NOT EXISTS (                       -- এমন কোনো কোর্স নেই…
  SELECT 1 FROM courses c
  WHERE c.course_id LIKE 'CSE3%'
    AND NOT EXISTS (                     -- …যা s নেয়নি
      SELECT 1 FROM enrollments e
      WHERE e.student_id = s.student_id
        AND e.course_id = c.course_id));