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

Поясняющие примеры соединений

Здесь приводятся примеры разных видов соединений по критерию полноты. В данном случае речь идет о "самосоединениях", то есть соединениях по значениям столбцов фактически одной и той же таблицы-источника, указанной во фразе FROM более одного раза. Самосоединения позволяют взглянуть на одну таблицу с разных смысловых точек зрения. В нашем случае таблица EMP воспринимается (а) как таблица с данными о подчиненных и (б) как таблица с данными о начальниках.

Пример закрытого соединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr = chief.empno
;

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

Пример полуоткрытого "влево" соединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr = chief.empno ( + )
;

Пример "левого" антисоединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr = chief.empno ( + ) AND chief.ename IS NULL
;

Пример полуоткрытого "вправо" соединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr ( + ) = chief.empno
;

Пример "правого" антисоединения:

SELECT 
   subordinate.ename, 'в подчинении у', chief.ename 
FROM   
   emp subordinate, emp chief
WHERE  
   subordinate.mgr ( + ) = chief.empno AND subordinate.ename IS NULL
;

Упражнение. Проверьте результат выполнения приведенных соединений.

До введения в версии Oracle 9 специального синтаксиса непосредственная запись открытого соединения была невозможна (иными словами, приписать '( + )' к обоим соединяемым столбцам одновременно не разрешается) и ее приходилось моделировать объединением результатов двух полуоткрытых соединений.

Приведенные выше формулировки полуоткрытых и антисоединений придают запросу некоторую краткость, но содержательно не сообщают ничего нового диалекту SQL в Oracle (и формулировкам стандарта SQL, о которых речь пойдет далее). Здесь в очередной раз проявляет себя избыточность SQL в Oracle и в стандарте. Так, левое антисоединение может быть сформулировано без дополнительного синтаксиса, к примеру, следующим образом:

SELECT subordinate.ename, 'в подчинении у', NULL ename
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.mgr = chief.empno
     )
;

Хотя это выглядит более тяжеловесно, чем со специальной записью, но смысл запроса стал более понятен. Еще более тяжеловесна, но также более ясна в своем действии полученная отсюда формулировка для левого полуоткрытого соединения:

SELECT subordinate.ename, 'в подчинении у', chief.empno
FROM   emp subordinate, emp chief
WHERE  subordinate.mgr = chief.empno
UNION ALL
SELECT subordinate.ename, 'в подчинении у', NULL
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.mgr = chief.empno
     )
;

Аналогичная пара для правого анти- и открытого соединений может быть выражена так:

SELECT NULL ename, 'в подчинении у', subordinate.ename
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.empno = chief.mgr
     )
;
SELECT chief.ename, 'в подчинении у', subordinate.ename
FROM   emp subordinate, emp chief
WHERE  subordinate.empno = chief.mgr
UNION ALL
SELECT NULL ename, 'в подчинении у', subordinate.ename
FROM   emp subordinate
WHERE NOT EXISTS
     ( SELECT chief.ename
       FROM   emp chief
       WHERE  subordinate.empno = chief.mgr
     )
;

И это не единственные примеры альтернативных формулировок. Примечательно, что часто Oracle технически обрабатывает разные формулировки по-разному.

Упражнение. На основе приведенных формулировок постройте запрос на полное самосоединение с информацией о подчиненности сотрудников.

Предостерегающий и типовой примеры полуоткрытых соединений

Использование полуоткрытых соединений плодотворно для программиста, но их составление, как и многое в SQL, требует внимания. Ниже в виде упражнений рассматривается пример неумышленно неправильного составления запроса и пример типового использования полуоткрытого соединения.

Упражнение 1. Упражнение обращает внимание на осторожность, которую следует соблюдать в употреблении полуоткрытого соединения. Пусть нужно выдать список имен отделов, число работающих и фонд зарплаты для каждого. Объясните результат следующего решения:

SELECT   dname, COUNT ( * ) emp_count, SUM ( sal ) tot_sal
FROM     emp, dept 
WHERE    emp.deptno ( + ) = dept.deptno
GROUP BY dname
;

