Подзапрос во фразе FROM

В первой части по подзапросам мы упоминали, что подзапросы можно использовать во фразе FROM. Но так ни разу этим не воспользовались.

Когда это может быть полезно?

Пример из жизни. Покупать товар по самой дешевой цене всегда приятно. Однако, не всегда удается купить именно по самой дешевой цене. То магазин закрыт, то добираться неудобно...

Допустим, что нас устроит покупка по цене от минимальной до средней на этот товар по всем магазинам.

Мы уже владеем коррелированными подзапросами. Воспользуемся ими для решения задачи:

SELECT *
  FROM product_price pp
 WHERE pp.price BETWEEN
           (SELECT min (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
       AND (SELECT avg (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
 ORDER BY pp.product_id,
          pp.price

Не ошиблись ли мы? Не знаю, давай проверим на товаре с product_id = 3:

SELECT pp.product_id,
       pp.price
  FROM product_price pp
 WHERE pp.product_id = 3
 ORDER BY pp.price

Вроде бы не ошиблись. А давай в исходном запросе выведем минимальную и среднюю стоимость. Мы же умеем писать коррелированные подзапросы:

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       (SELECT round (min (ppi.price), 2)
          FROM product_price ppi
         WHERE ppi.product_id = pp.product_id
       ) AS price_min,
       (SELECT round (avg (ppi.price), 2)
          FROM product_price ppi
         WHERE ppi.product_id = pp.product_id
       ) AS price_avg
  FROM product_price pp
 WHERE pp.price BETWEEN
           (SELECT min (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
       AND (SELECT avg (ppi.price)
              FROM product_price ppi
             WHERE ppi.product_id = pp.product_id)
 ORDER BY pp.product_id,
          pp.price
          

Работает, но читать такое сложновато.

Есть решение лучше. Давай сначала по каждому товару найдем минимальную и среднюю стоимость, а затем найдем товары с ценой в диапазоне от минимальной до средней.

1. Определим минимальную и среднюю стоимость по товару:

SELECT pp.product_id,
       min (pp.price) AS price_min,
       avg (pp.price) AS price_avg
  FROM product_price pp
 GROUP BY pp.product_id

2. Зная минимальную и среднюю стоимость по товару, возьмем товары с нужными ценами.

Можно использовать подзапросы во фразе FROM. В таком случае к результатам подзапроса можно обращаться как к обычной таблице:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) as ppma

Правда есть одно условие: результату подзапроса нужно назначить псевдоним. Если не задать псевдоним, то возникнет ошибка:

SELECT *
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) 

error: subquery in FROM must have an alias

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

SELECT pp.product_id,
       pp.store_id,
       pp.price,
       round (ppma.price_min, 2) AS price_min,
       round (ppma.price_avg, 2) AS price_avg
  FROM (SELECT pp.product_id,
               min (pp.price) AS price_min,
               avg (pp.price) AS price_avg
          FROM product_price pp
         GROUP BY pp.product_id
       ) ppma,
       product_price pp
 WHERE pp.product_id = ppma.product_id
   AND pp.price BETWEEN ppma.price_min AND ppma.price_avg
 ORDER BY pp.product_id,
          pp.price