Alternative to sql NOT IN?

Question

Hi,

I am trying to make a materialized view in Oracle (I am a newbie, btw). For some reason, it doesn't like the presence of sub-query in it. I've been trying to use LEFT OUTER JOIN instead, but it's returning different data set now.

Put simply, here's the code I'm trying to modify:

SELECT *
FROM   table1 ros, table2 bal, table3 flx
WHERE  flx.name = 'XXX'
       AND flx.value = bal.value
       AND NVL (ros.ret, 'D') = Nvl (flx.attr16, 'D')
       AND ros.value = bal.segment3
       AND ros.type IN ( 'AL', 'AS', 'PL' )
       AND bal.period = 13
       AND bal.code NOT IN (SELECT bal1.code
                            FROM   table2 bal1
                            WHERE  bal1.value = flx.value
                                   AND bal1.segment3 = ros.value
                                   AND bal1.flag = bal.flag
                                   AND bal1.period = 12
                                   AND bal1.year = bal.year)

And here's one of my attempt:

SELECT  *      
FROM   table1 ros, table2 bal, table3 flx
       LEFT OUTER JOIN table2 bal1
            ON bal.code = bal1.code      
WHERE  bal1.code is null
       AND bal1.segment3 = ros.value
       AND bal.segment3 = ros.value
       AND bal1.flag = bal.flag
       AND bal1.year = bal.year
       AND flx.name = 'XXX'
       AND flx.value = bal.value
       AND bal1.value = flx.value
       AND bal1.period_num = 12
       AND NVL (ros.type, 'D') = NVL (flx.attr16, 'D')
       AND ros.value = bal.segment3
       AND ros.type IN ( 'AL', 'AS', 'PL' )
       AND bal.period = 13;

This drives me nuts! Thanks in advance for the help :)

Answer 1

Try NOT EXISTS insted of NOT IN:

SELECT 
  *
FROM   
  table1 ros
  INNER JOIN table2 bal ON ros.value = bal.segment3
  INNER JOIN table3 flx ON flx.value = bal.value AND NVL(ros.ret, 'D') = Nvl(flx.attr16, 'D')
WHERE
  flx.name = 'XXX'
  AND ros.type IN ( 'AL', 'AS', 'PL' )
  AND bal.period = 13
  AND NOT EXISTS ( SELECT 1 FROM table2 WHERE
    code         = bal.code
    AND value    = flx.value
    AND segment3 = ros.value
    AND flag     = bal.flag
    AND period   = 12
    AND year     = bal.year
  )

BTW to make the sub-query fast, create a composite index on table2 that contains all fields you use in the sub-query.