title: Rownum in the join condition
body: <p>Recently I fixed the some bug: there was rownum in the join condition.</p>
  
  <p>Something like this: left join  t1 on t1.id=t2.id and rownum&lt;2. So it was supposed to return only one row regardless of the “left join”.</p>
  
  <p>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.</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>Then we have this query :</p>
  
  <pre><code>select * from master t
  left join detail d on d.ref_master_id=t.id
  </code></pre>
  
  <p>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.</p>
  
  <p><a href="http://i.stack.imgur.com/1ovjW.png" rel="noreferrer">Result Set</a></p>
  
  <p>Then I added “rownum=1” to the join condition and the result was the same</p>
  
  <pre><code>select * from master t
  left join detail d on d.ref_master_id=t.id and rownum=1
  </code></pre>
  
  <p>The most interesting thing is that I set “rownum&lt;-666” and got the same result again! </p>
  
  <pre><code>select * from master t
  left join detail d on d.ref_master_id=t.id and rownum&lt;-666.
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>select * from master t
  join detail d on d.ref_master_id=t.id and rownum&lt;-666.
  </code></pre>
  
  <p>This query doesn’t return any row,because I can't imagine rownum to be less then -666 :-)</p>
  
  <p>Moreover, if I use oracle syntax for outer join, using “(+)” everything goes well too.</p>
  
  <pre><code>select * from master m ,detail t
   where m.id=t.ref_master_id(+) and rownum&lt;-666.
  </code></pre>
  
  <p>This query doesn’t return any row too.</p>
  
  <p>Can anyone tell me, what I misunderstand with outer join and rownum?</p>