সাবকোয়েরি, EXISTS আর কোরিলেটেড কোয়েরি

কিছু প্রশ্ন করার আগেই আরেকটা উত্তর লাগে। "কোন প্রোডাক্টের দাম গড়ের চেয়ে বেশি?" — আগে গড়টা লাগবে। "কোন কাস্টমাররা কখনো অর্ডার করেননি?" — আগে লাগবে যারা করেছেন তাদের তালিকা। সাবকোয়েরি (subquery) হলো আরেকটা স্টেটমেন্টের ভেতরে ব্র্যাকেটে লেখা একটা SELECT, যা সেই প্রথম উত্তরটা জোগায়। আগের পাতায় ফ্যান-আউটের ফাঁদ সারাতে আপনি এরকম একটা ব্যবহারও করেছেন।

ডেটার কাজে সাবকোয়েরি সবখানে: ডেটা থেকেই হিসাব করা একটা সীমার ওপরের সারিগুলো রাখা, ট্রেনিং সেটকে শুধু সেই ইউজারদের মধ্যে রাখা যারা আরেকটা টেবিলে আছে, প্রতিটা মডেলের স্কোর তার নিজের গড়ের সাথে তুলনা করা। কোন প্রশ্নে সাবকোয়েরি, কোনটায় জয়েন আর কোনটায় EXISTS সবচেয়ে পরিষ্কার — আর NULL-এর যে ফাঁদটা NOT IN-এর ফলাফল খালি করে দেয় — এই পাতা সেটা নিয়েই।

যা শিখবেন

• স্কেলার সাবকোয়েরি, আর এক-সারি বনাম বহু-সারি সাবকোয়েরির পার্থক্য
• WHERE, FROM (ডিরাইভড টেবিল) আর SELECT-এ সাবকোয়েরি
• কোরিলেটেড সাবকোয়েরি: যে সাবকোয়েরি "প্রতি সারিতে" চলে
• EXISTS / NOT EXISTS, IN বনাম EXISTS, আর NULL থাকলে NOT IN কেন ভেঙে পড়ে
• সাবকোয়েরিকে জয়েন হিসেবে লেখা, আর সবচেয়ে পরিষ্কার রূপটা বাছাই

স্কেলার সাবকোয়েরি: একটা মান

স্কেলার সাবকোয়েরি (scalar subquery) ঠিক এক সারি আর এক কলাম ফেরত দেয় — একটাই মান — তাই যেখানে একটা মান বসতে পারে সেখানেই এটা বসতে পারে। প্রোডাক্টের গড় দাম:

SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

+-----------+
| avg_price |
+-----------+
|   5254.55 |
+-----------+

এই কোয়েরিটা ব্র্যাকেটে WHERE-এর ভেতরে বসান, ডেটাবেস আগে এটা হিসাব করবে, তারপর মানটা ব্যবহার করবে:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

+-----------------------------+-------+
| name                        | price |
+-----------------------------+-------+
| 27-inch Monitor             | 18500 |
| AI Engineering Bootcamp     | 15000 |
| Noise-Cancelling Headphones |  7500 |
+-----------------------------+-------+

WHERE price > AVG(price) লেখা যায় না: WHERE-এ অ্যাগ্রিগেট চলে না, কারণ WHERE একবারে একটা সারি দেখে (দেখুন অ্যাগ্রিগেট, GROUP BY ও HAVING)। সাবকোয়েরি পুরো টেবিলের ওপর আলাদা একটা কোয়েরি, তাই সে অ্যাগ্রিগেট করতে পারে। মানটা হার্ড-কোডও করা নয়: কাল নতুন প্রোডাক্ট যোগ হলে সীমাটাও ডেটার সাথে সরে যাবে।

স্কেলার সাবকোয়েরি SELECT তালিকাতেও একটা কলাম হিসেবে কাজ করে:

SELECT name, price,
       price - (SELECT MIN(price) FROM products) AS above_cheapest
FROM products
WHERE category_id = 2
ORDER BY price;

+-----------------------------+-------+----------------+
| name                        | price | above_cheapest |
+-----------------------------+-------+----------------+
| Wireless Mouse              |   900 |              0 |
| Mechanical Keyboard         |  4500 |           3600 |
| Noise-Cancelling Headphones |  7500 |           6600 |
| 27-inch Monitor             | 18500 |          17600 |
+-----------------------------+-------+----------------+

এক-সারি বনাম বহু-সারি: = চায় একটা মান

