Решение в случае использования скалярного подзапроса эквивалентно решению с рефлексивным объединением, кроме одной строки: в результирующем множестве присутствует строка служащего KING, тогда как в результате рефлексивного объединения ее нет. Вы спросите: Почему? Вспомните, значение NULL ничему не равно, даже самому себе. В решении с рефлексивным объединением мы проводим эквиобъ-единение между EMPNO и MGR, в результате чего отсеиваются все служащие, имеющие значение NULL в поле MGR. Чтобы увидеть строку служащего KING при использовании метода с рефлексивным объединением, необходимо выполнить внешнее объединение, как показано в следующих двух запросах. В первом решении используется ANSI-синтаксис внешнего объединения, тогда как второе решение является примером синтаксиса Oracle. Оба запроса возвращают один и тот же результат, показанный после второго запроса:

/* ANSI */

select a.ename, b.ename mgr from emp a left join emp b on (a.mgr = b.empno)

/* Oracle */

select a.ename, b.ename mgr from emp a, emp b where a.mgr = b.empno (+)

ENAME MGR

FORD JONES SCOTT JONES

select a.ename,

(select b.ename from emp b

where b.empno = a.mgr) as mgr from emp a

Представление отношений потомок-родитель-прародитель

Задача

Служащий CLARK является подчиненным служащего KING. Представить это отношение можно, используя первый рецепт данной главы. А что если бы служащий CLARK, в свою очередь, был руководителем другого служащего? Рассмотрим следующий запрос:

select ename,empno,mgr from emp

where ename in (KING,CLARK,MILLER)

ENAME EMPNO MGR

CLARK 7782 7839

KING 7839

MILLER 7934 7782

Как видите, служащий MILLER является подчиненным служащего CLARK, который, в свою очередь, подчиняется KING. Требуется показать всю иерархию от MILLER до KING. Должно быть получено следующее результирующее множество:

LEAF BRANCH ROOT

MILLER-->CLARK-->KING

Чтобы показать эти отношения полностью, сверху вниз, одного рефлексивного объединения недостаточно. Можно было бы написать запрос с двумя рефлексивными объединениями, но на самом деле необходим общий подход для представления таких иерархий.

Решение

Этот рецепт отличается от первого тем, что здесь рассматриваются трехуровневые отношения, как предлагает заголовок. Если используемая СУБД не предоставляет функциональности для обхода данных

Чтобы использовать это решение для DB2, в нем всего лишь надо заменить оператор конкатенации на соответствующий оператор DB2, . Но в любом случае, даже без замены оператора, решение будет работать как для DB2, так и для SQL Server.

Oracle

С помощью функции SYS CONNECT BY PATH получите MILLER, его руководителя, CLARK, затем руководителя CLARK, KING. Для обхода дерева используйте оператор CONNECT BY:

1 select ltrim(

2 sys connect by path(ename,-->),

3 -->) leaf branch root

4 from emp

5 where level = 3

6 start with ename = MILLER

7 connect by prior mgr = empno

PostgreSQL и MySQL

Чтобы возвратить служащего MILLER, его руководителя, CLARK, затем руководителя CLARK, KING, дважды выполните рефлексивное

с древовидной структурой, можно решить эту задачу, используя технику из рецепта Создание иерархического представления таблицы с введением дополнительного рефлексивного объединения. DB2, SQL Server и Oracle предлагают функции для работы с иерархиями. Таким образом, рефлексивные объединения в решениях для этих СУБД не нужны, хотя, безусловно, возможны.

DB2 и SQL Server

С помощью рекурсивного оператора WITH найдите руководителя служащего MILLER, CLARK, затем руководителя служащего CLARK, KING. В этом решении используется оператор конкатенации строк SQL Server +: