Соединения таблиц в предложении 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), либо как временная таблица с промежуточным хранением данных.