Różnica między CTE a SubQuery?

Główną zaletą Common Table Expression (gdy nie używa się go do rekurencyjnych zapytań ) jest enkapsulacja, zamiast deklarować podzapytanie w każdym miejscu, w którym chcesz go użyć, możesz zdefiniować je raz, ale mieć do niego wiele odniesień.

To jednak nie oznacza, że jest ona wykonywana tylko raz(zgodnie z poprzednimi iteracjami tej odpowiedzi , dziękuję wszystkim, którzy skomentowali). Zapytanie zdecydowanie ma możliwość wielokrotnego wykonania, jeśli odwołuje się wiele razy; optymalizator zapytań ostatecznie podejmuje decyzję co do jak CTE powinno być zinterpretowane.

CTE's są najbardziej przydatne dla rekurencji:

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

Zwróci @n wiersze (do 101). Przydatne do kalendarzy, atrapy wierszy itp.

Poza Tym, CTE i subqueries są identyczne.

Jedna różnica, która nie została wymieniona, to pojedynczy CTE może być odwołany w kilku częściach Unii

O ile czegoś nie przeoczyłem, równie łatwo można nazwać zapytania CTE i subqueries.

Myślę, że główną różnicą jest czytelność (uważam, że CTE jest bardziej czytelne, ponieważ definiuje subquery z przodu, a nie w środku).

A jeśli potrzebujesz zrobić coś z rekurencją, będziesz miał mały problem z zrobieniem tego z subquery ;)

Dodawanie do odpowiedzi innych, jeśli masz jedno i to samo zapytanie podrzędne używane kilka razy, możesz zastąpić wszystkie te zapytania jednym CTE. Pozwala to na lepsze wykorzystanie kodu.

Ważnym faktem, o którym nikt nie wspomniał, jest to, że (przynajmniej w postgres) CTE są płotami optymalizacyjnymi:

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

Oznacza to, że będą one traktowane jako własne zapytania atomowe, a nie złożone w cały plan zapytań. Brakuje mi Wiedzy, aby dać lepsze wyjaśnienie, ale powinieneś sprawdzić semantykę dla wersji sql używasz; dla zaawansowanych użytkowników, jest w stanie stworzyć ogrodzenie optymalizacyjne może pomóc w wydajności, jeśli jesteś ekspertem w kontrolowaniu planisty zapytań; w 99% przypadków należy jednak unikać prób mówienia planiście zapytań, co ma robić, ponieważ to, co myślisz, że będzie szybsze, jest prawdopodobnie gorsze niż to, co myśli, że będzie szybsze. :-)

Jedną z rzeczy, które musisz zrozumieć, jest to, że w starszych wersjach SQL Server (tak wiele osób nadal musi obsługiwać bazy danych SQL Server 2000), CTE nie są dozwolone, a następnie tabela pochodna jest najlepszym rozwiązaniem.

HINT: (MAXRECURSION n)

Można ograniczyć liczbę poziomów rekurencji dozwolonych dla określonego polecenie za pomocą podpowiedzi MAXRECURSION i wartości pomiędzy 0 oraz 32,767 w klauzuli OPTION

Na przykład, możesz spróbować:

OPTION 
      (MAXRECURSION 150)

GO