Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Isn't this strange? ("rownum" and "left join")

JackKOct 27 2011 — edited Oct 28 2011
Hi!
create table t1 (
  n number,
  v varchar2(2)
);

create table t2 (
  n number,
  v varchar2(2)
);

insert into t1 values (1, 'a1');
insert into t2 values (1, 'a1');
insert into t2 values (1, 'a2');
insert into t2 values (1, 'a3');
insert into t2 values (2, 'a1');
insert into t2 values (2, 'a2');
insert into t2 values (3, 'a1');
commit;
select * from t1      join t2 on t1.n=t2.n and rownum=1;

N V
- --
1 a1
select * from t1 LEFT join t2 on t1.n=t2.n and rownum=1;

N V
- --
1 a1
1 a1
1 a1
Why the second query returns 3 rows? ROWNUM seems to be ignored.
Explain me that, please. I'm now aware of some "inside things", probably.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 25 2011
Added on Oct 27 2011
12 comments
5,325 views