Разница между CTE и подзапросом?

эту статью как использовать ROW_NUMBER в следующей процедуре?

есть две версии ответов, где один использует SubQuery а другой использует CTE для решения той же проблемы.

Итак, в чем преимущество использования CTE (Common Table Expression) над sub-query(таким образом, более читабельный запрос-это на самом деле делает)

единственное преимущество использования CTE над sub select является то, что я могу на самом деле имя суб-запроса. Есть ли другие различия между этими двумя когда CTE используется как простой (нерекурсивный) CTE?

9 116
sql sql-server tsql subquery common-table-expression

9 ответов:

в подзапросе vs простой (нерекурсивные) версии CTE, они, вероятно, очень похожи. Вам нужно будет использовать профилировщик и фактический план выполнения, чтобы обнаружить какие-либо различия, и это будет зависеть от вашей настройки (поэтому мы не можем сказать вам ответ полностью).

на общие; CTE может использоваться рекурсивно; подзапрос не может. Это делает их особенно хорошо подходящими для древовидных структур.

82

главные преимущества Общее Табличное Выражение (если не использовать его для рекурсивные запросы) - это инкапсуляция, вместо того, чтобы объявлять подзапрос в каждом месте, где вы хотите его использовать, вы можете определить его один раз, но иметь несколько ссылок на него.

, Это значит не означает, что он выполняется только один раз (согласно предыдущие версии этого ответа, спасибо всем, кто прокомментировал). Этот запрос определенно может быть выполнен несколько раз, если на него ссылаются несколько раз; оптимизатор запросов в конечном итоге принимает решение о как CTE следует интерпретировать.
74

CTEнаиболее полезны для рекурсии:

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

вернутся @n строк (до 101). Полезно для календарей, фиктивных наборов строк и т. д.

они также более читабельны (на мой взгляд).

помимо этого, CTEи subqueries идентичны.

15

одно отличие, которое не было упомянуто, - это один CTE, на который можно ссылаться в нескольких частях Союза

9

Если я чего-то не хватает, вы можете назвать CTE и подзапросы так же легко.

Я думаю, что основное различие заключается в удобочитаемости (я нахожу CTE более читаемым, потому что он определяет ваш подзапрос спереди, а не в середине).

и если вам нужно сделать что-нибудь с рекурсией, вы будете иметь немного проблем, делая это с подзапросом ;)

8

добавление к ответам других, если у вас есть один и тот же подзапрос, используемый несколько раз, вы можете заменить все эти подзапросы одним CTE. Это позволяет лучше использовать ваш код.

5

один важный факт, который никто не упомянул, заключается в том, что (по крайней мере, в postgres) CTEs являются оптимизационными заборами:

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

то есть, они будут рассматриваться как их собственный атомарный запрос, а не свернуты во весь план запроса. Мне не хватает опыта, чтобы дать лучшее объяснение, но вы должны проверить семантику для версии sql, которую вы используете; для продвинутых пользователей, способных создание ограждения оптимизации может помочь производительности, если вы являетесь экспертом в управлении планировщиком запросов; в 99% случаев, однако, вы должны избегать попыток сказать планировщику запросов, что делать, потому что то, что вы думаете, будет быстрее, скорее всего, хуже, чем то, что он думает, будет быстрее. : -)

5

одна вещь, которую вам нужно понять, также заключается в том, что в более старых версиях SQL Server (да, многим людям все еще нужно поддерживать базы данных SQL Server 2000) CTE не разрешены, а затем производная таблица является вашим лучшим решением.

4

подсказка: (MAXRECURSION n)

вы можете ограничить количество уровней рекурсии, разрешенных для конкретного оператор с помощью MAXRECURSION подсказка и значение между 0 и 32,767 на OPTION п.

например, вы можете попробовать:

OPTION 
      (MAXRECURSION 150)

GO
2