שאילתות היררכיות ורקורסיביות ב-SQL

שאילתה היררכית היא שאילתת SQL שמטפלת בנתוני מודל היררכי.
על פי תקן SQL:1999 שאילתות היררכיות מיושמות באמצעות CTE רקורסיבי (Common Table Expression, ביטוי טבלה משותף), בניגוד להרחבה של אורקל שתתואר בהמשך, בדומה לאופן שיושם לראשונה ב-DB2 גרסה 2, ובהמשך ב-SQL Server,‏ Oracle 11g Release 2,‏ Firebird,‏ PostgreSQL ו-Cubrid.
סינטקס חלופי הוא Connect By הלא סטנדרטי שהוצג על ידי אוראקל ב-1980. לפני גרסה 10g הוא היה שימושי רק לגרפים ללא לולאות (עצים למשל), ומגרסה זו התווסף השימוש באופרטור NoCycle הפותר את הבעיה.

Connect By

Connect By נתמך על ידי אוראקל, EnterpriseDB, Cubrid, DB2. הסינטקס:
SELECT select_listFROM table_expression[ WHERE ... ][ START WITH start_expression ]CONNECT BY [NOCYCLE] { PRIOR parent_expr = child_expr | child_expr = PRIOR parent_expr }[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ...[ GROUP BY ... ][ HAVING ... ]...
למשל:
SELECT LEVEL, 
 LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", 
 empno, 
 mgr "manager"FROM emp START WITH mgr IS NULLCONNECT BY PRIOR empno = mgr;
הפלט של השאילתה הנ"ל יראה כך:
 level | employee | empno | manager-------+-------------+-------+---------
 1 | KING | 7839 |
 2 | JONES | 7566 | 7839
 3 | SCOTT | 7788 | 7566
 4 | ADAMS | 7876 | 7788
 3 | FORD | 7902 | 7566
 4 | SMITH | 7369 | 7902
 2 | BLAKE | 7698 | 7839
 3 | ALLEN | 7499 | 7698
 3 | WARD | 7521 | 7698
 3 | MARTIN  | 7654 | 7698
 3 | TURNER  | 7844 | 7698
 3 | JAMES | 7900 | 7698
 2 | CLARK | 7782 | 7839
 3 | MILLER  | 7934 | 7782(14 rows)
דוגמה נוספת - הדוגמה שלהלן תחזיר את שם המשפחה של כל עובד במחלקה 10, שרשרת הניהול מעל העובד, מספר דרגות הניהול בין המנהל לעובד, והנתיב בעץ ביניהם:
SELECT ename "Employee", 
 CONNECT_BY_ROOT ename "Manager",
 LEVEL-1 "Pathlen", 
 SYS_CONNECT_BY_PATH(ename, '/') "Path"FROM empWHERE LEVEL > 1 
 and deptno = 10CONNECT BY PRIOR empno = mgrORDER BY "Employee", "Manager", "Pathlen", "Path";
בנוסף- ניתן לחולל סט בעזרת Connect By, למשל המספרים מ-1 עד 9
:
Select Level As NFrom DualConnect By Level<=9;

Common Table Expression (ביטוי טבלה משותף)

CTE הוא שם זמני לסט תוצאות של שאילתה שמתקיים במסגרת פקודת DML בודדת.
ניתן להתייחס אליו כאל שאילתת משנה או View זמני.
הם נתמכים על מספר רב של בסיסי נתונים, וביניהם DB2, SQL Server ואוראקל.
סינטקס:
With query_name As(Select...…)Select …From query_name…;
CTE רקורסיבי (או Recursive Subquery Factoring באוראקל) מטפל בקשרים כמו בגרפים ובעיקר עצים, וחייב שימוש רב יותר קוד מאשר אופציית ה-Connect By הנ"ל שיש בה למשל חישוב אוטומטי של רמת ההיררכיה Level וכו', אם כי מנגד יכולותיה חזקות יותר.
דוגמה לקוד ב-Transact-SQL ל"פיצוץ" עץ מוצר תוך מניעת לולאה אינסופית:
With T As(Select BillOfMaterialsID OriginalBillOfMaterialsID,
 BillOfMaterialsID,
 ComponentID,
 1 Level,
 Cast(BillOfMaterialsID As Varchar(Max))+'/'+Cast(ComponentID As Varchar(Max)) PathFrom Production.BillOfMaterialsUnion AllSelect T.OriginalBillOfMaterialsID,
 BOM.BillOfMaterialsID,
 BOM.ComponentID,
 Level+1 Level,
 T.Path+'/'+Cast(BOM.ComponentID As Varchar(Max)) PathFrom Production.BillOfMaterials BOMInner Join T
 On T.ComponentID=BOM.BillOfMaterialsIDWhere '/'+T.Path+'/' Not Like '%/'+Cast(BOM.ComponentID As Varchar(Max))+'/%')Select *From TOption (MaxRecursion 0);
דוגמה לקוד המחולל רשימת מספרים מ-1 ועד 9 ומחשב לכל אחד מהם את העצרת:
With Temp As(Select 0 N, 
 1 Fct -- Initial SubqueryUnion AllSelect N+1, 
 (N+1)*Fct 
From Temp -- Recursive Subquery Where N<9)Select * 
From Temp;
כפי שניתן לראות ה-CTE מורכב משתי שאילתות המחוברות אנכית בעזרת האופרטור Union All: למעלה העוגן (anchor) ולמטה השאילתה הרקורסיבית שפונה ל-CTE (כלומר - מתוך ה-CTE ששמו Temp נעשית פניה ל-Temp עצמו).
במילים אחרות, בניגוד לרקורסיה בשפות פרוצדורליות, אין כאן רקורסיה עם תנאי עצירה, אלא עוגן ורקורסיה.
תנאי העצירה בדוגמה האחרונה הוא פסוקית התנאי Where N<9 שמונעת לולאה אינסופית.
במקרה של שליפה מטבלה היררכית אין בדרך כלל צורך בתנאי עצירה מכיוון שמעשית מדובר בהיררכיה סופית המיוצגת על ידי עץ, אם כי עדיין אין מניעה טכנית ליצור לולאה אינסופית.
השימוש ב-Connect By התווסף לאוראקל בשנת 1979, וב-CTE רקורסיבי ב-2009.
SQL Server תומך ב-CTE רקורסיבי מ-2005.