MySQL में CTE और WITH Clause

Common Table Expression क्या है?

CTE one statement की duration के लिए query result को name देती है। Permanent database object बनाए बिना complex SQL को readable stages में बाँटा जा सकता है।

WITH cte_name AS (
  SELECT ...
)
SELECT ...
FROM cte_name;

Verified Advanced Score Lab

CREATE TABLE advanced_scores (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(60) NOT NULL,
  class_name VARCHAR(20) NOT NULL,
  marks DECIMAL(5,2) NOT NULL
);

INSERT INTO advanced_scores VALUES
(1, 'Aarav',  'X-A', 86),
(2, 'Meera',  'X-A', 92),
(3, 'Sana',   'X-A', 92),
(4, 'Kabir',  'X-B', 74),
(5, 'Vihaan', 'X-B', 88),
(6, 'Riya',   'X-B', 81);

X-A में three students, total 270, average 90। X-B में three students, total 243, average 81। यही checkpoints चारों Day 8 lessons verify करेंगे।

पहली CTE: Class Summary

WITH class_summary AS (
  SELECT class_name,
         COUNT(*) AS student_count,
         SUM(marks) AS total_marks,
         ROUND(AVG(marks), 2) AS average_marks
  FROM advanced_scores
  GROUP BY class_name
)
SELECT class_name, student_count,
       total_marks, average_marks
FROM class_summary
WHERE average_marks >= 85
ORDER BY class_name;

CTE two summary rows बनाती है। Outer query X-A रखती है क्योंकि average 85 meet करती है; X-B at 81 नहीं। CTE display order guarantee नहीं करती, इसलिए presentation order के लिए final result में ORDER BY दें।

Multiple CTEs की Query Pipeline

WITH
class_summary AS (
  SELECT class_name, AVG(marks) AS class_average
  FROM advanced_scores
  GROUP BY class_name
),
above_class_average AS (
  SELECT s.student_id, s.student_name,
         s.class_name, s.marks,
         cs.class_average
  FROM advanced_scores AS s
  JOIN class_summary AS cs
    ON cs.class_name = s.class_name
  WHERE s.marks > cs.class_average
)
SELECT student_name, class_name, marks,
       ROUND(class_average, 2) AS class_average
FROM above_class_average
ORDER BY class_name, student_id;

Second CTE first को refer कर सकती है क्योंकि class_summary earlier है। One WITH clause लें और definitions commas से separate करें।

CTE Reuse और WITH के साथ DML

Containing statement में CTE several times reference हो सकती है। यह aggregation repeat किए बिना strongest और weakest class average compare करती है:

WITH class_summary AS (
  SELECT class_name, AVG(marks) AS average_marks
  FROM advanced_scores
  GROUP BY class_name
)
SELECT hi.class_name AS highest_class,
       lo.class_name AS lowest_class
FROM class_summary AS hi
CROSS JOIN class_summary AS lo
WHERE hi.average_marks = (
        SELECT MAX(average_marks) FROM class_summary
      )
  AND lo.average_marks = (
        SELECT MIN(average_marks) FROM class_summary
      );

MySQL supported SELECT, UPDATE और DELETE positions पर WITH allow करता है। Data-changing DML से पहले CTE-driven row set को SELECT से preview करें और appropriate transaction लें।

CTE, Derived Table, View या Temporary Table?

Scope, reuse, permissions और performance से चुनें—किसी one construct को universally superior न मानें।

Scope, Column Names और Dependencies

• One WITH clause में CTE names unique हों।
• CTE earlier CTEs को refer कर सकती है, same level पर later ones को नहीं।
• Explicit column-name list result column count से match करे।
• Explicit list न हो तो names CTE select list से आते हैं।
• Base-table name को CTE name की तरह तभी reuse करें जब deliberate shadowing unmistakable हो।

WITH class_summary
     (class_name, student_count, average_marks) AS (
  SELECT class_name, COUNT(*), AVG(marks)
  FROM advanced_scores
  GROUP BY class_name
)
SELECT * FROM class_summary;

Optimizer Behavior और अभ्यास

Nonrecursive CTE outer query में merge या materialize हो सकती है। Materialized और multiple references होने पर MySQL query के लिए once materialize करके useful internal indexes add कर सकता है। Recursive CTE materialized होती है।

1. हर CTE body independently validate करें।
2. हर stage पर row grain और counts जाँचें।
3. Semantics same रहें तभी early filter करें।
4. EXPLAIN लें; CTE syntax speed promise नहीं करती।
5. Representative data measure और wide intermediate results monitor करें।

अभ्यास: class maximum CTE; उससे top scorers; one summary CTE twice reference; basic example को derived table में rewrite करके plans compare करें।

Official संदर्भ

• MySQL 8.4: WITH and Common Table Expressions
• MySQL 8.4: CTE Merge and Materialization
• MySQL 8.4: EXPLAIN Statement

Syntax और behavior official MySQL 8.4 manual से जाँचे गए हैं। Production से पहले अपने server पर plans और limits verify करें।