=, >, < তুলনা করে একটা মানের সাথে। সাবকোয়েরি কয়েকটা সারি দিলে কী হয়? তিন ডায়ালেক্ট এখানে একমত নয়, আর SQLite-এর উত্তরটাই বিপজ্জনক:

SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'delivered');

+--------------+
| name         |
+--------------+
| Nadia Rahman |
+--------------+

ডেলিভারড অর্ডার এগারোটা, অথচ SQLite চুপচাপ সাবকোয়েরির প্রথম সারিটা নিয়েছে আর বাকিগুলো উপেক্ষা করেছে। MySQL আর PostgreSQL রাজি হয় না:

-- MySQL
SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'delivered');

Error: ERROR 1242: Subquery returns more than 1 row

-- PostgreSQL
SELECT name FROM customers
WHERE customer_id = (SELECT customer_id FROM orders WHERE status = 'delivered');

Error: more than one row returned by a subquery used as an expression

সাবকোয়েরি যখন অনেক সারি দিতে পারে, তখন তুলনা করুন IN দিয়ে, যার মানে "এই তালিকার যেকোনো মানের সমান":

SELECT name, city
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-05-01')
ORDER BY name;

+---------------+------------+
| name          | city       |
+---------------+------------+
| Farhana Akter | Dhaka      |
| Imran Hossain | Khulna     |
| Mitu Das      | Dhaka      |
| Tanvir Ahmed  | Chattogram |
+---------------+------------+

ওই সময়ে Tanvir আর Farhana একাধিকবার অর্ডার করেছেন, তবু প্রত্যেকে একবারই এসেছেন: IN শুধু জানতে চায় মিল আছে কিনা, জয়েনের মতো সারি গুণ করে না।

FROM-এ সাবকোয়েরি: ডিরাইভড টেবিল

FROM-এ সাবকোয়েরি হলো একটা অস্থায়ী টেবিল, যা একটা কোয়েরির জন্যই থাকে। দুবার অ্যাগ্রিগেট করার উপায় এটাই — যেমন গড় অর্ডার মূল্য, যার জন্য আগে লাগে প্রতিটা অর্ডারের মোট:

SELECT ROUND(AVG(order_total), 2) AS avg_order_value, MAX(order_total) AS biggest_order
FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total
      FROM order_items
      GROUP BY order_id) AS t;

+-----------------+---------------+
| avg_order_value | biggest_order |
+-----------------+---------------+
|         7396.43 |         18500 |
+-----------------+---------------+

order_items-এর ওপর সরাসরি AVG(quantity * unit_price) দিলে আসত গড় আইটেম মূল্য — একটা আলাদা সংখ্যা। প্রতিটা ডিরাইভড টেবিলকে একটা অ্যালিয়াস দিন (AS t)। SQLite আর PostgreSQL 16+ অ্যালিয়াস ছাড়াও মেনে নেয়, কিন্তু MySQL (আর পুরোনো PostgreSQL) নেয় না:

-- MySQL
SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total
      FROM order_items
      GROUP BY order_id);

Error: ERROR 1248: Every derived table must have its own alias

-- PostgreSQL
SELECT ROUND(AVG(order_total), 2) AS avg_order_value
FROM (SELECT order_id, SUM(quantity * unit_price) AS order_total
      FROM order_items
      GROUP BY order_id);

+-----------------+
| avg_order_value |
+-----------------+
|         7396.43 |
+-----------------+

কোরিলেটেড সাবকোয়েরি

এ পর্যন্ত সাবকোয়েরিগুলো একবারই, নিজে নিজে চলেছে। কোরিলেটেড সাবকোয়েরি (correlated subquery) বাইরের কোয়েরির একটা কলাম ব্যবহার করে, তাই বাইরের প্রতিটা সারির জন্য তার উত্তর আলাদা। কোন প্রোডাক্টের দাম তার নিজের ক্যাটাগরির গড়ের চেয়ে বেশি?

