Skn Skn - 5 months ago 20
SQL Question

nvl , Where, IN clauses

Is this correct?

select * from t1
where nvl(t1.a,t1.b) in (select id from t2 where name = 'Apple')


a,b are the columns in t1 for the Id from t2.

I want all the rows that have id for 'Apple' either in t1.a or t1.b

if a is null, id will be in b.

if b is null, id will be in a.

MT0 MT0
Answer

I want all the rows that have id for 'Apple' either in t1.a or t1.b

No, it is not correct.

Your query will get the rows where t1.a is an id in t2 or where t1.a is null and t1.b is an id in t2.

You want:

select *
from   t1
where  EXISTS ( select 1
                from   t2
                where  name = 'Apple'
                and    ( t1.a = t2.id OR t1.b = t2.id ) )

or:

SELECT *
FROM   t1
WHERE  a IN ( SELECT id FROM t2 WHERE name = 'Apple' )
OR     b IN ( SELECT id FROM t2 WHERE name = 'Apple' )

or (if you need to enforce the NULL in the non-matching value):

select *
from   t1
where  EXISTS ( select 1
                from   t2
                where  name = 'Apple'
                and    (  ( t1.a = t2.id AND t1.b IS NULL )
                       OR ( t1.b = t2.id AND t1.a IS NULL ) ) )