Запросы WITH - Общие табличные выражения (CTE) кратко

Привет, Вы узнаете о том , что такое запросы with, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое запросы with, общие табличные выражения, cte , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

WITH предоставляет способ записывать дополнительные операторы для применения в больших запросах. Эти операторы, которые также называют общими табличными выражениями (Common Table Expressions, CTE), можно представить как определения временных таблиц, существующих только для одного запроса. Дополнительным оператором в предложении WITH может быть SELECT, INSERT, UPDATE или DELETE, а само предложение WITH присоединяется к основному оператору, которым также может быть SELECT, INSERT, UPDATE или DELETE.

CTE играет роль SQL представления, которое создается в рамках одного запроса и, не сохраняется как объект схемы.

Распространенное табличное выражение (CTE) можно рассматривать как временный набор результатов, определенный в области выполнения одного оператора SELECT, INSERT, UPDATE, DELETE или CREATE VIEW. CTE похож на производную таблицу в том смысле, что он не сохраняется как объект и действует только в течение запроса. Однако, в отличие от производной таблицы, CTE может иметь самоссылку и ссылаться на нее несколько раз в одном запросе.

Все аналогично использованию представления за исключением обязательных скобок, ограничивающих запрос; формально, достаточно лишь заменить CREATE VIEW на WITH. Как и для представления, в скобках после имени CTE может быть указан список столбцов, если нам потребуется включить их не все из подлежащего запроса и/или переименовать

CTE может использоваться для:

• Создать рекурсивный запрос.
• Замена представления, когда общее использование представления не требуется; то есть вам не нужно хранить определение в метаданных.
• Включите группировку по столбцу, который получен из скалярного подвыбора, или функции, которая не является детерминированной или имеет внешний доступ.
• Ссылаться на полученную таблицу несколько раз в одном выражении.

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

Рекурсивные CTE

Рекурсивные общие табличные выражения (CTE) были реализацией стандарта SQL: 1999 для иерархических запросов. Об этом говорит сайт https://intellect.icu . Первые реализации Рекурсивных CTE начали появляться в 2007 году. Рекурсивные CTE из стандарта были относительно близки к существующей реализации в IBM DB2 версии 2. Рекурсивные CTE в конечном итоге поддерживались Microsoft SQL Server (начиная с SQL Server 2008 R2), Firebird 2.1, PostgreSQL 8.4+, SQLite 3.8.3+, Oracle 11g Release 2 и IBM Informix версии 11.50+.

Без выражений Common-table или выражения connected-by все еще возможно выполнять иерархические запросы с помощью пользовательских рекурсивных функций, но они, как правило, приводят к очень сложному SQL.

CTE в MariaDB

В MariaDB нерекурсивный CTE в основном считается локальным представлением запроса, синтаксис которого более читабелен, чем вложенный FROM (SELECT…). CTE может ссылаться на другое и на него можно ссылаться из нескольких мест.

Таким образом, CTE аналогичны производным таблицам. Например:

SQL с производной таблицей:

SQL с CTE:

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

С помощью рекурсивных CTE вы можете добиться того, что было бы очень трудно сделать со стандартным SQL, и с более высокой скоростью выполнения. Они могут помочь решить многие типы бизнес-задач и даже упростить некоторую сложную логику SQL/приложения вплоть до простого рекурсивного вызова базы данных.

Некоторые примеры использования для рекурсивного CTE: найти пробелы в данных, создать организационные диаграммы и создать тестовые данные.

WITH RECURSIVE означает рекурсивный CTE. Ему присваивается имя, за которым следует тело (основной запрос):

Ниже приведен рекурсивный CTE, который насчитывает от 1 до 50.
  

Вышеприведенное утверждение печатает ряд чисел от 1 до 49.

CTE в MySQL

MySQL 8.0 добавляет CTE через стандартное ключевое слово WITH, почти так же, как это реализовано в конкурирующих продуктах.

Чтобы указать общие табличные выражения, используйте предложение WITH, содержащее один или несколько разделенных запятыми подпунктов. Каждый подпункт обеспечивает подзапрос, который создает набор результатов и связывает имя с подзапросом. В следующем примере определяются CTE с именами cte1 и cte2 в предложении WITH и ссылки на них в SELECT верхнего уровня, который следует за предложением WITH:

Сразу перед SELECT для операторов, которые включают оператор SELECT:

• INSERT … WITH … SELECT …
• REPLACE … WITH … SELECT …
• CREATE TABLE … WITH … SELECT …
• CREATE VIEW … WITH … SELECT …
• DECLARE CURSOR … WITH … SELECT …
• EXPLAIN … WITH … SELECT …

Рекурсивное общее табличное выражение – это выражение, имеющее подзапрос, который ссылается на собственное имя. Например:

  

+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

Вау!! 😲 Ты еще не читал? Это зря!

• подзапросы SQL
• оконная функция , оконные функции , window functions ,

Исследование, описанное в статье про запросы with, подчеркивает ее значимость в современном мире. Надеюсь, что теперь ты понял что такое запросы with, общие табличные выражения, cte и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL