Rownum in the join condition

Question

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?

Answer 1

ROWNUM is a pseudo-attribute of result sets, not of base tables. ROWNUM is defined after rows are selected, but before they're sorted by an ORDER BY clause.

edit: I was mistaken in my previous writeup of ROWNUM, so here's new information:

You can use ROWNUM in a limited way in the WHERE clause, for testing if it's less than a positive integer only. See ROWNUM Pseudocolumn for more details.

SELECT ... WHERE ROWNUM < 10

It's not clear what value ROWNUM has in the context of a JOIN clause, so the results may be undefined. There seems to be some special-case handling of expressions with ROWNUM, for instance WHERE ROWNUM > 10 always returns false. I don't know how ROWNUM<-666 works in your JOIN clause, but it's not meaningful so I would not recommend using it.

In any case, this doesn't help you to fetch the first detail row for each given master row.

To solve this you can use analytic functions and PARTITION, and combine it with Common Table Expressions so you can access the row-number column in a further WHERE condition.

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;