Rownum处于连接状态(Rownum in the join condition)

 最近我修复了一些错误：在连接条件下有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
 
 结果集是可预测的：我们拥有主表中的所有行以及匹配此条件的详细表中的3行d.ref_master_id = t.id。 
 
 结果集 
 
 然后我将“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误解了什么？ 

Recently I fixed the some bug: there was rownum in the join condition.
 
Something like this: left join t1 on t1.id=t2.id and rownum<2. So it was supposed to return only one row regardless of the “left join”.
 
When I looked further into this, I realized that I don’t understand how Oracle evaluates rownum in the "left join" condition. Let’s create two sampe tables: master and 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.
 
Then we have this query :
 
select * from master t
left join detail d on d.ref_master_id=t.id
 
The result set is predictable: we have all the rows from the master table and 3 rows from the detail table that matched this condition d.ref_master_id=t.id.
 
Result Set
 
Then I added “rownum=1” to the join condition and the result was the same
 
select * from master t
left join detail d on d.ref_master_id=t.id and rownum=1
 
The most interesting thing is that I set “rownum<-666” and got the same result again! 
 
select * from master t
left join detail d on d.ref_master_id=t.id and rownum<-666.
 
Due to the result set we can say that this condition was evaluated as “True” for 3 rows in the detail table. But if I use “inner join” everything goes as supposed to be.
 
select * from master t
join detail d on d.ref_master_id=t.id and rownum<-666.
 
This query doesn’t return any row,because I can't imagine rownum to be less then -666 :-)
 
Moreover, if I use oracle syntax for outer join, using “(+)” everything goes well too.
 
select * from master m ,detail t
 where m.id=t.ref_master_id(+) and rownum<-666.
 
This query doesn’t return any row too.
 
Can anyone tell me, what I misunderstand with outer join and rownum?

原文：https://stackoverflow.com/questions/6603968