Предложите изменение запроса, позволяющее получить правильный ответ.

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

SELECT 
   dname
 , ( SELECT COUNT ( * ) FROM emp e WHERE e.deptno = d.deptno ) emp_count
 , ( SELECT SUM ( sal ) FROM emp e WHERE e.deptno = d.deptno ) tot_sal
FROM dept d
;

Ответы (возможно) будут разниться только техникой обработки, однако тема оптимизации запросов здесь не рассматривается. Если ее не касаться, выбор конкретной формулировки запроса в подобных случаях — дело вкуса программиста и удобства восприятия.

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

CREATE TABLE pivot_years 
AS 
  SELECT ( ROWNUM - 1 ) + 1980 AS year 
  FROM   emp 
  WHERE  ( ROWNUM - 1 ) <= 10
;

Она плотно заполнена "значениями года", от 1980 до 1990. Тогда следующий запрос выдаст сведения о количестве сотрудников, приходивших на работу в указанных в PIVOT_YEARS годах:

SELECT   p.year, COUNT ( e.empno ) 
FROM     pivot_years p, emp e 
WHERE    p.year = EXTRACT ( YEAR FROM e.hiredate ( + ) ) 
GROUP BY p.year 
ORDER BY p.year
;

Pivot table — это своего рода "реперная", или "опорная", "градуировочная", "калибровочная" таблица, помогающая анализировать данные. Ее можно сделать универсальной, если заполнить числами от 1 до n. Последний SELECT в этом случае придется слегка поправить.

В виде самостоятельного упражнения предлагается использовать технику реперной таблицы для построения запроса о количестве подчиненных у всех имеющихся сотрудников.

(Опорная таблица не обязана быть статичной. Аппарат табличных функций в PL/SQL позволяет построить функцию, способную порождать таблицу из n строк со значениями от 1 до n динамически).

Синтаксис для операции соединения, разрешенный с версии 9

Приводимый ранее способ записи полуоткрытого соединения является собственным решением Oracle (он взят из потерявшего силу стандарта SQL 1986 года и сейчас является нестандартизованным элементом диалекта SQL Oracle). В сложных запросах он может оказаться неудобочитаемым и провоцировать ошибки программирования. С версии 9 Oracle можно (и рекомендуется) для записи разных видов соединений использовать специально разработанные в стандарте SQL:1999 синтаксические конструкции. Они рассматриваются ниже.

Закрытые соединения

Пример записи обычного (внутреннего, или закрытого) соединения в соответствии с SQL:1999:

SELECT e.ename, d.dname 
FROM   emp e
       INNER JOIN
       dept d
       ON ( e.deptno = d.deptno )
;

Обратите внимание, что такая формулировка разрешает привести в конструкции ON любое условное выражение (<=, <>, LIKE и так далее), например:

SELECT e.ename, e.sal, s.grade
FROM   emp e
       INNER JOIN
       salgrade s
       ON ( e.sal BETWEEN s.losal AND s.hisal )
;

Если же, как в предпоследнем случае, речь идет об эквисоединении (сравнении на совпадение величин) с одинаковыми названиями столбцов приравниваемых значений, годится и иная формулировка:

SELECT e.ename, d.dname 
FROM   emp e 
       INNER JOIN
       dept d 
       USING ( deptno )
;

У формулировки INNER JOIN … USING есть отличие от формулировки INNER JOIN … ON: во внутреннем декартовом произведении строк таблиц, которое строится фразой FROM в соответствии с логической схемой обработки предложения SELECT (приводилась выше) автоматически удаляются повторяющиеся столбцы. Это свойство унаследовано от реляционного соединения, где из результата автоматически удаляются одинаковые атрибуты (это не то же, что одинаково названные столбцы в таблицах SQL).

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

SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
SELECT * FROM emp INNER JOIN dept USING ( deptno );

