Вопрос по подзапросам -- вычисляемые поля. Когда и как выполняется подзрапрос, порядок выполнения

Forums:

В учебнике Бена Форта имеется вот такой пример к главе 11 "Использование подзапросов":

SELECT cust_name, cust_state,
(SELECT COUNT(*)
 FROM Orders
 WHERE Orders.cust_id = Customers.cust_id) AS orders
 FROM Customers
 ORDER BY cust_name;

Для ясности поясню - подразумевается выборка количества заказов (которые хранятся в таблице Orders) каждого клиента (список которых хранится в таблице Customers,). Вот отрывок из пояснения к коду:

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

Непонятно вот что: за счет чего осуществляется переключение поля cust_id таблицы Customers на следующее значение? Другими словами, если внутренний SELECT выполняется всегда перед внешним, как программа знает, что при новом проходе цикла нужно сравнивать идентификаторы таблицы Orders уже со следующим идентификатором таблицы Customers? Или скобки меняют порядок выполнение селектов?

vedro-compota's picture

SELECT cust_name, cust_state,
(SELECT COUNT(*)
 FROM Orders
 WHERE Orders.cust_id = Customers.cust_id) AS orders
 FROM Customers
 ORDER BY cust_name;

этот запрос работает в mysql? Проверяли?

_____________
матфак вгу и остальная классика =)

Voronve's picture

Да, работает, и корректно.

vedro-compota's picture

Откуда можно подтянуть дамп базы?

_____________
матфак вгу и остальная классика =)

Voronve's picture

Коды для копирования базы данных находятся по этому адресу http://www.forta.com/books/0672325675/

vedro-compota's picture

Удобнее сразу взять подготовленный нами скрипт: http://fkn.ktu10.com/?q=node/8945
потому что в оригинале там есть символы, которые не "развернёшь" -- т.е. приходится удалять комментарии, если хочешь это выполнить как запрос для mySQL, а у нас всё корректно)

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

если внутренний SELECT выполняется всегда перед внешним,

Почему вы думаете что он выполняется перед внешним? Ведь в цитате из учебника сказано, что он выполняется для каждой выбранной строки из освного запроса (как вы уже цитировали):

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

-- т.е. чтобы понять механизм нам надо смотреть исходный код mysql (что непросто), по сразу можно сказать, что реализовать подобное можно, только можно выполняя подзапрос "после" после :
или дейсвительно после извлечения очередного кортежа в основном запросе

  1. или (что чуть сложнее объяснить) после того как все кортежи основного запроса будут извлечены -- выполнить запрос уже к таблице Customers, выбирая записи только с id из списка, извлеченного в основном запросе -- и группируя их по этому id, таким образом мы сможем отдельно получить колонку orders ("AS orders") -- и добавить её значения в каждый кортеж выборки основного запроса

-- но тут повторюсь, что второй пункт это размышления о возможной внутренней реализации, про представлять, что:
подзапрос выполняется с использованием очередного выбранного в основоном запросе Customers.cust_id
(во как))

--------------------

за счет чего осуществляется переключение поля cust_id таблицы Customers на следующее значение?

-- как сказано выше: просто просматриваются аналогичные id из Orders,
т.е. енсли вы про связку:

(SELECT COUNT(*)
 FROM Orders
 WHERE Orders.cust_id = Customers.cust_id)

то тут Customers.cust_id по сути просто берется из результата основного запроса (для каждого результата основоного запроса -- т.е. для каждой извлеченной строки/кортежа).

_____________
матфак вгу и остальная классика =)

Voronve's picture

Почему вы думаете что он выполняется перед внешним?

Видимо, я немного запутался. Просто у того же Бена Форта есть такой пример :

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');

И соответствующее пояснение о том что

Подзапросы всегда обрабатываются, начиная с самого
внутреннего оператора SELECT в направлении "изнутри на-
ружу".

Но, видимо, если мы говорим о вычисляемых полях тут этот принцип не действует?

vedro-compota's picture

Но, видимо, если мы говорим о вычисляемых полях тут этот принцип не действует?

Да, я хотел бы заметить:

  1. что в вашем втором примере:
    SELECT cust_id
    FROM Orders
    WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'RGAN01');
    

    -- подзапроса находится после WHERE осного запроса, а значит условия основонго запроса просто не будут известны, если предварительно не выполнить подзапрос.
    А потому в этом случае вы правы -- подзапрос в любом случае полнится перед основным, если бы у этого подзапроса был свой подзапрос и он бы выполнился ещё раньше и т.д.

  2. В запросе же с вычисляемым полем, подзапрос должен по сути вернуть одно значения для каждого кортежа (так или иначе) в нашем случае это достигается, за счет наложения в подзапросе условия, которое связывается результат с одним из значений основного запроса, и этот подзапрос стоит перед WHERE основного запроса:
    SELECT cust_name, cust_state,
    (SELECT COUNT(*)
     FROM Orders
     WHERE Orders.cust_id = Customers.cust_id) AS orders
     FROM Customers
     ORDER BY cust_name;
    

    -- здесь для основного запроса все условия известны и без выполнения подзапроса, более того, как как подзапрос опирается на значение из основного, то выполнить его раньше итак не получится.

Таким образом ывше высказывание:

Но, видимо, если мы говорим о вычисляемых полях тут этот принцип не действует?

в данном случае верно.

_____________
матфак вгу и остальная классика =)

Voronve's picture

наложения в подзапросе условия, которое связывается результат с одним из значений основного запроса

Вот именно эта внутренняя логика связывания результата подзапроса с одним из полей основного запроса в SQL мне и непонятна. Я так понял, мне на моем уровне это пока проще просто запомнить ). Спасибо за пояснение