左外连接与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 的第三行没有显示在结果集中？理想情况下，根据 LEFT JOIN 的规则，左侧表格中的所有记录都应显示。为什么那个被过滤掉了？

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