Как следствие (вполне логичное) соединения столбцы, участвующие в соединении, не требуют уточнения именем таблицы при ссылке на них в других местах предложения, например:

SELECT e.ename, d.dname, deptno
FROM   emp e
       INNER JOIN
       dept d
       USING ( deptno )
;

С другой стороны, когда заходит речь о самосоединении, это может приводить к проблеме:

SQL> SELECT * FROM dept INNER JOIN dept USING ( deptno );
SELECT * FROM dept INNER JOIN dept USING ( deptno )
                                           *
ERROR at line 1:
ORA-00918: column ambiguously defined

Успокаивает то, что с точки зрения приложения подобные запросы редко имеют смысл. Избавиться от этой ошибки Oracle можно введением псевдонимов. Следующие два запроса не приведут к ошибке:

SELECT * FROM dept a INNER JOIN dept b USING ( deptno ); 
SELECT * 
FROM   dept
       INNER JOIN
       ( SELECT * FROM dept )
       USING ( deptno )
; 

Первый из них формально, а второй фактически не будет считаться самосоединением.

Два полуоткрытых и открытое соединения

Примеры (внешних) полуоткрытых соединений:

SELECT e.ename, d.dname 
FROM   emp e 
       LEFT OUTER JOIN
       dept d 
       USING ( deptno )
;
SELECT e.ename, d.dname 
FROM   emp e 
       RIGHT OUTER JOIN
       dept d 
       USING ( deptno )
;

Пример (внешнего) открытого соединения:

SELECT e.ename, d.dname 
FROM   emp e 
       FULL OUTER JOIN
       dept d 
       USING ( deptno )
;

Последнее предложение не имеет равносильной записи в "старом" синтаксисе Oracle.

Естественные соединения

Для естественного закрытого соединения действует формулировка, подобная следующей:

SELECT e.ename, d.dname 
FROM   emp e 
       NATURAL INNER JOIN
       dept d
;

Фактически она работает как INNER JOIN … USING по всем столбцам с совпадающими именами. Эта формулировка соблазнительна в силу своей простоты, однако в SQL она не поощряется некоторыми специалистами как упускающая контроль над фактическим набором столбцов соединнения (ведь имена столбцов могут совпасть случайно и безотносительно к намерению разработчика БД служить средством соединения). А в реляционной модели такая формулировка соответствует единственно допустимой форме соединения и не имеет проблем потери контроля над способом соединения.

В то же время операция NATURAL INNER JOIN имеет относительную самостоятельность, хотя несколько превратно, но все же унаследованную от реляционной теории; ее можно использовать, если следить за именами столбцов соединяемых таблиц. Если столбцы таблиц вовсе не имеют совпадающих имен (в SQL!), то эта операция превращается в декартово произведение. Например, в таблице SALGRADE в схеме SCOTT три столбца: GRADE, LOSAL и HISAL и пять строк. Вот что даст естественное соединение этой таблицы с DEPT:

SQL> SELECT dname FROM dept NATURAL INNER JOIN salgrade;
DNAME
--------------
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
SALES
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
20 rows selected.

В таких случаях ответ будет совпадать с результатом действия другой операции, CROSS JOIN:

SELECT dname FROM dept CROSS JOIN salgrade;

Если же наоборот, все столбцы естественно соединяемых таблиц совпадают, операция фактически превращается в пересечение строк таблиц, как в INTERSECT. Например:

SQL> SELECT dname, loc FROM dept a NATURAL INNER JOIN dept b;
DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

Обратите внимание на неочевидное обстоятельство. Если в последнем запросе отказаться от псевдонимов, отсева повторений не происходит; значения считаются разными:

SQL> SELECT dname, loc FROM dept NATURAL INNER JOIN dept;
DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
ACCOUNTING     NEW YORK
ACCOUNTING     NEW YORK
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
RESEARCH       DALLAS
RESEARCH       DALLAS
RESEARCH       DALLAS
SALES          CHICAGO
SALES          CHICAGO
SALES          CHICAGO
SALES          CHICAGO
OPERATIONS     BOSTON
OPERATIONS     BOSTON
OPERATIONS     BOSTON
OPERATIONS     BOSTON
16 rows selected.

