Verwirrung Gebrauch von cte, innerer Verband und Vereinigung alle

Ich bin verwirrt über die Verwendung von innerem Beitritt auf dem CTE hier. Was ist in einem, wie es in der inneren Verknüpfung am Ende erscheint und was ist in cte1 c?

WITH cte1 AS (SELECT id,geographyname, OriginalGoals, ParentGeographyname, 0 AS HierarchyLevel, paradigm FROM businessobject_RefinementMaster WHERE Id = @Geo UNION ALL SELECT a.id, a.geographyname, a.OriginalGoals, a.ParentGeographyName, HierarchyLevel-1 AS HierarchyLevel, a.paradigm FROM businessobject_RefinementMaster a INNER JOIN cte1 c ON c.ParentGeographyname = a.geographyname AND c.paradigm=a.paradigm ) 

was wird das Ergebnis dieser Abfrage sein?

Dies ist ein rekursiver CTE ( hidden-RBAR ). Ich werde versuchen, es in einer Weise zu kommentieren, dass du verstehen kannst, was los ist:

 WITH cte1 AS ( /* This is called "anchor" and reads the "head" lines of a hierarchy */ SELECT id, geographyname, OriginalGoals, ParentGeographyname, 0 AS HierarchyLevel, --obviously this starts with a "0" paradigm FROM businessobject_RefinementMaster --The source-table WHERE Id = @Geo --You read elements with Id=@Geo. This is - probably - one single element 

–Das nächste SELECT wird dem Ergebnis-Set "hinzugefügt"

  UNION ALL /* The column-list must be absolutely the same (count and type) of the anchor */ SELECT a.id, a.geographyname, a.OriginalGoals, a.ParentGeographyName, HierarchyLevel-1 AS HierarchyLevel, --this is simple counting. Started with 0 this will lead to -1, -2, -3... a.paradigm FROM businessobject_RefinementMaster a --same source-table as above INNER JOIN cte1 c ON c.ParentGeographyname = a.geographyname --Find rows where the name of the element is the parent-name of the former element AND c.paradigm=a.paradigm ) /* Return the result-set */ SELECT * FROM cte1 

Das Ergebnis sollte eine vollständige rekursive list der Eltern zu einem gegebenen Element sein.