title: SQL query to return rows from one table that don't exist in another
body: <p>I've made a costly and embarrassing mistake recently and I'm d*mned if it's going to happen again. I hope you can help me...here's what happened. </p>
  
  <p>In a deployment to PROD, one of the steps is to make sure all of the <code>DEV.code_mapping</code> table rows exist in the <code>PROD.code_mapping</code> table.</p>
  
  <p>To do this, I ran the following query (Oracle 11g). I expected it to display every row in <code>DEV.code_mapping</code> that did <strong><em>not</em></strong> exist in PROD.code_mapping.</p>
  
  <pre><code>select * FROM code_mapping D 
  where D.source_system_id = '&amp;LHDNUMBER'
  and not exists
      (select 1 from
      dm.code_mapping@PROD_CHECK P where
      D.mapping_definition_id = P.mapping_definition_id and
      D.cdm_code = P.cdm_code and
      D.source_system_code = P.source_system_code);
  </code></pre>
  
  <p>The query returned zero results. </p>
  
  <p>I falsely concluded that every row in <code>DEV.code_mapping</code> existed in <code>PROD.code_mapping</code>.</p>
  
  <p>It turns out that in actual fact none of the rows existed in the <code>PROD.code_mapping</code> table. The query somehow didn't pick that up. I think it may be because this query can't evaluate against the nulls in PROD, but no amount of <code>NVL([column],'null')</code> statements seem to make this work.</p>
  
  <p>How can I make this work???</p>
  
  <p><strong>ALSO:</strong></p>
  
  <p>Beside the query correction itself, I'd welcome any best practice pointers you could give me for undertakings like this. I <strong><em>REALLY</em></strong> don't want a go-live like that again.</p>