Заметьте, что особый эффект последней формулировки исчезает, когда соединяются две разные таблицы, пусть с одинаковой структурой:

SQL> CREATE TABLE dept1 AS SELECT * FROM dept;
Table created.
SQL> SELECT * FROM dept NATURAL INNER JOIN dept1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Естественными могут быть и полуоткрытые соединения:

SELECT e.ename, d.dname
FROM   emp e
       NATURAL LEFT OUTER JOIN
       dept d
;
SELECT e.ename, d.dname 
FROM   emp e 
       NATURAL RIGHT OUTER JOIN
       dept d 
;

Оговорки употребления, сделанные для естественного внутреннего соединения (лаконичность формулировки и риски человеческих ошибок), распространяются и на эти случаи.

Дополнительные примеры формулировок

Специальный синтаксис записи соединения не препятствует наличию в предложении SELECT фразы отбора строк WHERE:

SELECT e.ename, d.dname
FROM   emp e 
       FULL OUTER JOIN
       dept d
       USING ( deptno )
WHERE  deptno  <> 10 
  AND  d.dname <> 'RESEARCH'
;

Пример многократного (здесь — двойного) соединения:

SELECT m.ename, d.dname, d.loc 
FROM   emp m
       INNER JOIN
       ( emp e 
         INNER JOIN
         dept d 
         USING ( deptno )
        )
        ON ( e.empno = m.mgr )
;

Последнее равносильно запросу

SELECT m.ename, d.dname, d.loc 
FROM   emp m 
       INNER JOIN
          emp e 
          INNER JOIN
          dept d 
          USING ( deptno )
       ON ( e.empno = m.mgr )
;

Следующая формулировка отличается только внешним видом, но может показаться более понятной программисту:

SELECT m.ename, d.dname, d.loc
FROM   emp e
         INNER JOIN
            dept d
            USING ( deptno )
                INNER JOIN
                   emp m  
                   ON ( e.empno = m.mgr )
;

Применение в одном запросе многократных соединений омрачается ухудшением читаемости и падением скорости обработки. Читаемость запроса можно пытаться "спасти" с помощью аппарата view и разложением на подзапросы (и то и другое рассматривается ниже), а скорость обработки в Oracle обычно заметно падает при числе соединений пять и более.

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

SELECT *
FROM   dept
       NATURAL INNER JOIN
       dept1
     , emp
WHERE  loc <> 'NEW YORK'
;

Заметьте, однако, что здесь появилось декартово произведение, так что трудность приведения содержательного примера возникла не случайно. С формальной же точки зрения вместо перечисления через запятую тут можно (и более предпочтительно) применить CROSS JOIN.

Вольности синтаксиса: ключевые слова INNER и OUTER необязательны и не влияют на смысл операции соединения в SQL; круглые скобки для логического условия после ON необязательны.

Фирма Oracle рекомендует использовать для записи соединений рассмотренный синтаксис (и, например, не рекомендует для полуоткрытых соединений применять обозначение '( + )'). Эту рекомендацию можно дополнить советами использовать ON вместо USING (там, где это возможно) и применять NATURAL JOIN с крайней осмотрительностью.

В целом же приведенный синтаксис для соединений — вероятно, одно из самых удачных решений в SQL, особенно если учесть вдобавок сферу его употребления: ведь в 99% случаев, если выразиться фигурально, запрос более чем к одной таблице в SQL будет ничем иным, как соединением (или в оставшемся 1% случаев — декартовым произведением!).

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

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

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

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

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

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

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

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

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

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

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

Формулирование рекурсивных запросов

С версии 11.2 фраза WITH может использоваться для формулирования рекурсивных запросов, в соответствии (неполном) со стандартом SQL:1999. В этом качестве она способна решать ту же задачу, что и CONNECT BY, однако (а) делает это похожим с СУБД других типов образом, (б) обладает более широкими возможностями, (в) применима не только к запросам по иерархии и (г) записывается значительно более замысловато.

