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!

Inner Join and Left Outer Join not working correctly together

819547Nov 29 2010 — edited Nov 30 2010
I have two tables which have the following structure:
create table t1(
id integer,
country_code varchar2(2)
);

create table t2(
id integer,
country_code varchar2(2)
);

I am putting in some sample data which we have in the two tables
insert into t1(id,country_code) values(1,'US');
insert into t1(id,country_code) values(1,'UK');
insert into t2(id,country_code) values(1,'US');

Now my objective is to get those records from table 't1' which have id values existing in table 't2' but whose corresponding country_code is not existing in 't2', which means that from the sample data we should get the following record returned from 't1':
Record which should be returned from 't1' : 1,'UK'

I was previously using this SQL to achieve this:
select t1.id,t1.country_code
from t1, t2
where t1.id = t2.id
and (t1.country_code = t2.country_code(+) and t2.country_code is null)
This SQL was working fine for some time but now it is not returning the desired output. When I now modified my SQL to the following it is working fine now:
select t1.id,t1.country_code
from t1 inner join t2
on t1.id = t2.id
left outer join t2 t3
on t1.country_code = t3.country_code
where t3.country_code is null

Can you please tell me how the first SQL is wrong and if wrong then why it was giving correct output for some time. Is it because the behavior of the first SQL is not consistent and it can give some time the correct output.

Thanks a lot in Advance!!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 28 2010
Added on Nov 29 2010
2 comments
491 views