SELECT p.name, p.price, p.category_id
FROM products p
WHERE p.price > (SELECT AVG(p2.price)
                 FROM products p2
                 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price;

+---------------------------+-------+-------------+
| name                      | price | category_id |
+---------------------------+-------+-------------+
| Hands-On Machine Learning |  2500 |           1 |
| 27-inch Monitor           | 18500 |           2 |
| AI Engineering Bootcamp   | 15000 |           3 |
| USB-C Hub                 |  2200 |           4 |
+---------------------------+-------+-------------+

ব্র্যাকেটের ভেতরের p.category_id বাইরের সারির। ধারণাগতভাবে ডেটাবেস সারি ধরে ধরে এগোয়:

একই টেবিলের দুটো অ্যালিয়াস (বাইরে p, ভেতরে p2) দ্বিধা দূর করে। AI-এর কাজে ঠিক এই আকারের কোয়েরি উত্তর দেয় "কোন ইভ্যাল রানের স্কোর সেই মডেলেরই গড়ের চেয়ে কম?"।

SELECT-এ কোরিলেটেড সাবকোয়েরি প্রতি সারিতে একটা মান হিসাব করে — প্রতি কাস্টমারের দ্রুত একটা সারাংশের জন্য সুবিধাজনক:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS orders,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c
ORDER BY c.customer_id;

+-----------------+--------+------------+
| name            | orders | last_order |
+-----------------+--------+------------+
| Nadia Rahman    |      3 | 2026-04-19 |
| Tanvir Ahmed    |      3 | 2026-05-21 |
| Farhana Akter   |      3 | 2026-06-09 |
| Rafiq Islam     |      1 | 2026-02-25 |
| Sadia Chowdhury |      1 | 2026-03-15 |
| Imran Hossain   |      2 | 2026-06-02 |
| Mitu Das        |      1 | 2026-05-06 |
| Karim Uddin     |      0 | NULL       |
+-----------------+--------+------------+

LEFT JOIN ছাড়াই Karim পেয়েছেন ০, আর দুটো সাবকোয়েরি একে অপরের সাথে ফ্যান-আউট করতে পারে না। খরচটা হলো: ধারণাগতভাবে প্রতিটা সাবকোয়েরি প্রতি কাস্টমারে একবার চলে। অপ্টিমাইজার প্রায়ই এটাকে জয়েনে বদলে নেয়, তবে বড় টেবিলে অনেকগুলো সাবকোয়েরি থাকলে একটা GROUP BY জয়েন (বা ডিরাইভড টেবিল) সাধারণত দ্রুত — EXPLAIN দিয়ে যাচাই করুন, যা অ্যাডভান্সড টিউটোরিয়ালে আছে।

EXISTS আর NOT EXISTS

EXISTS (subquery) সত্য হয় যদি সাবকোয়েরি অন্তত একটা সারি দেয়। সে কী সিলেক্ট করছে তাতে কিছু আসে যায় না, তাই সবাই লেখেন SELECT 1। এটা প্রায় সবসময়ই কোরিলেটেড:

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 c.customer_id;

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           8 | Karim Uddin |
+-------------+-------------+

উত্তরটা LEFT, RIGHT, FULL, CROSS আর সেলফ জয়েন পাতার LEFT JOIN … IS NULL অ্যান্টি-জয়েনের মতোই, কিন্তু পড়তে প্রশ্নটার মতো লাগে: "যেসব কাস্টমারের কোনো অর্ডার নেই"। প্রথম মেলা সারি পেলেই ডেটাবেস থামতে পারে, তাই EXISTS সাধারণত কম খরচের। দরকারমতো সাবকোয়েরিটা যত খুশি জটিলও হতে পারে — কোন প্রোডাক্ট কোনো ক্যানসেল অর্ডারে আছে?

SELECT p.name
FROM products p
WHERE EXISTS (SELECT 1
              FROM order_items oi
              JOIN orders o ON o.order_id = oi.order_id
              WHERE oi.product_id = p.product_id AND o.status = 'cancelled');

+-----------------+
| name            |
+-----------------+
| 27-inch Monitor |
+-----------------+

IN বনাম EXISTS, আর NOT IN-এর ফাঁদ

"মিল আছে" প্রশ্নে IN আর EXISTS একই সারি দেয়, আর আধুনিক অপ্টিমাইজার সাধারণত দুটো একইভাবে চালায়; যেটা পড়তে ভালো সেটা নিন। "কোনো মিল নেই" প্রশ্নে এরা আলাদা, NULL-এর কারণে। কোন ক্যাটাগরিতে কোনো প্রোডাক্ট নেই?

SELECT name FROM categories
WHERE category_id NOT IN (SELECT category_id FROM products);

+------+
| name |
+------+
+------+

কিছুই না — অথচ Furniture-এ কোনো প্রোডাক্ট নেই। সাবকোয়েরির তালিকায় আছে Gift Card-এর NULL। 5 NOT IN (1, 2, 3, 4, NULL) মানে 5 <> 1 AND … AND 5 <> NULL, আর 5 <> NULL অজানা, তাই কোনো ক্যাটাগরির জন্যই পুরো পরীক্ষাটা কখনো সত্য হয় না। তালিকায় একটা NULL থাকলেই ফলাফল খালি। NOT EXISTS-এর এই সমস্যা নেই, কারণ সে শুধু জানতে চায় মেলা সারি আছে কিনা:

SELECT name FROM categories c
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.category_id);

