I'm having scenario to which a sql query need to be built. I tried to come up with a efficient query, but could not find a clear way of doing this. My scenario is as follows:
I'm having TABLE_A and TABLE_B ,where FIELD_AB will definitely be a filed of TABLE_A, however, there can be exist FIELD_AB in TABLE_B.
I need to retrieve value for FIELD_AB, from TABLE_B if such field exist, if it is not, then retrieve value for FIELD_AB from TABLE_A.
I'm looking for a single query to retrieve the value of FIELD_AB, and according to my knowledge CASE statement can be used to accomplish this, but not clear a better way of using it.
EDIT:
Please do not misunderstood question. What I mean by "FIELD_AB can be exist" is that there is a possibility of FIELD_AB开发者_StackOverflow中文版 itself does not exist in the TABLE_B, not a value for FIELD_AB Any help appreciated
Thank You
You probably need to use an outer join to link the two tables:
select a.id
, case when b.col_ab is null then a.col_ab
else b.col_ab end as ab
from table_b b
left outer join table_a a
on ( b.id = a.id )
/
Oracle has some alternative ways of testing for NULL. A simpler, if non-standard, way of testing for AB would be:
nvl2(b.col_ab, b.col_ab, a.col_ab) as ab
This is logically identical to the more verbose CASE() statement.
create table table_b ( field_ab int not null, value varchar(20) not null )
create table table_a ( field_ab int not null, value varchar(20) not null )
insert into table_a values( 1, '1 from a')
insert into table_a values( 2, '2 from a')
insert into table_a values( 3, '3 from a')
insert into table_b values( 2, '2 from b')
-- result is '2 from b'
select
case when b.field_ab is null then a.value
else b.value
end
from table_a a left outer join table_b b on a.field_ab = b.field_ab
where a.field_ab = 2
-- result is '1 from a'
select
case when b.field_ab is null then a.value
else b.value
end
from table_a a left outer join table_b b on a.field_ab = b.field_ab
where a.field_ab = 1
精彩评论