连接条件中的 Rownum

Question

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

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

当我进一步研究这个问题时，我意识到我不明白Oracle如何在“左连接”条件下计算rownum。 让我们创建两个样本表：主表和详细表。

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

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

结果集

然后我在连接条件中添加了“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 行，此条件被评估为“True”。但如果我使用“内部联接”，一切都会按预期进行。

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 伪列。

SELECT ... WHERE ROWNUM < 10

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

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

要解决这个问题，您可以使用分析函数和PARTITION，并将其组合起来与公用表表达式，这样您就可以在进一步的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;

Answer 2

ROWNUM 过滤器在联接中没有任何意义，但不会因无效而被拒绝。

解释计划将包含 ROWNUM 过滤器或排除它。如果包含它，它将在应用其他连接条件后将过滤器应用于详细表。因此，如果您输入 ROWNUM=100（永远不会满足），则所有详细信息行都会被排除，然后外连接就会启动。

如果您输入 ROWNUM=1，它似乎会删除过滤器。

如果你询问

with 
 a as (select rownum a_val from dual connect by level < 10),
 b as (select rownum*2 b_val from dual connect by level < 10)
select * from a left join b on a_val < b_val and rownum in (1,3);

你会得到一些完全奇怪的东西。

它可能应该被视为错误而被拒绝，所以预计会发生无意义的事情

Answer 3

如果您想从连接条件中获取前三个值，请像这样更改 select 语句。

    select * 
    from (select * 
          from master t left join detail d on d.ref_master_id=t.id)
    where rownum<3;

您将获得所需的输出。使用 * 时请注意明确定义的列名称

让我给出一个绝对的答案，您可以直接运行而无需对代码进行任何更改。

    select * 
    from (select t.id,t.name,d.id,d.ref_master_id,d.name 
          from master t left join detail d on d.ref_master_id=t.id)
    where rownum<3;