Общий алгоритм вычисления фразой WITH таков:

Результат := пусто;
Добавок := исходный SELECT ...;
Пока Добавок не пуст выполнять:
    Результат  :=     Результат  
            {UNION ALL | UNION | INTERSECT | EXCEPT}
Добавок;
    Добавок := рекурсивный SELECT ... FROM Добавок …;
конец цикла;

Предложение SELECT для исходного множества строк Oracle называет опорным (anchor) членом фразы WITH. Предложение SELECT для получения добавочного множества строк Oracle называют рекурсивным членом. Обратите внимание, что для вычитания множеств строк Oracle использует здесь не собственное обозначение MINUS, а стандартное EXCEPT.

Простой пример

Простой пример употребления фразы WITH для построения рекурсивного запроса:

WITH
numbers ( n ) AS (
   SELECT 1 AS n FROM dual -- исходное множество -- одна строка
      UNION ALL           -- символическое "объединение" строк 
   SELECT n + 1 AS n       -- рекурсия: добавок к предыдущему результату
   FROM   numbers           -- предыдущий результат в качестве источника данных
   WHERE  n < 5           -- если не ограничить, будет бесконечная рекурсия
)
SELECT n FROM numbers       -- основной запрос
;

Операция UNION ALL здесь используется символически, в рамках определенного контекста, для указания способа рекурсивного накопления результата.

Ответ:

         N
----------
         1
         2
         3
         4
         5

Строка с n = 1 получена из опорного запроса, а остальные строки — из рекурсивного. Из примера видна оборотная сторона рекурсивных формулировок: при неаккуратном планировании они допускают "бесконечное" выполнение (на деле — пока хватит ресурсов СУБД для сеанса или же пока администратор не прервет запрос или сеанс). С фразой CONNECT BY "бесконечное" выполнение в принципе невозможно. Программист обязан отнестись к построению рекурсивного запроса ответственно.

Еще один вывод из этого примера: подобно случаю с CONNECT BY, вынесенный рекурсивный подзапрос применим вовсе не обязательно только к иерархически организованным данным.

Пример с дополнительным разъяснением способа выполнения:

SQL> WITH
  2    anchor1234 ( n ) AS (            -- обычный
  3       SELECT 1 FROM dual UNION ALL
  4       SELECT 2 FROM dual UNION ALL
  5       SELECT 3 FROM dual UNION ALL
  6       SELECT 4 FROM dual
  7    )
  8  , numbers ( n ) AS (            -- рекурсивный
  9       SELECT n FROM anchor1234
 10          UNION ALL
 11       SELECT n + 1 AS n
 12       FROM   numbers
 13       WHERE  n < 5
 14    )
 15  SELECT n FROM numbers
 16  ;
         N
----------
         1  ← опорный запрос
         2  ← опорный запрос
         3  ← опорный запрос
         4  ← опорный запрос
         2  ← рекурсия 1
         3  ← рекурсия 1
         4  ← рекурсия 1
         5  ← рекурсия 1
         3  ← рекурсия 2
         4  ← рекурсия 2
         5  ← рекурсия 2
         4  ← рекурсия 3
         5  ← рекурсия 3
         5  ← рекурсия 4

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

WITH     period ( year ) AS (
            SELECT 1980 AS year FROM dual
               UNION ALL
            SELECT year + 1 AS year
            FROM   period
            WHERE  year < 1990
         )
SELECT   p.year, COUNT ( e.empno )
FROM     emp e RIGHT OUTER JOIN period p
         ON p.year = EXTRACT ( YEAR FROM e.hiredate )
GROUP BY p.year
ORDER BY p.year
;

Запрос в приведенной формулировке самодостаточен. Желание его параметризировать, если оно возникнет, осуществимо с помощью "контекста сеанса" Oracle, но уже потребует соблюдения определенной технологии программирования при обращении к запросу.