+-----------+
| name      |
+-----------+
| Furniture |
+-----------+

তিন ডায়ালেক্টেই এখানে আচরণ একই; এটা স্ট্যান্ডার্ড SQL-এর যুক্তি, বাগ নয়। সাবকোয়েরির সাথে NOT IN ব্যবহার করলে NULL-গুলো ছেঁকে ফেলুন: … NOT IN (SELECT category_id FROM products WHERE category_id IS NOT NULL)। ভালো অভ্যাস: NOT EXISTS ব্যবহার করুন।

সাবকোয়েরি নাকি জয়েন?

অনেক সাবকোয়েরি জয়েন হিসেবে লেখা যায়, উল্টোটাও। তবে সবসময় হুবহু এক নয়। যেসব ঢাকার কাস্টমার অর্ডার করেছেন, জয়েন দিয়ে:

SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.city = 'Dhaka'
ORDER BY c.name;

+---------------+
| name          |
+---------------+
| Farhana Akter |
| Farhana Akter |
| Farhana Akter |
| Mitu Das      |
| Nadia Rahman  |
| Nadia Rahman  |
| Nadia Rahman  |
+---------------+

জয়েন প্রতিটা অর্ডারের জন্য একটা সারি দেয়। DISTINCT যোগ করা যেত, কিন্তু তাতে চাপা পড়ে যায় যে আপনি ভুল গ্রেইনে জয়েন করেছেন। EXISTS গ্রেইন রাখে প্রতি কাস্টমারে এক সারি:

SELECT c.name
FROM customers c
WHERE c.city = 'Dhaka'
  AND EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
ORDER BY c.name;

+---------------+
| name          |
+---------------+
| Farhana Akter |
| Mitu Das      |
| Nadia Rahman  |
+---------------+

আধুনিক ডেটাবেসে এই রূপগুলোর গতির পার্থক্য সাধারণত সামান্য; আগে সবচেয়ে পরিষ্কারটা লিখুন, ধীর হলে তবেই মাপুন।

সাধারণ ভুল

• কয়েকটা সারি দিতে পারে এমন সাবকোয়েরির সাথে =। SQLite চুপচাপ প্রথম সারিটা নেয়; MySQL আর PostgreSQL এরর দেয়। সমাধান: IN ব্যবহার করুন, অথবা সাবকোয়েরিকে ইচ্ছে করে এক সারি দেওয়ান (একটা অ্যাগ্রিগেট, বা কি দিয়ে ফিল্টার)।
• NULL হতে পারে এমন কলামের ওপর NOT IN। একটা NULL-ই ফলাফল খালি করে দেয়। সমাধান: NOT EXISTS, অথবা সাবকোয়েরির ভেতরে WHERE col IS NOT NULL।
• কোরিলেশন ভুলে যাওয়া। WHERE p2.category_id = p.category_id ছাড়া WHERE price > (SELECT AVG(price) FROM products p2) তুলনা করে সামগ্রিক গড়ের সাথে, কোনো এরর ছাড়াই। সমাধান: টেবিলের দুটো কপিকেই অ্যালিয়াস দিন, আর দেখুন ভেতরের WHERE-এ বাইরের অ্যালিয়াস আছে কিনা।
• অ্যালিয়াস ছাড়া ডিরাইভড টেবিল। SQLite-এ চলে, MySQL-এ ERROR 1248। সমাধান: সবসময় (…) AS t লিখুন।
• জয়েন দিয়ে ফিল্টার করে তারপর DISTINCT দিয়ে পরিষ্কার করা। এতে গ্রেইনের সমস্যা চাপা পড়ে, আর সত্যিই আলাদা সারিও এক হয়ে যেতে পারে। সমাধান: অন্য টেবিলের কোনো কলাম না লাগলে EXISTS দিয়ে ফিল্টার করুন।

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

