Во время написания использовалась Sqlite 3.45.1

Заметка конкретно про WITH CTE основана на моём опыте. Я мог что то упустить, или забыть.
Чтобы получить полную информацию про эту конструкцию, рекомендую изучить официальную документацию:
The WITH Clause

WITH - это конструкция для создания временного набора данных, существующих только для этого sql запроса.

Базовый синтаксис

WITH cte_name (column1, column2, ...) AS (
    sub_query
) SELECT * FROM cte_name;

Можно определять несколько 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:
  • Нужно упросить сложный запрос
  • Требуется рекурсия
  • Переиспользование подзапроса
  • Улучшить читаемость
• JOIN:
  • Объединить данные из связанных таблиц
  • Нужно прямое соединение по ключам

• Начиная с версии Sqlite 3.35.0 CTE не всегда может создавать временную таблицу. Движок Sqlite может превратить CTE в отдельный подзапрос, или создать таблицу.

Примеры

Задача: получить список клиентов с сумой всех их заказов и оставить только тех, у кого сумма >= 100.

Ожидаемый результат:

+----+-------+--------------+
| id | name  | total_amount |
+----+-------+--------------+
| 1  | Alice | 150.0        |
| 2  | Bob   | 200.0        |
+----+-------+--------------+

Подготовка данных

Дальнейшие примеры будут на этих таблицах:

CREATE TABLE customers (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount      REAL NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers (id, name) VALUES
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Carol');

INSERT INTO orders (id, customer_id, amount) VALUES
    (1, 1, 100.0),
    (2, 1, 50.0),
    (3, 2, 200.0),
    (4, 3, 10.0);

Здесь мы создаём две таблицы customers и orders, и связываем их по id. После заполняем таблицы данными.

Без использования CTE

SELECT
    c.id,
    c.name,
    (
        SELECT SUM(o.amount)
        FROM orders o
        WHERE o.customer_id = c.id
    ) AS total_amount
FROM customers c
WHERE (
    SELECT SUM(o.amount)
    FROM orders o
    WHERE o.customer_id = c.id
) >= 100;

Здесь используется вложенный подзапрос (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id = c.id). Этот подзапрос используется два раза: первый для вывода в результат, второй при фильтрации через WHERE.

Коррелирующий запрос c.id (WHERE o.customer_id = c.id) выполняется для каждой строки из customers.

Также здесь и далее используется краткие наименования (Alias):

• FROM customer c - создаёт псевдоним c для таблицы customer
• FROM orders o - создаёт псевдоним o для таблицы orders

Результат запроса:

+----+-------+--------------+
| id | name  | total_amount |
+----+-------+--------------+
| 1  | Alice | 150.0        |
| 2  | Bob   | 200.0        |
+----+-------+--------------+
Run Time: real 0.000 user 0.000119 sys 0.000085

С одним CTE

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.id,
    c.name,
    ct.total_amount
FROM customers c
JOIN customer_totals ct ON ct.customer_id = c.id
WHERE ct.total_amount >= 100;

Здесь используется CTE customer_totals, который существует только в рамках этого sql запроса. Внутри CTE используется агрегирующая функция с GROUP BY. Она считывает сумму заказов по группам клиентов. На выходе таблица с двумя столбцами customer_id и total_amount.

Пример вывода customer_totals:

SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id;
+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
| 1           | 150.0        |
| 2           | 200.0        |
| 3           | 10.0         |
+-------------+--------------+

Результат запроса:

+----+-------+--------------+
| id | name  | total_amount |
+----+-------+--------------+
| 1  | Alice | 150.0        |
| 2  | Bob   | 200.0        |
+----+-------+--------------+
Run Time: real 0.001 user 0.000141 sys 0.000100

С двумя CTE

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
),
important_customers AS (
    SELECT
        customer_id,
        total_amount
    FROM customer_totals
    WHERE total_amount >= 150
)
SELECT
    c.id,
    c.name,
    ic.total_amount
FROM important_customers ic
JOIN customers c ON c.id = ic.customer_id;

Здесь уже используется два CTE: customer_totals и important_customers, разделённые запятой. Важный момент - important_customers использует результат customer_totals:

important_customers AS (
...
    FROM customer_totals
...
)

В отличие от предыдущего варианта с одним CTE, здесь фильтрация происходит в important_customers,
а итоговый SELECT получает готовую таблицу, из которой собирает результат.

Результат запроса:

+----+-------+--------------+
| id | name  | total_amount |
+----+-------+--------------+
| 1  | Alice | 150.0        |
| 2  | Bob   | 200.0        |
+----+-------+--------------+
Run Time: real 0.001 user 0.000198 sys 0.000141

Сравнение производительности с/без CTE

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

В примерах рассмотрено три варианта решения задачи, и приведены замеры времени.
Получились такие данные:

На малом количества данных различия минимальны. По этому проверим скорость работы, если заполнить таблицу большим количеством значений. Вводить сотни тысяч строк в базу - это очень долго. Так что можно воспользоваться конструкцией WITH RECURSIVE:

INSERT INTO customers (id, name)
WITH RECURSIVE cte(n) AS (
    SELECT 4                  -- Начинаем с 4, т.к. уже есть значения в таблице, а поле стоит как уникальное
    UNION ALL
    SELECT n + 1 FROM cte
    WHERE n < 100000          -- Сколько строк нужно
)
SELECT
    n AS id,
    'Customer ' || n AS name
FROM cte;

Здесь мы с помощью WITH RECURSIVE вставляем в таблицу customers последовательно сгенерированные значения.
Запрос отработал: Run Time: real 0.097 user 0.092234 sys 0.004402

Далее добавим 300’000 последовательных значений в таблицу orders:

INSERT INTO orders (id, customer_id, amount)
WITH RECURSIVE cte(n) AS (
    SELECT 4                  -- Начинаем с 4, т.к. уже есть значения в таблице, а поле стоит как уникальное
    UNION ALL
    SELECT n + 1 FROM cte
    WHERE n < 300000           -- Сколько заказов нужно
)
SELECT
    n AS id,
    (abs(random()) % 100000) + 1 AS customer_id,  -- Случайный customer_id в диапазоне 1..100000
    (abs(random()) % 1000) + 1 AS amount          -- Случайная сумма от 1 до 1000
FROM cte;

Запрос отработал: Run Time: real 0.336 user 0.323472 sys 0.010698

И теперь выполним те же запросы, но с большим количеством данных, и запишем результаты в таблицу:

Данные по таблице:

+-----------+--------+
| customers | orders |
+-----------+--------+
| 99999     | 300000 |
+-----------+--------+

Таким образом решение “в лоб” без использования CTE заняло больше 40 минут. Использование одного или двух временных таблиц сократило время выполнения примерно в 3,600 раз! А разница между использованием одного или двух CTE не такая большая (Буквально 4 тысячных секунды).
Такая разница объясняется тем, что коррелирующий подзапрос выполняется дважды для каждой строки таблицы customers(SELECT и WHERE), при этом каждое выполнение сканирует всю таблицу orders. А при создании временной таблицы - один раз для всех данных.