Использование предыдущих значений при рекурсивном вычислении

Рекурсивные запросы с фразой WITH позволяют программисту больше, нежели запросы с CONNECT BY (тоже рекурсивные). Например, они позволяют накапливать изменения и не испытывают необходимости в функциях LEVEL или SYS_CONNECT_BY_PATH, имея возможность легко их моделировать.

Пример запроса по маршрутам из Москвы с подсчетом километража:

WITH stepbystep ( node, route, distance ) AS (
  SELECT node, parent || '-' || node, distance 
  FROM   way 
  WHERE  parent = 'Москва'
     UNION ALL
  SELECT w.node
       , s.route || '-' || w.node
       , w.distance + s.distance
  FROM way w
       INNER JOIN
       stepbystep s
       ON ( s.node = w.parent )
  )
SELECT route, distance FROM stepbystep
/

Ответ:

ROUTE                                      DISTANCE
---------------------------------------- ----------
Москва-Ленинград                                696
Москва-Новгород                                 538
Москва-Новгород-Ленинград                       717
Москва-Ленинград-Выборг                         831
Москва-Новгород-Ленинград-Выборг                852

Запрос по маршрутам из Выборга аналогичен, но с поправкой на симметрию, вызванной движением по иерархии снизу вверх, а не сверху вниз:

WITH stepbystep ( parent, route, distance ) AS (
  SELECT parent, node || '-' || parent, distance 
  FROM   way 
  WHERE  node = 'Выборг'
     UNION ALL
  SELECT w.parent
       , s.route || '-' || w.parent
       , w.distance + s.distance
  FROM   way w
         INNER JOIN
         stepbystep s
         ON ( s.parent = w.node )
  )
SELECT route, distance FROM stepbystep
/

Ответ:

ROUTE                                      DISTANCE
---------------------------------------- ----------
Выборг-Ленинград                                135
Выборг-Ленинград-Москва                         831
Выборг-Ленинград-Новгород                       314
Выборг-Ленинград-Новгород-Москва                852

Обработка зациклености данных

Пример организации зациклености в сведениях о маршрутах:

INSERT INTO way VALUES ( 'Новгород', 'Выборг', 135 );

Реакция на появление цикла (уже получается не иерархия) в этом случае отлична от имевшейся для CONNECT BY и будет

ERROR:
ORA-32044: cycle detected while executing recursive WITH query

Упражнение. Проверьте это самостоятельно.

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

WITH stepbystep ( node, route, distance ) AS (
  SELECT node, parent || '-' || node, distance
  FROM   way
  WHERE  parent = 'Москва'
     UNION ALL
  SELECT w.node
       , s.route || '-' || w.node
       , w.distance + s.distance
  FROM way w
       INNER JOIN
       stepbystep s
       ON ( s.node = w.parent )
  )
  CYCLE node SET cyclemark TO 'X' DEFAULT '-'
SELECT route, distance, cyclemark FROM stepbystep
/

Ответ:

ROUTE                                        DISTANCE C
------------------------------------------ ---------- -
Москва-Ленинград                                  696 -
Москва-Новгород                                   538 -
Москва-Новгород-Ленинград                         717 -
Москва-Ленинград-Выборг                           831 -
Москва-Новгород-Ленинград-Выборг                  852 -
Москва-Ленинград-Выборг-Новгород                  966 -
Москва-Ленинград-Выборг-Новгород-Ленинград       1145 X
Москва-Новгород-Ленинград-Выборг-Новгород         987 X

Упорядочение результата

Для придания порядка строкам результата в запросах с CONNECT BY используется собственная конструкция ORDER BY SIBLINGS. Аналогичным образом в вынесенном рекурсивном запросе применяется особое указание SEARCH. В его рамках программистом задается в том числе вымышленное имя столбца, в котором СУБД автоматически проставит числовые значения и который самостоятельно включит в порождаемый набор столбцов. На этот столбец программист может сослаться далее уже в обычной фразе ORDER BY для создания нужного порядка строк.

Пример:

ROLLBACK;
WITH stepbystep ( node, route, distance ) AS (
  SELECT node, parent || '-' || node, distance
  FROM   way
  WHERE  parent = 'Москва'
     UNION ALL
  SELECT w.node
       , s.route || '-' || w.node
       , w.distance + s.distance
  FROM way w
       INNER JOIN
       stepbystep s
       ON ( s.node = w.parent )
  )
  SEARCH DEPTH FIRST BY node DESC SET orderval
SELECT   route, distance, orderval 
FROM     stepbystep
ORDER BY orderval DESC
/

Ответ:

ROUTE                                      DISTANCE   ORDERVAL
---------------------------------------- ---------- ----------
Москва-Ленинград-Выборг                         831          5
Москва-Ленинград                                696          4
Москва-Новгород-Ленинград-Выборг                852          3
Москва-Новгород-Ленинград                       717          2
Москва-Новгород                                 538          1

Подробности и прочие свойства построений указания SEARCH приведены в документации по Oracle.

Замечание об общей формулировке запроса

Общая формулировка рекурсивного запроса в стандарте SQL и в Oracle способна вызвать у некоторых программистов недоумение, однако она имеет свое вероятное обоснование. Ранее упоминалось о возможности описания реляционной БД средствами логики предикатов. В таком случае база представляет собой набор истинных утверждений. Пусть есть "предикатный символ" (predicate symbol, то есть "обозначение утверждения") way ( x, y ) как общее обозначение однотипных утверждений (километраж и вероятные другие свойства здесь для простоты опущены как несущественные). В БД представлено несколько конкретных соответствующих ему истинных утверждений, например:

way ( 'Ленинград ', 'Выборг ' )
way ( 'Новгород ', 'Ленинград ' )
...

То есть "имеется путь от Ленинграда до Выборга", "от Новгорода до Ленинграда" и так далее. Это так называемое "существовательное" (intensional) определение БД, явно перечисляющее объекты с их свойствами. Дополнительно можно ввести еще один предикатный символ route (x, y) со смыслом "маршрут". Допустим, что утверждения для него представлены не "существовательно", а "расширительно" (extensionally), в виде двух правил вывода:

route ( x, y ) → way ( x, y )
route ( x, y ) → route ( x, z ), way ( z, y ) 

Это позволяет получать из БД сведения (о "маршрутах"), напрямую в ней не представленные, и БД становится "расширительной". Так, она оказалась дополнена группой новых утверждений вида route ( x, y ).

Теперь если обозначить way ( x, y ) как W, route ( x, y ) как R, второе (рекурсивное) правило вывода как $$ullet$$, то с позиций уже реляционной алгебры для новых сведений из БД для определения маршрутов можно предложить формулировку $$ R = W \cup R ullet W^*$$ Идея получения подобной формулы упоминается в книге Марков А. С., Лисовский К. Ю. Базы данных: Введение в теорию и методологию. // М.: Финансы и Статистика, 2006, интересной разработчику и программисту БД во многих других отношениях.. Она удивительно напоминает общее построение рекурсивного запроса в SQL, где однако пошли дальше и обобщили операцию $$\cup$$ объединения множеств на упомянутую группу. Если обобщенную множественную операцию указать как $$\circ$$, формула будет выглядеть как $$R = W \circ R ullet W$$.

Получается, что рекурсивная формулировка запроса в SQL придает вообще-то "существовательной" базе, предполагаемой этим языком, некоторые качества "расширительной", где возможно получение новых "знаний" из имеющихся. К сожалению, на практике такое достижение нельзя подкрепить созданием представления данных (view) на основе рекурсивного запроса ввиду имеющегося в настоящее время в Oracle запрета на подобное действие.

Оборотной стороной помимо риска зацикленности (для избежания которого Oracle дает упоминавшееся частичное решение) является пониженная производительность вычисления. Для повышения производительности Oracle тоже предлагает определенную гамму решений (организация materialized view и прочее), но все они носят неполный характер и обременены собственными издержками.