1. সহজ: প্রোডাক্টের গড় দামের চেয়ে সস্তা প্রোডাক্টগুলো দেখান, সবচেয়ে সস্তাটা আগে (সমান হলে নাম অনুযায়ী)।
2. মাঝারি: কোরিলেটেড সাবকোয়েরি দিয়ে প্রতিটা বিভাগের (department) সবচেয়ে বেশি বেতনের কর্মী বের করুন: নাম, বেতন, বিভাগ, বিভাগ অনুযায়ী সাজিয়ে।
3. কঠিন: ক্যানসেল হয়নি এমন অর্ডারে প্রত্যেক কাস্টমার মোট কত খরচ করেছেন হিসাব করুন; তারপর খুঁজুন কাদের খরচ, অর্ডার করেছেন এমন সব কাস্টমারের গড় খরচের চেয়ে বেশি। নাম আর টাকার অঙ্ক দেখান, বড়টা আগে। (ইঙ্গিত: প্রতি কাস্টমারের মোটের একটা ডিরাইভড টেবিল, দুবার ব্যবহার।)

উত্তর

-- 1
SELECT name, price
FROM products
WHERE price < (SELECT AVG(price) FROM products)
ORDER BY price, name;

-- 2
SELECT name, salary, department
FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees e2 WHERE e2.department = e.department)
ORDER BY department;

+-----------------+--------+-------------+
| name            | salary | department  |
+-----------------+--------+-------------+
| Lamia Karim     | 160000 | Data        |
| Hasan Mahmud    | 180000 | Engineering |
| Ayesha Siddiqua | 250000 | Management  |
+-----------------+--------+-------------+

-- 3
SELECT c.name, t.spent
FROM (SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.order_id
      WHERE o.status <> 'cancelled'
      GROUP BY o.customer_id) AS t
JOIN customers c ON c.customer_id = t.customer_id
WHERE t.spent > (SELECT AVG(spent)
                 FROM (SELECT o.customer_id, SUM(oi.quantity * oi.unit_price) AS spent
                       FROM orders o
                       JOIN order_items oi ON oi.order_id = o.order_id
                       WHERE o.status <> 'cancelled'
                       GROUP BY o.customer_id) AS t2)
ORDER BY t.spent DESC;

+---------------+-------+
| name          | spent |
+---------------+-------+
| Nadia Rahman  | 23600 |
| Tanvir Ahmed  | 22500 |
| Imran Hossain | 19950 |
+---------------+-------+

অর্ডার করেছেন এমন ছয়জন কাস্টমারের গড় ১৪,১৭৫। একই ডিরাইভড টেবিল দুবার লেখা বেখাপ্পা; CTE (WITH; শিখবেন অ্যাডভান্সড টিউটোরিয়ালের প্রথম পাতা CTE আর রিকার্সিভ কোয়েরি-তে) একবার নাম দিয়ে বারবার ব্যবহার করতে দেয়।

সারসংক্ষেপ

• সাবকোয়েরি হলো ব্র্যাকেটে লেখা একটা SELECT, যা বাইরের কোয়েরিকে একটা মান, একটা তালিকা বা একটা টেবিল জোগায়।
• স্কেলার সাবকোয়েরি বসে যেখানে একটা মান বসে; কয়েকটা সারি দিলে SQLite চুপচাপ প্রথমটা নেয়, MySQL আর PostgreSQL এরর দেয় — তালিকার জন্য IN ব্যবহার করুন।
• FROM-এ ডিরাইভড টেবিল দিয়ে দুবার অ্যাগ্রিগেট করা যায়; সবসময় অ্যালিয়াস দিন (MySQL-এ বাধ্যতামূলক)।
• কোরিলেটেড সাবকোয়েরি বাইরের সারিকে রেফার করে আর "নিজের গ্রুপের তুলনায়" ধরনের প্রশ্নের উত্তর দেয়।
• EXISTS/NOT EXISTS গ্রেইন না বদলে মেলা সারি আছে কিনা দেখে; তালিকায় NULL থাকলে NOT IN কিছুই ফেরত দেয় না।

এরপর: UNION, INTERSECT ও EXCEPT কোয়েরি একটার ভেতরে আরেকটা না বসিয়ে পুরো ফলাফলগুলো জোড়ে: দুটো কোয়েরির সারি একটার ওপর আরেকটা সাজানো, আর দুটো ফলাফলে কোন সারি দুজনেরই আছে বা নেই তা খোঁজা।