oracle - 连接条件中的 Rownum

Question

最近我修复了一些错误:连接条件中有rownum。

像这样:在 t1.id=t2.id 和 rownum<2 上左连接 t1。因此，无论“左连接”如何，它都应该只返回一行。

当我进一步研究这个时，我意识到我不明白 Oracle 如何在“左连接”条件下评估 rownum。
让我们创建两个样本表:master 和 detail。

create table MASTER
(
  ID   NUMBER not null,
  NAME VARCHAR2(100)
)
;
alter table MASTER
  add constraint PK_MASTER primary key (ID);

prompt Creating DETAIL...
create table DETAIL
(
  ID            NUMBER not null,
  REF_MASTER_ID NUMBER,
  NAME          VARCHAR2(100)
)
;
alter table DETAIL
  add constraint PK_DETAIL primary key (ID);
alter table DETAIL
  add constraint FK_DETAIL_MASTER foreign key (REF_MASTER_ID)
  references MASTER (ID);

prompt Disabling foreign key constraints for DETAIL...
alter table DETAIL disable constraint FK_DETAIL_MASTER;
prompt Loading MASTER...
insert into MASTER (ID, NAME)
values (1, 'First');
insert into MASTER (ID, NAME)
values (2, 'Second');
commit;
prompt 2 records loaded
prompt Loading DETAIL...
insert into DETAIL (ID, REF_MASTER_ID, NAME)
values (1, 1, 'REF_FIRST1');
insert into DETAIL (ID, REF_MASTER_ID, NAME)
values (2, 1, 'REF_FIRST2');
insert into DETAIL (ID, REF_MASTER_ID, NAME)
values (3, 1, 'REF_FIRST3');
commit;
prompt 3 records loaded
prompt Enabling foreign key constraints for DETAIL...
alter table DETAIL enable constraint FK_DETAIL_MASTER;
set feedback on
set define on
prompt Done.

然后我们有这个查询:

select * from master t
left join detail d on d.ref_master_id=t.id

结果集是可预测的:我们有来自主表的所有行和来自详细表的 3 行与此条件 d.ref_master_id=t.id 匹配。

Result Set

然后我在连接条件中添加了“rownum=1”，结果是一样的

select * from master t
left join detail d on d.ref_master_id=t.id and rownum=1

最有趣的是，我设置了“rownum<-666”，结果又是一样的!

select * from master t
left join detail d on d.ref_master_id=t.id and rownum<-666.

由于结果集，我们可以说对于详细信息表中的 3 行，此条件被评估为“真”。但是，如果我使用“内部连接”，一切都会按预期进行。

select * from master t
join detail d on d.ref_master_id=t.id and rownum<-666.

此查询不返回任何行，因为我无法想象 rownum 小于 -666 :-)

此外，如果我使用 oracle 语法进行外连接，使用“(+)”也一切顺利。

select * from master m ,detail t
 where m.id=t.ref_master_id(+) and rownum<-666.

此查询也不会返回任何行。

谁能告诉我，我对外连接和 rownum 的误解是什么？

Answer 1

ROWNUM 是结果集的伪属性，而不是基表的伪属性。 ROWNUM 是在选择行之后定义的，但在它们由 ORDER BY 子句排序之前定义。

编辑:我之前写的 ROWNUM 写错了，所以这里有新信息:

您可以在 WHERE 子句中以有限的方式使用 ROWNUM，仅用于测试它是否小于正整数。有关更多详细信息，请参阅 ROWNUM Pseudocolumn。

SELECT ... WHERE ROWNUM < 10

不清楚 ROWNUM 在 JOIN 子句的上下文中具有什么值，因此结果可能未定义。似乎有一些特殊情况处理 ROWNUM 的表达式，例如 WHERE ROWNUM > 10 总是返回 false。我不知道 ROWNUM<-666 在你的 JOIN 子句中是如何工作的，但它没有意义，所以我不建议使用它。

在任何情况下，这都无助于获取每个给定主行的第一个详细信息行。

要解决此问题，您可以使用 analytic functions and PARTITION ，并将其与 Common Table Expressions 结合使用，以便您可以在进一步的 WHERE 条件中访问行号列。

WITH numbered_cte AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY d.something) AS rn
  FROM master t LEFT OUTER JOIN detail d ON d.ref_master_id = t.id
) 
  SELECT *
  FROM numbered_cte
  WHERE rn = 1;