title: Alternative to sql NOT IN?
body: <p>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. </p>
  
  <p>Put simply, here's the code I'm trying to modify:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>And here's one of my attempt:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>This drives me nuts! Thanks in advance for the help :)</p>