sql - 与 `CROSS APPLY` 和 `OUTER APPLY` 不一致的行为

Question

我在 Oracle-12c 中有一个类似于典型论坛的架构 accounts , posts , comments .我正在编写一个查询来获取...

一位用户

该用户的所有帖子

对每个帖子的评论

以及每条评论的作者。

查询如下所示:

select "accounts".*, "p".*, "c".*, "author".*
from "accounts"
cross apply (
    select * from "posts"
    where "posts"."author_id" = "accounts"."id"
) "p"
cross apply (
    select * from "comments"
    where "comments"."post_id" = "p"."id"
) "c"
left join "accounts" "author" on "author"."id" = "c"."author_id"
where "accounts"."id" = 1

此查询按预期工作。 我正在使用 CROSS APPLY而不是典型的 JOIN，因为我将添加 OFFSET和 FETCH稍后分页。然而，问题在于 CROSS APPLY省略没有评论的帖子，这是我不想要的。我要 保留 结果中的帖子，即使他们没有评论。

所以我尝试改变 CROSS APPLY至 OUTER APPLY .

select "accounts".*, "p".*, "c".*, "author".*
from "accounts"
outer apply (
    select * from "posts"
    where "posts"."author_id" = "accounts"."id"
) "p"
outer apply (
    select * from "comments"
    where "comments"."post_id" = "p"."id"
) "c"
left join "accounts" "author" on "author"."id" = "c"."author_id"
where "accounts"."id" = 1

但现在我收到这个错误:

ORA-00904: "p"."id": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 9 Column: 34

出于某种原因，我的第二个 OUTER APPLY加入提示我引用 "p"."id"从第一个结果来看。但是当我使用 CROSS APPLY 时还好.

到底是怎么回事？为什么这些行为之间存在这种差异？

编辑: OUTER APPLY在这个基本示例中似乎没有必要。这是从一个更复杂的场景中提炼出来的，在这个场景中我必须坚持 OUTER APPLY确实是必要的，但该场景的细节与我要问的实际问题无关——这是关于 CROSS 之间这种看似没有记录的行为差异的问题。和 OUTER APPLY .

编辑:

Oracle 版本: Database 12c Standard Edition Release 12.1.0.2.0 - 64 位生产

客户: Oracle SQL Developer 版本 4.2.0.16.356

服务器: uname -a 的输出- Linux ubuntu-1gb-sfo2-01 4.4.0-64-generic #85-Ubuntu SMP Mon Feb 20 11:50:30 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
DDL: link

Answer 1

致 CodeFuller's回答，我只想补充一点，(A) 有一个补丁可用于此错误，(B) 有一个适用于 12.1.0.2 的变通 SQL，但我不知道它是否适合您的目的。

解决方法是基本上嵌套您的连接，如下所示:

SELECT accounts.*,
       p.*,
       author.*
FROM   accounts
       CROSS APPLY (SELECT posts.id,
                           posts.author_id,
                           posts.text,
                           comments.comment_author_id,
                           comments.comment_text
                    FROM   posts
                           OUTER APPLY (SELECT comments.author_id comment_author_id,
                                               comments.text comment_text
                                        FROM   comments
                                        WHERE  comments.post_id = posts.id) comments
                    WHERE  posts.author_id = accounts.id) p
       LEFT JOIN accounts author
         ON author.id = p.comment_author_id
WHERE  accounts.id = 1;


ID   NAME      ID_1 AUTHOR_ID TEXT                                              COMMENT_AUTHOR_ID COMMENT_TEXT                            ID_2  NAME_1                               
---- --------- ---- --------- ------------------------------------------------- ----------------- --------------------------------------- ----- ------------------- 
   1 Fred         1         1 Fred wrote this and it has comments                               3 This is Helen's comment on Fred's post      3 Helen                                
   1 Fred         2         1 Fred wrote this and it does not have any comments
-------- End of Data --------
2 row(s) fetched

引用:用于解决方法的表 DDL

CREATE TABLE accounts
(
  id     NUMBER PRIMARY KEY,
  name   VARCHAR2 (30)
);

CREATE TABLE posts
(
  id          NUMBER PRIMARY KEY,
  author_id   NUMBER,
  text        VARCHAR2 (240)
);

CREATE TABLE comments
(
  id          NUMBER PRIMARY KEY,
  post_id     NUMBER,
  author_id   NUMBER,
  text        VARCHAR2 (240)
);

INSERT INTO accounts (id, name)
VALUES (1, 'Fred');

INSERT INTO accounts (id, name)
VALUES (2, 'Mary');

INSERT INTO accounts (id, name)
VALUES (3, 'Helen');

INSERT INTO accounts (id, name)
VALUES (4, 'Iqbal');

INSERT INTO posts (id, author_id, text)
VALUES (1, 1, 'Fred wrote this and it has comments');

INSERT INTO posts (id, author_id, text)
VALUES (2, 1, 'Fred wrote this and it does not have any comments');

INSERT INTO posts (id, author_id, text)
VALUES (3, 4, 'Iqbal wrote this and it does not have any comments');

INSERT INTO comments (id,
                      post_id,
                      author_id,
                      text)
VALUES (1,
        1,
        3,
        'This is Helen''s comment on Fred''s post');