LEFT OUTER JOIN с XMLTABLE не работает?

Question

Вот пример запроса:

WITH empdata AS (SELECT xmltype ('<office>
                            <emp>
                                <empno>1</empno>
                                <ename>Abraham</ename>
                                <deptno>10</deptno>
                            </emp>
                            <emp>
                                <empno>2</empno>
                                <ename>Alexander</ename>
                                <deptno>10</deptno>
                            </emp>
                            <emp>
                                <empno>3</empno>
                                <ename>Benjamin</ename>
                                <deptno>20</deptno>
                            </emp>
                            <emp>
                                <empno>4</empno>
                                <ename>Bradley</ename>
                                <deptno>20</deptno>
                            </emp>
                         </office>') AS xcol FROM dual),
     dept AS
         (SELECT 10 deptno, 'Accounting' dname FROM dual
          UNION ALL
          SELECT 20, 'Broking' FROM dual
          UNION ALL
          SELECT 30, 'HR' FROM dual)
SELECT d.dname, e.ename, e.empno
  FROM dept d
       CROSS JOIN empdata e_data
       LEFT OUTER JOIN
       xmltable (
           'office/emp'
           PASSING e_data.xcol
           COLUMNS deptno NUMBER (28, 0) PATH 'deptno',
                   ename VARCHAR2 (10) PATH 'ename',
                   empno NUMBER (28, 0) PATH 'empno') e
           ON d.deptno = e.deptno;

Результат я получаю

DNAME      ENAME           EMPNO
---------- ---------- ----------
Accounting Abraham             1
Accounting Alexander           2
Broking    Benjamin            3
Broking    Bradley             4

Почему не третий ряд из deptто есть что HR не отображается в наборе результатов? В идеале в соответствии с правилами ЛЕВОГО СОЕДИНЕНИЯ должны отображаться все записи из таблицы слева. Почему это фильтруется?

Answer 1

У вас есть внешнее соединение, и перекрестное соединение неверно. Вам нужно кросс-присоединиться empdata в xmltable как подзапрос, и используйте этот подзапрос (встроенный просмотр) в качестве цели для внешнего соединения:

...
SELECT d.dname, e.ename, e.empno
  FROM dept d
       LEFT OUTER JOIN
       (
         SELECT x.*
          FROM empdata e_data
               CROSS JOIN 
               xmltable (
                   'office/emp'
                   PASSING e_data.xcol
                   COLUMNS deptno NUMBER (28, 0) PATH 'deptno',
                           ename VARCHAR2 (10) PATH 'ename',
                           empno NUMBER (28, 0) PATH 'empno') x
        ) e
           ON d.deptno = e.deptno;

DNAME      ENAME           EMPNO
---------- ---------- ----------
Accounting Abraham             1
Accounting Alexander           2
Broking    Benjamin            3
Broking    Bradley             4
HR