CTE (Common Table Expressions) এবং Recursive Queries

CTE (Common Table Expressions) এবং Recursive Queries T-SQL-এ অত্যন্ত শক্তিশালী টুল যা ডেটাবেসে জটিল কুয়েরি অপারেশন সহজ এবং কার্যকরী করে তোলে। CTE-গুলি সাধারণত WITH ক্লজ দিয়ে শুরু হয় এবং একটি এক্সপ্রেশন হিসেবে ডেটা সেট প্রদান করে, যা কুয়েরির মধ্যে ব্যবহার করা যায়। Recursive Queries CTE-এর একটি বিশেষ বৈশিষ্ট্য যা পুনরাবৃত্তি কুয়েরি অপারেশন সমর্থন করে।

১. Common Table Expressions (CTE)

CTE (Common Table Expression) হলো একটি টেম্পোরারি ফলাফল সেট যা একটি SELECT স্টেটমেন্ট, INSERT, UPDATE, বা DELETE এর মধ্যে ব্যবহার করা হয়। CTE ডেটাবেসে এক্সপ্রেশন বা সাব-কুয়েরি তৈরি করতে সাহায্য করে যা কোডকে আরও পরিষ্কার এবং পুনঃব্যবহারযোগ্য করে।

CTE এর সিনট্যাক্স:

WITH CTE_Name AS
(
    -- CTE Query
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition
)
-- Main Query
SELECT * FROM CTE_Name;

• WITH: CTE ডিফাইন করতে ব্যবহৃত কিওয়ার্ড।
• CTE_Name: CTE এর নাম।
• Main Query: CTE থেকে ফলাফল সংগ্রহ করার জন্য প্রধান কুয়েরি।

CTE উদাহরণ:

WITH DepartmentEmployee AS
(
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE Department = 'IT'
)
SELECT * FROM DepartmentEmployee;

এখানে:

• DepartmentEmployee CTE তৈরি করা হয়েছে যা শুধুমাত্র IT ডিপার্টমেন্টের কর্মচারীদের তথ্য রিটার্ন করে।
• SELECT স্টেটমেন্টের মাধ্যমে CTE থেকে ডেটা সংগ্রহ করা হয়েছে।

২. Recursive Queries

Recursive Queries হল এমন কুয়েরি যা নিজেকে পুনরায় কল করে, অর্থাৎ, একটি কুয়েরি অন্য কুয়েরি বা তার নিজস্ব ফলাফল পুনরায় ব্যবহারের মাধ্যমে কাজ করে। এটি সাধারণত CTE এর সাহায্যে করা হয়। Recursive CTE এমন একটি কুয়েরি যা একটি বেস কেস (base case) থেকে শুরু করে এবং ধাপে ধাপে নিজেকে পুনরায় কল করে।

Recursive CTE এর সিনট্যাক্স:

WITH RecursiveCTE AS
(
    -- Base Case (Non-recursive part)
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition

    UNION ALL

    -- Recursive Case
    SELECT t.Column1, t.Column2
    FROM TableName t
    JOIN RecursiveCTE r ON t.SomeColumn = r.SomeColumn
)
SELECT * FROM RecursiveCTE;

• Base Case: প্রথম স্টেপে একটি স্ট্যাটিক বা প্রাথমিক ডেটা সেট নির্বাচন করা হয়।
• Recursive Case: পরবর্তী স্টেপে আগের ফলাফলগুলির ওপর ভিত্তি করে আবার কুয়েরি চালানো হয়। এটি UNION ALL বা UNION দ্বারা যুক্ত করা হয়।

Recursive Query উদাহরণ:

ধরা যাক, একটি Employee টেবিল আছে যার মধ্যে কর্মচারীদের EmployeeID এবং তাদের ManagerID রয়েছে, এবং আপনি চান যে প্রতিটি কর্মচারীর ম্যানেজারের হায়ারার্কি দেখাতে:

WITH EmployeeHierarchy AS
(
    -- Base case: Starting from the top-level manager
    SELECT EmployeeID, FirstName, LastName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL  -- Assuming top-level managers have no manager

    UNION ALL

    -- Recursive case: Joining the employee with their manager
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

এখানে:

• প্রথমে ManagerID IS NULL শর্তের মাধ্যমে বেস কেস (প্রথম স্তরের ম্যানেজার) নির্বাচন করা হয়েছে।
• এরপর UNION ALL দিয়ে EmployeeHierarchy CTE-এর মধ্যে কর্মচারী এবং তাদের ম্যানেজারদের হায়ারার্কি তৈরি করা হয়েছে।

৩. CTE এবং Recursive Query এর ব্যবহারের ক্ষেত্র

1. জটিল ডেটা প্রক্রিয়া:
   • CTE এবং Recursive Queries দিয়ে আপনি ডেটাবেসে জটিল ডেটা প্রক্রিয়া সহজে করতে পারেন, যেমন হায়ারার্কি ডেটা বা ফ্ল্যাট টেবিল থেকে গাছের (tree) কাঠামো তৈরি করা।
2. Self-Referencing বা Hierarchical Data:
   • Recursive CTE সাধারণত সেলফ-রেফারেন্সিং (self-referencing) ডেটা, যেমন কর্মচারী এবং ম্যানেজার সম্পর্ক, বা ক্যাটেগরি ও সাবক্যাটেগরি সম্পর্কের জন্য ব্যবহৃত হয়।
3. ডেটাবেস ডিজাইন অপ্টিমাইজেশন:
   • CTE ব্যবহার করে ডেটাবেসের ডিজাইনকে আরও পরিষ্কার এবং সহজবোধ্য করা যায়, কারণ এটি কোডের পুনঃব্যবহার এবং উন্নত পারফরম্যান্সের জন্য সহায়তা করে।
4. ব্যাপক ডেটা অপারেশন:
   • যখন আপনি একাধিক টেবিল থেকে ডেটা সংগ্রহ করতে চান বা ডেটার মধ্যে ম্যানিপুলেশন করতে চান, তখন CTE-গুলি সেগুলি একত্রিত করতে সহজ এবং কার্যকরী উপায়।

সারাংশ

• CTE (Common Table Expressions) SQL কুয়েরিতে একটি টেম্পোরারি ফলাফল তৈরি করে, যা কোড পরিষ্কার এবং পুনঃব্যবহারযোগ্য করে তোলে।
• Recursive Queries CTE-এর সাহায্যে নিজেকে পুনরায় কল করতে পারে, এবং এটি বিশেষত হায়ারার্কি ডেটা (যেমন, কর্মচারী এবং ম্যানেজার সম্পর্ক) পরিচালনার জন্য ব্যবহৃত হয়।

CTE এবং Recursive Queries T-SQL-এ শক্তিশালী বৈশিষ্ট্য যা ডেটাবেস কুয়েরি অপারেশনকে সহজ এবং কার্যকরী করে তোলে, বিশেষ করে জটিল বা হায়ারার্কিকাল ডেটার ক্ষেত্রে।

CTE (Common Table Expression) হলো T-SQL-এ একটি শক্তিশালী ফিচার যা একটি অনুক্রমিক বা লজিক্যাল কোয়েরি তৈরি করতে ব্যবহৃত হয়। এটি একটি অস্থায়ী রেজাল্ট সেট তৈরি করে, যা পরে পুরো কুয়েরি বা স্টোরড প্রসিডিউরের মধ্যে পুনঃব্যবহার করা যায়। CTE ব্যবহার করে একাধিক অংশে কোড ভাগ করে, এবং লজিক্যালভাবে সেগুলি একত্রিত করা যায়, যা কোডের পাঠযোগ্যতা এবং রক্ষণাবেক্ষণ সহজ করে।

CTE এর সিনট্যাক্স

WITH CTE_Name AS
(
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM CTE_Name;

• WITH: এটি CTE এর ঘোষণা শুরু করে।
• CTE_Name: CTE এর জন্য একটি নাম যা পরবর্তী কোয়েরি ব্যবহার করবে।
• SELECT: CTE-এ যে ডেটা থাকবে, তা নির্বাচন করতে ব্যবহৃত হয়।
• CTE এর পর, আপনি একটি SELECT স্টেটমেন্ট চালাতে পারেন যা CTE-এ সঞ্চিত ডেটা ব্যবহার করবে।

CTE এর প্রয়োজনীয়তা

1. কোডের পাঠযোগ্যতা উন্নত করা:

   • CTE ব্যবহার করে আপনি কোডটি বেশি পরিষ্কার এবং সহজে পাঠযোগ্য করতে পারেন, কারণ এটি অস্থায়ী টেবিল তৈরি করতে সাহায্য করে যা আপনার কুয়েরি কোডকে ছোট এবং আরও বোধগম্য করে তোলে।

   উদাহরণ:

   WITH DepartmentSales AS
   (
       SELECT Department, SUM(SalesAmount) AS TotalSales
       FROM Sales
       GROUP BY Department
   )
   SELECT Department, TotalSales
   FROM DepartmentSales
   WHERE TotalSales > 10000;
   

2. রিকার্সিভ কোয়েরি:

   • CTE ব্যবহার করে রিকার্সিভ কোয়েরি লেখা যায়, যেখানে একটি কোয়েরি নিজেকে পুনরাবৃত্তি (recursive) করতে পারে। এটি হায়ারারকিক্যাল ডেটা যেমন অর্গানাইজেশনাল স্ট্রাকচার বা ফোল্ডার স্ট্রাকচার এর জন্য উপযুক্ত।

   উদাহরণ:

   WITH RecursiveCTE AS
   (
       SELECT EmployeeID, ManagerID, Name
       FROM Employees
       WHERE ManagerID IS NULL
       
       UNION ALL
       
       SELECT e.EmployeeID, e.ManagerID, e.Name
       FROM Employees e
       INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
   )
   SELECT * FROM RecursiveCTE;
   

   এখানে:

   • প্রথমে ManagerID IS NULL শর্তের মাধ্যমে শীর্ষ কর্মকর্তাদের নির্বাচিত করা হয়েছে।
   • তারপর UNION ALL দিয়ে এটি পুনরাবৃত্তি (recursive) করে, যেখানে প্রতিটি কর্মচারীর ManagerID অনুযায়ী তাদের ম্যানেজারের অধীনে কর্মচারী নির্বাচন করা হয়।

3. পুনঃব্যবহারযোগ্য লজিক:

   • CTE ব্যবহার করে একই লজিক একাধিক জায়গায় পুনরায় ব্যবহার করা যেতে পারে। এটি কোডের পুনঃব্যবহারযোগ্যতা বাড়ায় এবং ভুলের সম্ভাবনা কমিয়ে দেয়।

   উদাহরণ:

   WITH SalesData AS
   (
       SELECT ProductID, SUM(SalesAmount) AS TotalSales
       FROM Sales
       GROUP BY ProductID
   )
   SELECT ProductID, TotalSales
   FROM SalesData
   WHERE TotalSales > 5000;
   

4. জটিল কুয়েরি সহজ করা:
   • CTE ব্যবহারের মাধ্যমে একাধিক অংশের জটিল কুয়েরি সহজ করা যায়। এটি আপনার কুয়েরি গঠনকে আরও পরিষ্কার করে তোলে এবং সাবকোয়েরির সংখ্যা কমাতে সাহায্য করে।

5. গণনা ও বিশ্লেষণ সহজ করা:

   • CTE ব্যবহার করে আপনি সহজেই একাধিক সিলেক্ট কুয়েরি বা গণনা করতে পারেন, যেমন অ্যাগ্রিগেট ফাংশন (SUM, AVG, COUNT) বা অন্যান্য জটিল বিশ্লেষণ।

   উদাহরণ:

   WITH DepartmentSales AS
   (
       SELECT Department, SUM(SalesAmount) AS TotalSales
       FROM Sales
       GROUP BY Department
   )
   SELECT Department, TotalSales
   FROM DepartmentSales
   WHERE TotalSales > (SELECT AVG(TotalSales) FROM DepartmentSales);
   

6. স্টোরড প্রসিডিউর বা ফাংশনের মধ্যে ব্যবহার:
   • CTE ব্যবহার করা যায় স্টোরড প্রসিডিউর বা ফাংশনের মধ্যে, যা পুনরায় ব্যবহারযোগ্য এবং কার্যকরী কোড তৈরি করতে সাহায্য করে।

CTE এর সুবিধা

1. কোড সহজ ও পরিষ্কার রাখা: CTE ব্যবহার করে জটিল কোয়েরি সহজ করে তোলা যায় এবং কোড পুনঃব্যবহারযোগ্য হয়।
2. রিকার্সিভ কুয়েরি সমাধান: CTE ব্যবহার করে সহজে রিকার্সিভ কুয়েরি করা যায়, যা হায়ারারকিক্যাল ডেটা মডেলিংয়ে সহায়ক।
3. পুনঃব্যবহারযোগ্য লজিক: একই কোড একাধিকবার ব্যবহার করা যায়, যেমন WITH স্টেটমেন্টে সংজ্ঞায়িত এক্সপ্রেশন বা গণনা পুনরায় ব্যবহার করা।

সারাংশ

CTE (Common Table Expression) T-SQL-এ একটি শক্তিশালী ফিচার যা কোডের পাঠযোগ্যতা, রিকার্সিভ কোয়েরি, এবং ডেটার পুনঃব্যবহারযোগ্যতা বাড়ায়। CTE ব্যবহার করে আমরা অস্থায়ী টেবিল তৈরি করতে পারি, যা পরে পুরো কুয়েরি বা স্টোরড প্রসিডিউরের মধ্যে পুনঃব্যবহার করা যায়। CTE-এর প্রয়োজনীয়তা বিশেষত জটিল কোয়েরি বা ডেটা বিশ্লেষণের ক্ষেত্রে, যেখানে ডেটা প্রক্রিয়া করা সহজ এবং দক্ষভাবে পরিচালনা করা যায়।

Recursive CTE হল SQL Server-এ একটি বিশেষ ধরনের Common Table Expression (CTE) যা একটি কুয়েরি (Query) নিজেকে পুনরায় কল (Recursive) করতে সক্ষম। এটি ডেটাবেসের মধ্যে এমন সমস্যা সমাধানে সহায়তা করে যেখানে একটি হায়ারার্কিকাল (অথবা পিরামিডাল) ডেটা কাঠামো বা সম্পর্কের প্রয়োজন হয়, যেমন অর্গানাইজেশনাল চার্ট, ফোল্ডার স্ট্রাকচার, পরিবারের গাছ, ইত্যাদি।

Recursive CTE এর গঠন

একটি Recursive CTE সাধারণত তিনটি অংশে বিভক্ত:

1. Anchor Member: এটি CTE এর প্রথম অংশ, যেখানে আপনি প্রথম মান বা ডেটা সেট নির্ধারণ করেন।
2. Recursive Member: এটি CTE এর পুনরাবৃত্তিমূলক অংশ, যা নিজেকে পুনরায় কল করে এবং আগের আউটপুটের উপর ভিত্তি করে নতুন মান বা ডেটা তৈরি করে।
3. Termination Condition: এটি CTE এর সীমাবদ্ধতা নির্ধারণ করে, যাতে এটি কখনও অগণিত রেকর্ড তৈরি না করে।

সাধারণ সিনট্যাক্স

WITH RecursiveCTE AS
(
    -- Anchor Member
    SELECT column1, column2
    FROM table_name
    WHERE condition1

    UNION ALL

    -- Recursive Member
    SELECT t.column1, t.column2
    FROM table_name t
    INNER JOIN RecursiveCTE r
    ON t.column1 = r.column1
    WHERE condition2
)
SELECT * FROM RecursiveCTE;

উদাহরণ ১: অর্গানাইজেশনাল চার্ট (Hierarchical Data)

ধরা যাক, আপনার একটি Employees টেবিল রয়েছে, যেখানে কর্মীদের হায়ারার্কি সংরক্ষিত রয়েছে। ManagerID কলামে একজন কর্মীর Manager's ID রাখা হয়েছে, এবং EmployeeID হল কর্মীর একক আইডি। আপনি এই তথ্য থেকে কর্মীদের হায়ারার্কি (অর্গানাইজেশনাল চার্ট) বের করতে চান।

Employees টেবিলের গঠন:

Recursive CTE ব্যবহার করে অর্গানাইজেশনাল চার্ট:

WITH RecursiveCTE AS
(
    -- Anchor Member: Manager ছাড়া প্রথম স্তরের কর্মী নির্বাচন করা
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive Member: ম্যানেজারের অধীনে কর্মী নির্বাচন করা
    SELECT e.EmployeeID, e.Name, e.ManagerID, r.Level + 1 AS Level
    FROM Employees e
    INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
-- ফলাফল নির্বাচন করা
SELECT EmployeeID, Name, ManagerID, Level
FROM RecursiveCTE
ORDER BY Level, ManagerID;

আউটপুট:

এখানে:

• Anchor Member প্রথমে ম্যানেজার ছাড়া কর্মীদের নির্বাচন করে।
• Recursive Member এরপর ম্যানেজারের অধীনে থাকা কর্মীদের নির্বাচন করে এবং Level বাড়িয়ে দেয়, এটি পুনরায় আগের রেকর্ডের সাথে যুক্ত হয়।

উদাহরণ ২: ফোল্ডার স্ট্রাকচার

ধরা যাক, আপনার একটি Folders টেবিল রয়েছে, যেখানে ফোল্ডার এবং সাবফোল্ডারের তথ্য রয়েছে। আপনি যদি ফোল্ডার স্ট্রাকচার (যেমন হায়ারার্কি) বের করতে চান, তাহলে Recursive CTE ব্যবহার করতে পারেন।

Folders টেবিলের গঠন:

Recursive CTE ব্যবহার করে ফোল্ডার স্ট্রাকচার বের করা:

WITH RecursiveCTE AS
(
    -- Anchor Member: মূল ফোল্ডার নির্বাচন করা (Root)
    SELECT FolderID, FolderName, ParentFolderID, 0 AS Level
    FROM Folders
    WHERE ParentFolderID IS NULL

    UNION ALL

    -- Recursive Member: সাবফোল্ডারগুলো নির্বাচন করা
    SELECT f.FolderID, f.FolderName, f.ParentFolderID, r.Level + 1 AS Level
    FROM Folders f
    INNER JOIN RecursiveCTE r ON f.ParentFolderID = r.FolderID
)
-- ফলাফল নির্বাচন করা
SELECT FolderID, FolderName, ParentFolderID, Level
FROM RecursiveCTE
ORDER BY Level, ParentFolderID;

আউটপুট:

এখানে:

• Anchor Member প্রথমে মূল ফোল্ডার (Root) নির্বাচন করে।
• Recursive Member এরপর সাবফোল্ডারগুলো নির্বাচন করে এবং Level বাড়িয়ে দেয়, যা সম্পূর্ণ ফোল্ডার স্ট্রাকচার তৈরি করতে সাহায্য করে।

সারাংশ

• Recursive CTE ডেটাবেসের মধ্যে হায়ারার্কিকাল ডেটা (যেমন: অর্গানাইজেশনাল চার্ট, ফোল্ডার স্ট্রাকচার) বের করার জন্য ব্যবহৃত হয়।
• এটি Anchor Member দিয়ে শুরু হয় এবং পরবর্তীতে Recursive Member দিয়ে নিজেকে পুনরায় কল করে।
• এটি অত্যন্ত কার্যকরী যখন আপনি ডেটাবেসের মধ্যে সম্পর্কিত তথ্যের মধ্যে হায়ারার্কি তৈরি করতে চান।

CTE (Common Table Expression) হল একটি SQL ফিচার যা একটি অস্থায়ী ফলাফল সেট তৈরি করে, যেটি মূল কুয়েরির মধ্যে পুনরায় ব্যবহৃত হয়। CTE সাধারণত জটিল কুয়েরি নির্মাণে সাহায্য করে এবং একটি পরিষ্কার, পাঠযোগ্য এবং পুনরায় ব্যবহারের উপযোগী SQL কোড তৈরি করতে সাহায্য করে। CTE সাধারণত বড় এবং জটিল কুয়েরি নির্মাণে ব্যবহৃত হয়, যেখানে সাবকুয়েরি বা যোগদান (JOIN) লজিক প্রয়োগ করা হয়।

CTE এর বৈশিষ্ট্য:

1. CTE মূল কুয়েরির আগে ঘোষণা করা হয়।
2. এটি একটি অস্থায়ী ফলাফল সেট তৈরি করে যা কুয়েরির অন্যান্য অংশে ব্যবহার করা যায়।
3. CTE পুনরায় ব্যবহারযোগ্য হয়, যা কোড রিডেবিলিটি উন্নত করে।
4. CTE এর মাধ্যমে সাবকুয়েরি বা JOIN এর মধ্যে লজিক তৈরি করা সহজ হয় এবং কোড পরিষ্কার থাকে।

CTE Syntax (স্ট্যান্ডার্ড সেগমেন্ট)

WITH CTE_name AS (
    -- CTE এর মধ্যে SELECT কুয়েরি
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM CTE_name
WHERE condition;

• WITH : CTE এর শুরু।
• CTE_name : CTE এর নাম।
• SELECT : CTE এর মধ্যে SELECT কুয়েরি যা মূল কুয়েরির জন্য অস্থায়ী ফলাফল তৈরি করবে।
• FROM CTE_name : মূল কুয়েরিতে CTE এর নাম ব্যবহার করা হয়।

CTE দিয়ে জটিল কুয়েরি হ্যান্ডলিং উদাহরণ:

ধরা যাক, আমাদের একটি Employees টেবিল রয়েছে, যেখানে কর্মচারীদের নাম, বয়স, বিভাগ এবং তাদের বেতন তথ্য রয়েছে। আমরা যদি DepartmentWise Salary Analysis করতে চাই, তবে CTE এর মাধ্যমে সহজেই এই কাজটি করা যাবে।

উদাহরণ ১: CTE ব্যবহার করে ডিপার্টমেন্ট অনুযায়ী গড় বেতন বের করা

WITH DepartmentSalary AS (
    SELECT Department, AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY Department
)
SELECT Department, AverageSalary
FROM DepartmentSalary
WHERE AverageSalary > 50000;

• প্রথমে, CTE (DepartmentSalary) তৈরি করা হয়েছে, যেখানে Employees টেবিল থেকে Department অনুযায়ী গড় বেতন বের করা হয়েছে।
• এরপর, মূল কুয়েরিতে CTE থেকে ডেটা ব্যবহার করা হয়েছে এবং AverageSalary > 50000 শর্তে ডিপার্টমেন্টগুলো নির্বাচন করা হয়েছে।

উদাহরণ ২: CTE ব্যবহার করে সাবকুয়েরি অপটিমাইজ করা

ধরা যাক, আমাদের Orders এবং Customers টেবিল রয়েছে এবং আমরা সেই সমস্ত গ্রাহকদের খুঁজে বের করতে চাই যারা ১০টি বা তার বেশি অর্ডার করেছেন।

WITH CustomerOrders AS (
    SELECT CustomerID, COUNT(OrderID) AS TotalOrders
    FROM Orders
    GROUP BY CustomerID
)
SELECT c.CustomerName, co.TotalOrders
FROM Customers c
JOIN CustomerOrders co ON c.CustomerID = co.CustomerID
WHERE co.TotalOrders >= 10;

• প্রথমে, CTE (CustomerOrders) তৈরি করা হয়েছে যেখানে Orders টেবিল থেকে CustomerID অনুযায়ী অর্ডারের সংখ্যা গোনা হয়েছে।
• এরপর, মূল কুয়েরিতে Customers টেবিলের সাথে CustomerOrders CTE যোগ করা হয়েছে এবং শর্ত দেয়া হয়েছে যে, TotalOrders >= 10 হওয়া উচিত।

উদাহরণ ৩: CTE দিয়ে হায়ারার্কিকাল ডেটা রিডিং

ধরা যাক, একটি Employees টেবিল রয়েছে, যেখানে ManagerID দিয়ে প্রতিটি কর্মচারীর মানেজারকে চিহ্নিত করা হয়েছে এবং আমরা চাই কর্মচারী ও তাদের মানেজারের সম্পর্ক বের করতে।

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, Name, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.Name, e.ManagerID
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

• প্রথমে, CTE (EmployeeHierarchy) তৈরি করা হয়েছে যেখানে Employees টেবিলের প্রথম স্তরের (Top-level) কর্মচারীদের (যাদের ManagerID NULL) নির্বাচন করা হয়েছে।
• এরপর, UNION ALL ব্যবহার করে কর্মচারীদের মানেজার এবং তাদের অধীনে থাকা কর্মচারীদের তথ্য একত্রিত করা হয়েছে।
• শেষ পর্যন্ত, EmployeeHierarchy CTE এর মাধ্যমে সম্পূর্ণ হায়ারার্কিকাল ডেটা নির্বাচন করা হয়েছে।

CTE এর সুবিধা:

1. পাঠযোগ্যতা উন্নত করা: CTE গুলি কোডকে আরও পরিষ্কার এবং সহজ পাঠযোগ্য করে তোলে। এটি আপনাকে জটিল কুয়েরি থেকে লজিক আলাদা করে সংগঠিত করতে সাহায্য করে।
2. পুনঃব্যবহারযোগ্য: একটি CTE তৈরি করার পর সেটি একাধিক জায়গায় ব্যবহার করা যায়, যার ফলে কোডের পুনরাবৃত্তি কমানো যায়।
3. প্যারালেল কুয়েরি: CTE এর মাধ্যমে কোডের প্রতিটি অংশ আলাদা এবং সংগঠিত থাকায়, SQL অপটিমাইজার এটি দ্রুত প্রক্রিয়া করতে পারে।
4. সাবকুয়েরি বা JOIN সহজ করা: CTE দিয়ে আপনি সাবকুয়েরি বা জটিল JOIN অপারেশনকে সহজভাবে লিখতে পারেন।

CTE এর সীমাবদ্ধতা:

1. পারফরম্যান্স: CTE দিয়ে জটিল কুয়েরি হ্যান্ডলিং করতে গিয়েও কখনও কখনও পারফরম্যান্স ইস্যু হতে পারে, বিশেষ করে যখন অনেক বড় ডেটাবেস এবং কমপ্লেক্স কুয়েরি চলে।
2. কিছু SQL ডাটাবেসে সীমাবদ্ধতা: সব ডাটাবেস সিস্টেমে CTE সমর্থিত না হতে পারে। উদাহরণস্বরূপ, কিছু পুরনো SQL ডাটাবেসে CTE কাজ নাও করতে পারে।
3. রেক্যুয়েস্ট সাইজ: CTE এর মাধ্যমে বড় সাইজের ডেটা প্রসেস করলে কিছু সীমাবদ্ধতা (যেমন মেমরি খরচ) দেখা দিতে পারে।

উপসংহার:

CTE (Common Table Expressions) জটিল কুয়েরি হ্যান্ডলিংয়ের জন্য একটি শক্তিশালী টুল যা আপনার SQL কোডের কার্যকারিতা এবং পাঠযোগ্যতা উন্নত করে। CTE এর মাধ্যমে আপনি সাবকুয়েরি, JOIN, এবং হায়ারার্কিকাল ডেটা অপারেশনগুলো সহজে পরিচালনা করতে পারবেন, এবং এতে কোডের পুনঃব্যবহারযোগ্যতা বাড়ে। তবে, এটি ব্যবহারের সময় পারফরম্যান্স বিষয়েও নজর রাখতে হবে, বিশেষ করে বড় ডেটাবেসের ক্ষেত্রে।

CTE (Common Table Expression) এবং Subquery উভয়ই SQL-এ এমন প্রযুক্তি, যা ডেটাবেসে জটিল প্রশ্ন (query) তৈরি করতে সাহায্য করে। তবে, তাদের ব্যবহার, কাঠামো এবং পারফরম্যান্সের ক্ষেত্রে কিছু পার্থক্য রয়েছে।

নিচে CTE এবং Subquery এর মধ্যে প্রধান পার্থক্যগুলি আলোচনা করা হলো:

১. সংজ্ঞা (Definition)

• CTE (Common Table Expression):
  CTE হল একটি অস্থায়ী ফলাফল সেট যা একটি SQL কুয়েরির মধ্যে ব্যবহার করা হয়। এটি WITH কিওয়ার্ড ব্যবহার করে তৈরি হয় এবং একটি প্রাথমিক কুয়েরি ব্লক হিসেবে কাজ করে। CTE সাধারণত আরও বড় বা জটিল কুয়েরি লেখার জন্য ব্যবহার করা হয়, যা অন্যান্য কুয়েরি বা অপারেশন দ্বারা পুনরায় ব্যবহারযোগ্য হতে পারে।

  উদাহরণ:

  WITH DepartmentCTE AS (
      SELECT Department, COUNT(*) AS EmployeeCount
      FROM Employees
      GROUP BY Department
  )
  SELECT * FROM DepartmentCTE;
  

• Subquery:
  Subquery হল একটি কুয়েরি যা অন্য একটি কুয়েরির মধ্যে অন্তর্ভুক্ত থাকে। এটি সাধারণত SELECT, INSERT, UPDATE, অথবা DELETE স্টেটমেন্টের মধ্যে অন্তর্ভুক্ত থাকে। Subquery তে আপনি একটি কুয়েরি চালাতে পারেন যেটি অন্য কুয়েরির ফলাফল হিসেবে ব্যবহৃত হয়।

  উদাহরণ:

  SELECT EmployeeName
  FROM Employees
  WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');
  

২. ব্যবহার (Usage)

• CTE:
  • CTE সাধারণত WITH কিওয়ার্ডের সাথে ব্যবহার করা হয় এবং একটি কুয়েরির প্রথমে স্থাপন করা হয়।
  • এটি একাধিকবার ব্যবহার করা যেতে পারে (যেমন, JOIN, UNION ইত্যাদিতে) একই কুয়েরির মধ্যে।
  • এটি কোডটিকে আরও পরিষ্কার এবং বুঝতে সহজ করে তোলে, বিশেষ করে যখন বড় বা জটিল কুয়েরি তৈরি করতে হয়।
  • CTE ব্যবহার করার সময় আপনি আগের কুয়েরির ফলাফল ব্যবহার করে আরও কুয়েরি তৈরি করতে পারেন।
• Subquery:
  • Subquery কুয়েরির ভিতরে এক বা একাধিক স্থানে ব্যবহার করা যেতে পারে (যেমন, SELECT, WHERE, HAVING, FROM ক্লজে)।
  • এটি কুয়েরির মধ্যে অন্তর্ভুক্ত থাকে এবং কোনও নির্দিষ্ট ফলাফল পেতে ব্যবহৃত হয়।
  • Subquery সাধারণত একটি একক ব্যবহারকারীর জন্য একক কুয়েরি হতে থাকে, যার জন্য কোডটি সাধারণত খুব পরিষ্কার থাকে না।

৩. পুনঃব্যবহারযোগ্যতা (Reusability)

• CTE:
  • CTE পুনরায় ব্যবহারযোগ্য, অর্থাৎ আপনি একবার CTE ডিফাইন করার পর, সেটি একাধিকবার SELECT, INSERT, UPDATE, বা DELETE স্টেটমেন্টে ব্যবহার করতে পারেন।
  • CTE যখন একাধিকবার ব্যবহৃত হয়, তখন এটি কোডটি পরিষ্কার এবং কমপ্যাক্ট রাখে।
• Subquery:
  • Subquery সাধারণত একবার ব্যবহার করা হয়। তবে, আপনি যদি একই Subquery বারবার ব্যবহার করতে চান, তাহলে আপনাকে এটি আবার লিখতে হবে।
  • Subquery প্রায়ই ইনলাইন কুয়েরি হিসাবে ব্যবহৃত হয়।

৪. পারফরম্যান্স (Performance)

• CTE:
  • CTE সাধারণত বড় এবং জটিল কুয়েরি তৈরি করার জন্য ভালো কারণ এটি কোডটিকে পুনরায় ব্যবহারযোগ্য করে তোলে। তবে, CTE এর পারফরম্যান্স Subquery এর তুলনায় কম হতে পারে, যদি না সেগুলি সঠিকভাবে অপটিমাইজ করা হয়।
  • কিছু ক্ষেত্রে, CTE-কে উপকারী হতে পারে কারণ এটি SQL Server-কে পুনরায় ব্যবহারের জন্য একটি একক কৌশল তৈরি করতে দেয়।
• Subquery:
  • Subquery কুয়েরি সময়ে সম্পাদন করা হয় এবং ফলাফল একবারের জন্য ব্যবহার করা হয়, যা পারফরম্যান্সে বেশি চাপ ফেলতে পারে। বড় ডেটাসেটের ক্ষেত্রে, সাবকুয়েরি কিছুটা ধীর হতে পারে।
  • তবে, Subquery সাধারণত কম জটিল এবং সহজ অপারেশনের জন্য দ্রুত হতে পারে।

৫. পাঠযোগ্যতা (Readability)

• CTE:
  • CTE কোডটিকে পরিষ্কার এবং সহজভাবে পড়া যায় কারণ এটি অস্থায়ী টেবিল হিসেবে কাজ করে এবং তারপরে CTE কে পুনরায় ব্যবহার করা যায়।
  • CTE দীর্ঘ এবং জটিল কুয়েরি সহজে ডিবাগ করার সুযোগ দেয়।
• Subquery:
  • Subquery কোডের মধ্যে অন্তর্ভুক্ত থাকে এবং বেশ কিছু ক্ষেত্রে কোডটি বিভ্রান্তিকর হতে পারে।
  • যখন Subquery খুব জটিল হয় এবং এটি অনেক বার ব্যবহৃত হয়, তখন কোড পড়তে সমস্যা হতে পারে।

৬. Multiple CTEs vs Subqueries

• Multiple CTEs:

  • আপনি একাধিক CTE ডিফাইন করতে পারেন একই কুয়েরিতে। এটি কোডের পাঠযোগ্যতা বাড়ায় এবং একাধিক স্টেপে ফলাফল প্রক্রিয়া করতে সহায়ক হয়।
  • উদাহরণ:

  WITH CTE1 AS (
      SELECT EmployeeID, Department FROM Employees
  ),
  CTE2 AS (
      SELECT Department, COUNT(*) AS TotalEmployees FROM CTE1 GROUP BY Department
  )
  SELECT * FROM CTE2;
  

• Subquery:
  • Subqueries সাধারণত একক কুয়েরিতে ব্যবহৃত হয় এবং একাধিক Subquery ব্যবহার করতে চাইলে, এটি কিছুটা জটিল এবং দুর্বল পড়তে পারে।

৭. রিস্কোপ (Scope)

• CTE:
  • CTE শুধুমাত্র ঐ কুয়েরির জন্য কার্যকর থাকে যেখানে এটি ডিফাইন করা হয়, অর্থাৎ CTE কেবল এক কুয়েরি রানে এবং পরে অদৃশ্য হয়ে যায়।
• Subquery:
  • Subquery সাধারণত নির্দিষ্ট ডেটা ফিল্টার করতে ব্যবহৃত হয় এবং সাধারণত নিজস্ব পরিসরে কাজ করে।

সারাংশ

CTE ডেটা গ্রুপিং এবং পুনরায় ব্যবহারযোগ্য কোডের জন্য একটি শক্তিশালী সরঞ্জাম, তবে Subquery সাধারণত সিম্পল ডেটা ফিল্টারিং বা এক্সপ্রেশন কাজের জন্য উপযুক্ত।