CODEWITHSUNDEEP
sqloracle-database
tulu matinee
tulu matinee

Reputation: 61

How to replace IN clause with JOIN clause in Oracle

I have written SQL with IN clause but I have tried writing the same with JOIN but with no luck.

select MSG.ID
from AOUT MSG, AOUTMSG OUTM
where OUTM.ID = MSG.ID
and MSG.ID  in (
  select ID
  from AOUTID MSGID1
  where MSGID1.ID = MSG.ID
  and trim(MSGID1.TYPEFLG) = 'DEMO'

  )
and MSG.ID in (
  select ID
  from AOUTID  MSGID2
  where MSGID2.ID = MSG.ID
  and trim(MSGID2.MKT_ID_TYPE_FLG) = 'KEEP'

  )

Upvotes: 0

Views: 63

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You don't need a join. Just use aggregation and having:

select aout.id
from aout
group by aout.id
having sum(case when trim(aout.TYPEFLG) = 'DEMO' then 1 else 0 end) > 0 and
       sum(case when trim(MSGID3.MKT_ID_TYPE_FLG) = 'KEEP' then 1 else 0 end) > 0;

Upvotes: 1

Related Questions