Трюк SQL: вкладений запит у предикаті FROM

Можливо, ви не знали, що в предикаті FROM можна використовувати підзапит. Але тепер ви знаєте.
Більше того, результати підзапиту можна об’єднати з іншими частинами предикату FROM.

Розглянемо приклад. Є таблиці «orders» та «products», пов’язані за принципом «багато-до-багатьох» зі зведеною таблицею «order_product».

Data schema

Нам потрібно створити звіт про замовлення за серпень 2026 року. Проблема: таблиця «orders» дуже велика. «order_product» також. Тому дуже добре, якщо join почне працювати після того, як замовлення для звіту будуть вибрані.

Ми досягнемо цього за допомогою підзапиту в предикаті FROM:

SELECT filtered_orders.*,
    count(order_product.id) n,
    sum(order_product.quantity*order_product.price) amount
FROM
    (
     SELECT orders.* FROM orders 
     WHERE created_at BETWEEN '2026-08-01 00:00:00' AND '2026-08-31 23:59:59'
    ) filtered_orders
    JOIN order_product ON (filtered_orders.id = order_product.order_id)
GROUP BY filtered_orders.id
ORDER BY filtered_orders.created_at

filtered_orders називається derived table і може брати участь у JOIN-ах.

У сучасних DBMS (наприклад, в MySQL, починаючи з 8.0) можна використовувати CTE (Common Table Expressions) для визначення derived table, що поліпшить читання запиту. Ось як виглядатиме наш запит при використанні CTE:

WITH filtered_orders as (SELECT orders.* FROM orders 
    WHERE created_at BETWEEN '2026-08-01 00:00:00' AND '2026-08-31 23:59:59')
SELECT filtered_orders.*,
       count(order_product.id) n,
       sum(order_product.quantity*order_product.price) amount
FROM
    filtered_orders
      JOIN order_product ON (filtered_orders.id = order_product.order_id)
GROUP BY filtered_orders.id
ORDER BY filtered_orders.created_at;

На сучасних SQL-серверах ми можемо не побачити різниці у швидкості виконання запиту. Так, для MySQL 8.4 я не побачив різниці в порівнянні з запитом, що використовує прості JOIN-и при розмірі таблиць «orders» та «products» у мільйон записів. Це пояснюється внутрішньою оптимізацією запитів: DBMS тепер розумні і самі переробляють запити більш оптимально, зокрема, використовуючи викладену методику.

Але, по-перше, краще все-таки відразу писати оптимальні запити, не сподіваючись, що сервер тебе поправить, а по-друге, використання підзапитів у предикаті FROM може дозволити виконати досить складну логіку одним запитом. Це корисно мати у своєму арсеналі.

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

👍ПодобаєтьсяСподобалось3
До обраногоВ обраному0
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Я в першому коментарі звернув увагу тільки на синтаксис — не було настрою вдумуватись. Але зараз передивився... а чи є взагалі в даному випадку сенс у вкладеному запиті?

Фактично у вас є JOIN двох таблиць. І ви фільтруєте одну з них за дуже простим критерієм: діапазон одного параметра. В будь-якій сучасній базі оптимізатор сам впорається з таким, вирішивши, яке з джерел треба попередньо профільтрувати (або не треба). Є багато статей, як це роблять MySQL, Postgres, інші. Коротко — вони оцінюють розмір даних, який буде вибраний, витрати на виборку, і будують дерево виконання так, щоб скоротити сумарну кількість операцій.

Ні, приклад має бути більш складний. В субзапиті має щось бути таке, щоб виносити в один «плоский» запит (навіть якщо в ньому купа JOIN-ів) було надто складно.

У класичній книзі Дейта «Введення в системи баз даних» є такі приклади:

[cut]
8.3.13. Отримати номера постачальників, статус яких менше поточного максимального статуса в таблиці S:

SELECT S.S#
FROM S
WHERE S.STATUS < (
    SELECT MAX(S.STATUS) FROM S
    );
[/cut]

або:

[cut]
8.3.15. Отримати номери постачальників, що не постачають деталь P2:

SELECT DISTINCT S.SNAME
FROM S
WHERE NOT EXISTS (
    SELECT *
    FROM SP
    WHERE SP.S# = S.S#
        AND SP.P# = "P2"
    )
або альтернативно:
SELECT DISTINCT S.NAME
FROM S
WHERE S.S# NOT IN (
    SELECT SP.S#
    FROM SP
    WHERE SP.P# = "P2"
    )

[/cut]

Ці випадки незручно формулювати у плоскому вигляді, якщо взагалі можливо.

Claude мені видав такий приклад, ще складнішій:

[cut]
«The three most recent orders per customer»:

SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS rn
    FROM orders o
) t
WHERE rn <= 3;
An ordering problem applies: WHERE runs before windows, so WHERE ROW_NUMBER() OVER (...) <= 3 is illegal. The subquery-free alternative is a self-join that counts newer orders, HAVING COUNT(*) < 3, which is quadratic per customer and mishandles ties unless you add tie-breakers.
[/cut]

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

==а чи є взагалі в даному випадку сенс у вкладеному запиті?==

Мабуть, приклад не дуже переконливий. Головне, що я хотів показати, — що можна використовувати derived table у предикаті FROM.

Подумаю над більш переконливими прикладами.

То что нарисовал клод — решает QUALIFY без под запросов. Да сахар, но без возни с под запросами.

Я взагалі лякаюсь віконних функцій,

Встретить чистый сиквел без них — это редкость.

То что нарисовал клод — решает QUALIFY без под запросов.

В MySQL и Postgres его нету. И даже в Microsoft SQL нету. У меня сейчас не тот домен, чтобы хоть как-то смотреть на другие движки. Так что исключаем. За факт спасибо, но посмотрим, когда будет хоть где-то ближе.

Встретить чистый сиквел без них — это редкость.

Знаю.
Про «боюсь» я чуть преувеличил:)) но опыта с ними реально слишком мало, а внимательность нужна повышенная.

Пригадую, ще 25 років тому цей спосіб не рекомендували, натомість пропонувалися CTE чи views.

якщо це corelated subquery то запит буде важкий

SELECT *
                FROM Customers outer
                WHERE 10/03/1990 IN
                  ( SELECT odate
                       FROM Orders inner
                       WHERE outer.cnum = inner.cnum );

== натомість пропонувалися CTE чи views. ==

CTE покращити читаність, але нічого не змінить у виконанні запиту.

Але мені стало цікаво, як у даній ситуації використовувати view — коли дати можуть бути будь-які. Було б дуже повчально.

Сплутав з параметризованими процедурами Interbase/Firebird. Там можна джойнити таблиці з результатами процедури, які є, по суті, віртуальною таблицею, і параметри передавати можна

Якось так

SELECT 
    C.NAME, 
    O.ORDER_ID, 
    O.ORDER_DATE, 
    O.TOTAL_AMOUNT
FROM CUSTOMERS C
LEFT JOIN GET_ORDERS_BY_DATE(C.ID, '2026-01-01', '2026-10-13') O ON 1=1;

Але зараз надають перевагу іншому стилю того ж: WITH клауза в запиті, яка ще зветься common table expression (CTE). Ваш запит у такому випадку переписується десь так:

WITH filtered_orders AS (SELECT orders.* FROM orders 
     WHERE created_at BETWEEN '2026-08-01 00:00:00' AND '2026-08-31 23:59:59')
SELECT filtered_orders.*,
    count(order_product.id) n,
    sum(order_product.quantity*order_product.price) amount
JOIN order_product ON (filtered_orders.id = order_product.order_id)
GROUP BY filtered_orders.id
ORDER BY filtered_orders.created_at

Є довгий список причин, чому CTE в загальному випадку краще за вкладені запити: я зараз перепитав AI:
Readability: High; breaks logic into linear, top-to-bottom steps.
Reusability: Reusable; can be referenced multiple times in the main query.
Recursion: Supported via WITH RECURSIVE. (ну це вищий пілотаж;))
Scope: Available to the entire query execution that follows it.

У вас є причини триматись за старий стиль? Сервер не підтримує CTE? Ви згадали MySQL 8.4. Він підтримує CTE починаючи з 8.0.

Я додам, що підзапити (неважливо, який з цих двох синтаксисів) рекомендовані для пейджингу, коли маєш нумерувати сторінки: тоді вкладений запит отримує лише id-и, а зовнішній вже вибирає повний набір даних. Це може економити на ціні запиту в десятки разів і більше.

А як

WITH клауза в запиті, яка ще зветься common table expression (CTE)

врятує від тормозів у разі corelated subquery? нмп, він синтаксично не дозволяє його зробити ненавмисно.
Це syntax sugar по суті, поправте будь-ласка, якщо я не прав.

Ну да, прямий ефект більш синтаксичний. Семантично в основному це усунення зайвих повторень.
(Хіба є оптимізатор, який може щось корисне зробити для одного синтаксису, а не іншого? тут мій досвід зупинився: таких складнощей не робив.)

== Ваш запит у такому випадку переписується десь так: ==

Дякую, я додав цю думку до статті, виправивши Вашу випадкову помилку в запиті.