Inner Join and Left Outer Join not working correctly together
819547Nov 29 2010 — edited Nov 30 2010I 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!!