SQL. Обобщенное табличное выражение и способы его использования
Обобщенное табличное выражение является общим инструментов для многих баз данных. Рассмотрим конкретнее, что же это такое и как с ним работать на примере средств MS SQL.
Common Table Expression (CTE) — результаты запроса, которые можно использовать множество раз в других запросах. То есть, запросом мы достаем данные, и они помещаются в пространство памяти, аналогично временному представлению, которое физически не сохраняется в виде объектов. Далее мы работаем с получившейся конструкцией как с таблицей, используя такие конструкции как select, update, insert и delete.
Выведем количество сотрудников, устроившихся на работу, в разбивке по годам:
Еще обобщенное табличное выражение можно составить из результатов нескольких запросов. Последний результирующий запрос обращается к данным нижнего CTE(TABLE_CTE2), но может и к любому из них:
Основные способы использования:
- для улучшения читаемости запроса в случае сложных запросов (разительно уменьшают размер кода);
- в случаях, когда нужно много раз обращаться к одним и тем же таблицам/выборкам из таблиц;
- для создания представлений (VIEW) в части select;
- для написания рекурсивных запросов.
Отличия от вложенного запроса:
- вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса.
Отличия от временной таблицы:
- заполнение временной таблицы при больших объемах создает нагрузку на диск;
- исполнение запросов с использованием временной таблицы увеличивает время их выполнения из-за места хранения данного типа таблиц (tempdb).
Мы познакомились с обобщенными табличными выражениями и убедились в том, что использование данного инструмента, совместно с остальными методами оптимизации запросов, помогает увеличить эффективность извлечения и обработки данных.
Отлично, спасибо.
А есть ещё рекурсивные CTE.
По хорошему вложенные запросы должны быть оптимизированы во что-то типа CTE, но как минимум SQLite так не умеет делать.
"Отличия от вложенного запроса:
вложенный запрос повторяется для каждой строки из нашей выборки, что повышает стоимость выполнения запроса."
Разве это верно для обычных подзапросов? Повторяться для каждой строки вложенный подзапрос будет только если он коррелирующий.