Sqlite WITH (CTE)
Режисcёрская версия распологается на сайте:
(Там есть пример использования и сравнение скорости выполнения на разных объёмах данных)
Введение
Во время написания использовалась Sqlite 3.45.1
Заметка конкретно про WITH CTE основана на моём опыте. Я мог что то упустить, или забыть.
Чтобы получить полную информацию про эту конструкцию, рекомендую изучить официальную документацию:
WITH - это конструкция для создания временного набора данных, существующих только для этого sql запроса.
Базовый синтаксис
WITH cte_name (column1, column2, ...) AS (
sub_query
) SELECT * FROM cte_name;
Компоненты:
- WITH: Ключевое слово для инициализации CTE
- cte_name: Имя временной таблицы, на которую можно ссылаться
- (column1, column2, ...): Название столбцов для CTE (Опционально)
- AS (sub_query): Подзапрос, определяющий содержимое CTE
Можно определять несколько CTE в одном WITH, разделяя их запятыми:
WITH cte1_name AS (
SELECT A, B FROM Table
),
cte2_name AS (
SELECT C FROM Table2
) SELECT * FROM cte1_name, cte2_name;
Здесь создаются два представления данных, из таблиц Table и Table2. Эти таблицы располагаются в текущей открытой базе данных.
Внутри CTE можно использовать существующие таблицы из базы данных, другие CTE, представления, любые другие объекты, доступные через SELECT.
Сравнение WITH и JOIN и критерии выбора
Используйте WITH, когда:
- Нужно упросить сложный запрос
- Требуется рекурсия
- Требуется переиспользование подзапроса
- Нужно улучшить читаемость
Используйте JOIN, когда:
- Нужно объединить данные из связанных таблиц
- Требуется прямое соединение по ключам
Сравнение по критериям:
- Назначение:
- WITH (CTE): Создание временных именованных наборов данных для упрощения запроса
- JOIN: Объединение данных из нескольких таблиц на основе связей
- Время жизни:
- WITH (CTE): Существует только в рамках одного запроса
- JOIN: Выполняется непосредственно при запросе
- Читаемость:
- WITH (CTE): Высокая — разбивает сложную логику на понятные блоки
- JOIN: Может быть сложной при множественных JOIN
- Многократное использование:
- WITH (CTE): CTE можно использовать несколько раз в одном запросе
- JOIN: Нужно повторять JOIN для каждого использования
- Рекурсия:
- WITH (CTE): Поддерживает рекурсивные запросы (WITH RECURSIVE)
- JOIN: Не поддерживает рекурсию
- Производительность:
- WITH (CTE): CTE материализуется один раз
- JOIN: JOIN может быть оптимизирован для каждого использования
- Синтаксис:
- WITH (CTE): Определяется перед основным запросом: WITH ... AS (...)
- JOIN: Встраивается в основной запрос: JOIN ... ON ...
- Связь данных:
- WITH (CTE): Логическое разделение этапов обработки данных
- JOIN: Физическое объединение строк по условию
- Использование для иерархий:
- WITH (CTE): Идеально подходит для древовидных структур
- JOIN: Требует самосоединения (self-join) и сложной логики
- Возможность модификации:
- WITH (CTE): Можно использовать с INSERT, UPDATE, DELETE
- JOIN: Используется только в SELECT (для чтения)