एसक्यूएल में पदानुक्रमित और पुनरावर्ती क्वेरी

एक पदानुक्रमित क्वेरी एक प्रकार का चयन (एसक्यूएल) है जो पदानुक्रमित मॉडल डेटा को संभालती है। वे अधिक सामान्य रिकर्सिव fixpoint  प्रश्नों के विशेष मामले हैं, जो सकर्मक क्लोजर की गणना करते हैं।

मानक एसक्यूएल में: 1999 पदानुक्रमित प्रश्नों को पुनरावर्ती #कॉमन टेबल एक्सप्रेशन (सीटीई) के माध्यम से कार्यान्वित किया जाता है। ओरेकल के पहले #कनेक्ट बाय|कनेक्ट-बाय क्लॉज के विपरीत, रिकर्सिव सीटीई को शुरुआत से फिक्सपॉइंट सेमेन्टिक्स के साथ डिजाइन किया गया था। मानक से पुनरावर्ती CTE IBM DB2 संस्करण 2 में मौजूदा कार्यान्वयन के अपेक्षाकृत करीब थे। रिकर्सिव सीटीई भी माइक्रोसॉफ्ट एसक्यूएल सर्वर द्वारा समर्थित हैं (एसक्यूएल सर्वर 2008 आर 2 के बाद से), फायरबर्ड (डेटाबेस सर्वर) | फायरबर्ड 2.1, पोस्टग्रेएसक्यूएल | पोस्टग्रेएसक्यूएल 8.4+, SQLite|SQLite 3.8.3+, IBM Informix संस्करण 11.50+, CUBRID, MariaDB|MariaDB 10.2+ और MySQL|MySQL 8.0.1+। झांकी में प्रलेखन है यह वर्णन करते हुए कि सीटीई का उपयोग कैसे किया जा सकता है। TIBCO Spotfire CTE का समर्थन नहीं करता है, जबकि Oracle 11g रिलीज़ 2 के कार्यान्वयन में फ़िक्सपॉइंट सिमेंटिक्स का अभाव है।

सामान्य टेबल एक्सप्रेशंस या कनेक्टेड-बाय क्लॉज के बिना उपयोगकर्ता-परिभाषित पुनरावर्ती कार्यों के साथ पदानुक्रमित प्रश्नों को प्राप्त करना संभव है।

सामान्य तालिका अभिव्यक्ति
एक सामान्य तालिका अभिव्यक्ति, या सीटीई, (एसक्यूएल में) एक अस्थायी नामित परिणाम सेट है, जो एक साधारण क्वेरी से प्राप्त होता है और निष्पादन के दायरे में परिभाषित होता है।,  ,  , या   कथन।

सीटीई को व्युत्पन्न तालिकाओं (सबक्वेरी), देखें (डेटाबेस)  और इनलाइन यूजर-डिफ़ाइंड फ़ंक्शंस के विकल्प के रूप में माना जा सकता है।

टेराडाटा (संस्करण 14 से शुरू), IBM Db2, Informix (संस्करण 14.1 से शुरू), फायरबर्ड (डेटाबेस सर्वर) (संस्करण 2.1 से शुरू) द्वारा सामान्य तालिका अभिव्यक्तियों का समर्थन किया जाता है। Microsoft SQL सर्वर (संस्करण 2005 से शुरू), Oracle डेटाबेस (11g रिलीज़ 2 से पुनरावर्तन के साथ), PostgreSQL (8.4 से), MariaDB (10.2 से), MySQL (8.0 से), SQLite (3.8.3 से), HSQLDB, IBM सूचना (14.10 से), Google BigQuery, Sybase (डेटाबेस) (संस्करण 9 से शुरू), Vertica, H2 (DBMS) (प्रायोगिक), और संबंधपरक डेटाबेस प्रबंधन प्रणालियों की तुलना। Oracle CTEs सबक्वेरी फैक्टरिंग को कॉल करता है। CTE (जो रिकर्सिव हो भी सकता है और नहीं भी) के लिए सिंटैक्स इस प्रकार है:

कहाँ का सिंटैक्स है:

पुनरावर्ती CTE का उपयोग संबंधों (ग्राफ़ या पेड़ों के रूप में) को पार करने के लिए किया जा सकता है, हालांकि सिंटैक्स बहुत अधिक शामिल है क्योंकि कोई स्वचालित छद्म-स्तंभ नहीं बनाया गया है (जैसे  #छद्म-स्तंभ); यदि ये वांछित हैं, तो उन्हें कोड में बनाना होगा। एमएसडीएन दस्तावेज देखें या आईबीएम प्रलेखन  ट्यूटोरियल उदाहरण के लिए।   पोस्टग्रेएसक्यूएल के अलावा अन्य सिस्टम में आमतौर पर ई> कीवर्ड की आवश्यकता नहीं होती है। SQL:1999 में एक पुनरावर्ती (CTE) क्वेरी कहीं भी प्रकट हो सकती है जहाँ किसी क्वेरी की अनुमति है। उदाहरण के लिए, परिणाम का नाम देना संभव है  [ ]. एक के अंदर CTE का उपयोग करना, एक पुनरावर्ती क्वेरी से उत्पन्न डेटा के साथ एक तालिका को पॉप्युलेट कर सकता है; बिना किसी प्रक्रियात्मक कथन का उपयोग किए इस तकनीक का उपयोग करके यादृच्छिक डेटा पीढ़ी संभव है। कुछ डेटाबेस, जैसे कि PostgreSQL, छोटे क्रिएट रिकर्सिव व्यू फॉर्मेट का समर्थन करते हैं, जिसे आंतरिक रूप से विथ रिकर्सिव कोडिंग में अनुवादित किया जाता है। 0 से 9 तक संख्याओं के भाज्य की गणना करने वाली पुनरावर्ती क्वेरी का एक उदाहरण निम्नलिखित है:

द्वारा कनेक्ट करें
एक वैकल्पिक सिंटैक्स गैर-मानक है  निर्माण; इसे 1980 के दशक में Oracle द्वारा पेश किया गया था। Oracle 10g से पहले, निर्माण केवल विश्वकोश रेखांकन के लिए उपयोगी था क्योंकि यह किसी भी चक्र का पता लगाने में त्रुटि देता था; 10g संस्करण में Oracle ने NOCYCLE फीचर (और कीवर्ड) पेश किया, जिससे चक्रों की उपस्थिति में ट्रैवर्सल कार्य भी हो गया।

स्नोफ्लेक इंक, एंटरप्राइजडीबी द्वारा समर्थित है, ओरेकल डेटाबेस, घनाकार, आईबीएम इनफॉर्मिक्स और IBM Db2 हालांकि केवल अगर यह एक संगतता मोड के रूप में सक्षम है। वाक्य रचना इस प्रकार है:


 * उदाहरण के लिए,

उपरोक्त क्वेरी से आउटपुट ऐसा दिखाई देगा:

स्तर | कर्मचारी | एम्प्नो | प्रबंधक ---+-+---+-     1 | राजा | 7839 | 2 | जोन्स | 7566 | 7839 3 | स्कॉट | 7788 | 7566 4 | एडम्स | 7876 | 7788 3 | फोर्ड | 7902 | 7566 4 | स्मिथ | 7369 | 7902 2 | ब्लेक | 7698 | 7839 3 | एलन | 7499 | 7698 3 | वार्ड | 7521 | 7698 3 | मार्टिन | 7654 | 7698 3 | टर्नर | 7844 | 7698 3 | जेम्स | 7900 | 7698 2 | क्लार्क | 7782 | 7839 3 | मिलर | 7934 | 7782 (14 पंक्तियाँ)

छद्म-स्तंभ

 * LEVEL
 * CONNECT_BY_ISLEAF
 * CONNECT_BY_ISCYCLE
 * CONNECT_BY_ROOT

यूनरी ऑपरेटर
निम्नलिखित उदाहरण विभाग 10 में प्रत्येक कर्मचारी का अंतिम नाम देता है, पदानुक्रम में उस कर्मचारी के ऊपर प्रत्येक प्रबंधक, प्रबंधक और कर्मचारी के बीच स्तरों की संख्या, और दोनों के बीच का रास्ता:

यह भी देखें

 * संगणक वैज्ञानिक फिक्सपॉइंट प्रश्नों को भी लागू करता है
 * डिडक्टिव डेटाबेस
 * पदानुक्रमित मॉडल
 * पहुंच योग्यता
 * सकर्मक बंद
 * वृक्ष संरचना

अग्रिम पठन
Academic textbooks. Note that these cover only the SQL:1999 standard (and Datalog), but not the Oracle extension.
 * Chapter 24.
 * Chapter 24.

बाहरी संबंध

 * https://stackoverflow.com/questions/1731889/cycle-detection-with-recursive-subquery-factoring
 * http://explainextended.com/2009/11/18/sql-server-are-the-recursive-ctes-really-set-based/
 * https://web.archive.org/web/20131114094211/http://gennick.com/with.html
 * http://www.cs.duke.edu/courses/fall04/cps116/lectures/11-recursion.pdf
 * http://www.blacktdn.com.br/2015/06/blacktdn-mssql-usando-consulta-cte.html