SQL में CTE बनाम Subquery: पढ़ने में आसानी, उपयोग और प्रदर्शन

CTE, WITH से क्वेरी के नतीजे को नाम देती है; subquery किसी दूसरे statement के अंदर लिखी क्वेरी होती है। वह रूप चुनें जिसमें डेटा के बदलाव साफ समझ आएँ। CTE अपने आप तेज़ नहीं होती और स्थायी टेबल भी नहीं है।

यह गाइड साधारण, nonrecursive CTE की तुलना FROM में लिखी derived-table subquery से करती है। Scalar subquery, correlated subquery और recursive CTE अलग समस्याएँ हल करती हैं; हर उदाहरण में उन्हें एक-दूसरे की जगह नहीं रख सकते।

स्थिति	उपयोगी शुरुआत
बीच की एक छोटी गणना	Subquery
नाम वाले कई बदलाव के चरण	CTE
अलग statements में नतीजे का उपयोग	View या temporary table पर विचार करें
प्रदर्शन का निर्णय लेना है	वास्तविक डेटाबेस पर मापें

सवाल है: किन ग्राहकों के paid भुगतान का कुल कम से कम 150 है? Refunded भुगतान कुल में शामिल नहीं होने चाहिए।

SQLite में खुद चलाकर देखें

SQL Lab खोलें, यह सेटअप पेस्ट करें और एक बार चलाएँ। फिर हर क्वेरी अलग चलाएँ। टेबल और कॉलम के नाम अंग्रेज़ी में रखे गए हैं ताकि हर भाषा में वही SQL चले। लैब और लिंक किए गए पाठ के निर्देश अभी अंग्रेज़ी में हैं।

CREATE TABLE blog_payments (
  id INTEGER PRIMARY KEY, customer_id INTEGER, status TEXT, amount INTEGER
);
INSERT INTO blog_payments VALUES
  (1, 1, 'paid', 120), (2, 1, 'paid', 80), (3, 1, 'refunded', 90),
  (4, 2, 'paid', 60), (5, 3, 'paid', 200);

1. FROM में subquery इस्तेमाल करें

अंदर की क्वेरी paid भुगतान चुनकर प्रति ग्राहक उनका कुल निकालती है। बाहर की क्वेरी उस कुल को फ़िल्टर करती है। totals alias, derived table का नाम है। ग्राहक 1 और 3 के 200-200 हैं, इसलिए वे योग्य हैं; ग्राहक 2 के केवल 60 हैं।

SELECT customer_id, paid_total
FROM (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
) AS totals
WHERE paid_total >= 150
ORDER BY customer_id;

अपेक्षित नतीजा

customer_id	paid_total
1	200
3	200

2. उसी बीच के नतीजे को CTE का नाम दें

नतीजा बिल्कुल समान है। WITH बीच की गणना को मुख्य SELECT से ऊपर रखता है और paid_totals नाम देता है। यह नाम केवल इसी statement में मौजूद है। बाद में अलग SELECT चलाने पर paid_totals कोई संग्रहित टेबल नहीं होगी जिसे पढ़ सकें।

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT customer_id, paid_total
FROM paid_totals
WHERE paid_total >= 150
ORDER BY customer_id;

अपेक्षित नतीजा

customer_id	paid_total
1	200
3	200

3. गहरी nesting के बिना कई चरण जोड़ें

पहली CTE paid कुल निकालती है। दूसरी ग्राहक का segment तय करती है; मुख्य क्वेरी segments का सारांश बनाती है। पंक्ति फ़िल्टर, ग्राहक का कुल, वर्गीकरण और अंतिम रिपोर्ट अलग चरण बन जाते हैं। high में दो ग्राहक और 400 revenue हैं; standard में एक ग्राहक और 60 revenue हैं।

WITH paid_totals AS (
  SELECT customer_id, SUM(amount) AS paid_total
  FROM blog_payments
  WHERE status = 'paid'
  GROUP BY customer_id
), customer_segments AS (
  SELECT customer_id, paid_total,
    CASE WHEN paid_total >= 150 THEN 'high' ELSE 'standard' END AS segment
  FROM paid_totals
)
SELECT segment, COUNT(*) AS customers, SUM(paid_total) AS revenue
FROM customer_segments
GROUP BY segment
ORDER BY segment;

अपेक्षित नतीजा

segment	customers	revenue
high	2	400
standard	1	60

अभ्यास और उत्तर की जाँच

पहली दोनों क्वेरी में सीमा 150 से 210 कर दें। दोनों में कोई पंक्ति नहीं आनी चाहिए। फिर सभी payment statuses शामिल करें और सीमा वापस 150 करें: ग्राहक 1 का कुल 290 और ग्राहक 3 का 200 होगा। इससे जाँच होती है कि व्यावसायिक नतीजा पंक्ति के फ़िल्टर से तय होता है, CTE या subquery चुनने से नहीं।

प्रदर्शन के लिए वास्तविक इंजन और डेटा पर execution plan तथा समय की तुलना करें। PostgreSQL कुछ CTE को मुख्य क्वेरी में मिला सकता है या query के अनुसार materialize कर सकता है; उसके दस्तावेज़ शर्तें और hints बताते हैं। SQLite का अपना planning व्यवहार है। नतीजे को नाम देना एक बार physical execution या caching की गारंटी नहीं है।

हर क्वेरी में tuning के लिए materialization hint न जोड़ें। सही जाँच में डेटाबेस version, indexes, पंक्तियों की संख्या, query plan और कई बार चलाने का समय दर्ज करें। छोटा उदाहरण शुद्धता और संरचना समझाता है; इससे production में विजेता तय नहीं हो सकता।

जब सार्थक नाम अस्पष्टता हटाए तो CTE चुनें। संबंधित छोटा तर्क एक साथ रखना हो तो subquery चुनें। दोनों में SELECT * की जगह ज़रूरी कॉलम लिखें और JOIN से पहले तथा बाद में पंक्तियाँ गिनें, ताकि गलती से डेटा दोहराए नहीं।

दस्तावेज़ और आगे की पढ़ाई

CTE का पाठ और FROM में subquery का पाठ आज़माएँ। उदाहरण SQLite पर चलते हैं और सामान्य WITH syntax PostgreSQL तथा MySQL 8 में भी है। चलाने योग्य उदाहरण में किसी इंजन का खास materialization syntax नहीं है।

• SQLite के आधिकारिक दस्तावेज़
• डेटाबेस के आधिकारिक दस्तावेज़