webentwicklung-frage-antwort-db.com.de

Unterschied zwischen CTE und SubQuery?

Aus diesem Beitrag Wie verwende ich ROW_NUMBER in der folgenden Prozedur?

Es gibt zwei Versionen von Antworten, bei denen eine ein SubQuery und die andere ein CTE verwendet, um dasselbe Problem zu lösen.

Was ist dann der Vorteil der Verwendung einer CTE (Common Table Expression) gegenüber einer sub-query (also mehr lesbar was die Abfrage eigentlich macht)

Der einzige Vorteil der Verwendung von CTE gegenüber sub select ist, dass ich die Unterabfrage tatsächlich name kann. Gibt es noch andere Unterschiede zwischen diesen beiden wenn ein CTE als einfacher (nicht rekursiver) CTE verwendet wird?

sqlsql-servertsqlsubquerycommon-table-expression
134
Sung M. Kim

In der Unterabfrage vs simple (non-recursive) CTE-Versionen sind sie sich wahrscheinlich sehr ähnlich. Sie müssten den Profiler und den tatsächlichen Ausführungsplan verwenden, um Unterschiede zu erkennen. Dies ist spezifisch für Ihr Setup (daher können wir Ihnen die Antwort nicht vollständig mitteilen).

In allgemein; Ein CTE kann rekursiv verwendet werden. Eine Unterabfrage kann nicht. Dadurch eignen sie sich besonders gut für Baumstrukturen.

90
Marc Gravell

Der Hauptvorteil von Common Table Expression (wenn es nicht für rekursive Abfragen verwendet wird) ist die Kapselung, anstatt die Unterabfrage an jeder Stelle deklarieren zu müssen, die Sie verwenden möchten Sie können es einmal definieren, haben aber mehrere Verweise darauf.

Dies bedeutet jedoch nicht, dass es nur einmal ausgeführt wird (gemäß vorherige Iterationen dieser Antwort , vielen Dank an alle, die kommentiert haben). Die Abfrage kann definitiv mehrmals ausgeführt werden, wenn auf sie mehrmals verwiesen wird. Das Abfrageoptimierungsprogramm trifft letztendlich die Entscheidung, ob wie der CTE interpretiert werden soll.

78
casperOne

CTE sind für die Rekursion am nützlichsten:

WITH hier(cnt) AS (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @n
        )
SELECT  cnt
FROM    hier

wird zurückkehren @n Zeilen (bis zu 101). Nützlich für Kalender, Dummy-Rowsets usw.

Sie sind auch besser lesbar (meiner Meinung nach).

Abgesehen davon sind CTE und subqueries identisch.

15
Quassnoi

Ein Unterschied, der nicht erwähnt wurde, ist, dass auf einen einzelnen CTE in den verschiedenen Teilen einer Union verwiesen werden kann

10
user340140

Sofern mir nichts fehlt, können Sie CTEs und Unterabfragen genauso einfach benennen.

Ich denke, der Hauptunterschied ist die Lesbarkeit (ich finde den CTE besser lesbar, weil er Ihre Unterabfrage eher im Voraus als in der Mitte definiert).

Und wenn Sie irgendetwas mit Rekursion tun müssen, werden Sie ein bisschen Probleme damit haben, das mit einer Unterabfrage zu tun;)

8
AlexCuse

Eine wichtige Tatsache, die niemand erwähnt hat, ist, dass CTEs (zumindest in postgres) Optimierungszäune sind:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

Das heißt, sie werden als eigene atomare Abfrage behandelt und nicht in den gesamten Abfrageplan integriert. Mir fehlt das Fachwissen, um eine bessere Erklärung zu geben, aber Sie sollten die Semantik für die Version von SQL überprüfen, die Sie verwenden. Für erfahrene Benutzer kann die Erstellung eines Optimierungsfensters die Leistung verbessern, wenn Sie Experten in der Steuerung des Abfrageplaners sind. In 99% der Fälle sollten Sie jedoch vermeiden, dem Abfrageplaner zu sagen, was zu tun ist, da das, was Ihrer Meinung nach schneller ist, wahrscheinlich schlechter ist als das, was er für schneller hält. :-)

7
Ajax

Wenn Sie zu den Antworten anderer hinzufügen und dieselbe Unterabfrage mehrmals verwenden, können Sie alle diese Unterabfragen durch einen CTE ersetzen. Auf diese Weise können Sie Ihren Code besser wiederverwenden.

6
A-K

Eine Sache, die Sie auch verstehen müssen, ist, dass in älteren Versionen von SQL Server (ja, viele Leute müssen noch SQL Server 2000-Datenbanken unterstützen) CTEs nicht zulässig sind und dann die abgeleitete Tabelle Ihre beste Lösung ist.

4
HLGEM

TIPP: (MAXRECURSION n)

sie können die Anzahl der Rekursionsstufen begrenzen, die für eine bestimmte Anweisung zulässig sind, indem Sie den MAXRECURSION -Hinweis und einen Wert zwischen und 2.767 in OPTION Klausel

Zum Beispiel könnten Sie versuchen:

OPTION 
      (MAXRECURSION 150)

GO
2
Basic_