title: Optimizing Oracle query by removing 'exists' and 'not exists'
body: <p>I recently moved a piece of code into production on a oracle database where one of the more experienced developer who reviewed it mentioned I had way too many <code>exists</code> and <code>not exists</code> statements and that there should be a way to remove them, but it had been too long since he had to use it and didn't remember much on how it worked.  Currently, I'm going back and making the piece of code more maintainable as it is a piece likely to be changed multiple times in future years as business logic/requirements change, and I wanted to go ahead and optimize it while making it more maintainable.</p>
  
  <p>I've tried looking it up, but all I can find is recommendations on replacing <code>not in</code> with <code>not exists</code> and to not return actual results.</p>
  
  <p>As such, I'm wondering what can be done to optimize out <code>exists</code>/<code>not exists</code> or if there is a way to write <code>exists</code>/<code>not exists</code> so that oracle will optimize it internally (likely at a better degree than I can).</p>
  
  <p>For example, how can the following be optimized?</p>
  
  <pre><code>UPDATE
      SCOTT.TABLE_N N
  SET
      N.VALUE_1 = 'Data!'
  WHERE
      N.VALUE_2 = 'Y'
      AND
      EXISTS
      (
          SELECT
              1
          FROM
              SCOTT.TABLE_Q Q
          WHERE
              N.ID = Q.N_ID
      )
      AND
      NOT EXISTS
      (
          SELECT
              1
          FROM
              SCOTT.TABLE_W W
          WHERE
              N.ID = W.N_ID
      )
  </code></pre>