Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6782 / 673 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 8:

Соединения таблиц в предложении SELECT

Подзапросы и разложение запроса на подзапросы

Подзапросы в тексте запроса

Обычные, вложенные подзапросы (запросы внутри запросов) могут быть:

  • скалярными, возвращающими одно значение какого-то типа (не обязательно простого, а, например, составного, объектного): формально — одностолбцовыми и одно- либо нульстрочными;
  • однострочными, возвращающими набор значений в форме строки;
  • многострочными, возвращающими произвольное множество строк.

Подзапросы этих категорий могут возникать в разных местах предложения SELECT и предложений DML по изменению данных (рассматриваются далее):

  • в выражениях в качестве значения (однозначные);
  • в условных выражениях (WHERE или CASE) как операнд сравнения (однозначные);
  • в условных выражениях (WHERE или CASE) как операнд сравнения со списком (многостолбцовые однострочные);
  • в условных выражениях (WHERE или CASE) как операнд сравнения в операторах сравнения с кванторами ANY и ALL, IN с подзапросом (многострочные);
  • во фразе SET предложения UPDATE (однозначные и многостолбцовые однострочные);
  • в предложении INSERT INTO … AS SELECT (многострочные);
  • в предложениях SELECT, INSERT, UPDATE, DELETE, MERGE везде, где разрешено указывать имена таблиц (многострочные).

Зоны видимости имен таблиц и их столбцов при использовании вложенных подзапросов поясняется следующим примером:


Таблица A видна из Q1, Q3, Q4, Q5. Таблица B видна из Q3, Q5.

Указание ORDER BY в подзапросе имеет смысл только в одном особом случае "запроса с квотой": типа TopN (отбор первых N записей). Любопытно, что именно в этом случае сортировка технически в полном объеме выполняться как раз не будет, в то время как в остальных применениях ORDER BY в подзапросе, несмотря на бессмысленность, будет!

Вынесенные подзапросы, или разложение запроса на подзапросы с помощью фразы WITH

Oracle допускает вынесение определений подзапросов из тела основного запроса с помощью особой фразы WITH. Эта техника получила название subquery factoring, то есть "факторизация", "разложение на подзапросы".

Фраза WITH используется в двух целях:

  • для придания запросу формулировки, более понятной программисту (просто subquery factoring) и
  • для записи рекурсивных запросов (recursive subquery factoring).

Обе формулировки фразы WITH не противоречат друг другу и могут использоваться совместно. Первый вариант фразы WITH не отменяет описательного характера предложения SELECT и (помимо удобства формулировки) способен разве что дать ускоренное общее выполнение. Рекурсивный же вариант фразы WITH по сути откровенно процедурен и тем противоречит описательному характеру предложения SELECT, положенному когда-то в основу SQL.

Простое и рекурсивное разложения на подзапросы с помощью фразы WITH рассматриваются ниже.

Вынесение определений подзапросов ради удобства формулировки

Возможность была введена в версии 9.0 в соответствии со стандартом SQL:1999. В стандарт же она попала из правил построения выражений над отношениями в реляционной теории. Фраза WITH в этом качестве — неисполняемая и предназначена в первую очередь для придания тексту сложного запроса более понятную структуру. Но сверх этого она может способствовать более эффективному вычислению ответа на запрос.

Фраза WITH предшествует фразе SELECT и позволяет привести сразу несколько предварительных формулировок подзапросов для ссылки на них в нижеформулируемом основном запросе. Общая схема употребления демонстрируется следующей схемой:

WITH 
  x AS ( SELECT ... )
, y AS ( SELECT ... FROM x )
, z AS ( SELECT ... FROM x, y )
SELECT ... FROM x, y, z, w
;

Пример употребления:

WITH
 commissioners AS ( SELECT * FROM emp WHERE comm IS NOT NULL )
SELECT
  ename
, deptno
, sal + comm AS earnings
FROM  commissioners
;

Следующий пример позволяет пользователю SYS выдать сведения о десяти запросах к БД, более всех остальных выполняющих логические обращения к диску:

WITH buffergets AS (
SELECT
    u.username
  , q.buffer_gets
  , q.executions
  , q.buffer_gets / CASE q.executions WHEN 0 THEN 1 ELSE q.executions
                    END 
    "read/exec ratio"
  , q.command_type
  , q.sql_text
FROM
    v$sqlarea q
  , dba_users u
WHERE
    q.parsing_user_id = u.user_id
ORDER BY 2 DESC
)
SELECT * FROM buffergets WHERE ROWNUM <= 10
/

В процессе вычисления подзапрос с предварительной формулировкой в зависимости от обстоятельств может вычисляться либо как неименованное представление данных ("вписанное в запрос представление данных", inline view), либо как временная таблица с промежуточным хранением данных.

Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Ольга Чорная
Ольга Чорная
Израиль, Ашкелон
Александр Теглей
Александр Теглей
Россия, Липецк, ЛГТУ, 2005