title: Oracle Minus query. How can I get a result with NULLS if the top SQL and the bottom SQL don't contain NULLs?
body: <p>How could this SQL...</p>
  
  <pre><code>CREATE TABLE NewTable AS
      SELECT A,B,C FROM Table1
      minus
      SELECT A, B, C From Table2
  </code></pre>
  
  <p>...create a new table with NULL values in column A
  when neither Table1 or Table2 had NULL values for in column A?</p>
  
  <p>But on the other hand, this SQL...</p>
  
  <pre><code>SELECT * FROM
  (
     SELECT A,B,C FROM Table1
      minus
      SELECT A, B, C From Table2
  ) 
  WHERE A IS NULL 
  </code></pre>
  
  <p>return no rows!</p>
  
  <p>It seems inconsistent!</p>
  
  <p>I think it is a bug in Oracle.</p>
  
  <p>Of course the real SQL is much more complex but I believe this accurately illustrates the nature of the problem.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here's the ACTUAL SQL:</p>
  
  <p>I executed this statement:</p>
  
  <pre><code>CREATE TABLE MyMinus
  AS
  select 
  *
  FROM
  ---begin main query 
  (
  SELECT expenditure_item_date, expenditure_org, expenditure_type,
         f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
         f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
         f_company_code, f_cost_center_num, f_cuic, f_currency_code,
         f_destination_type_code, f_distribution_id, f_distribution_num,
         f_exchange_rate, f_extract_date, f_gl_account,
         f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
         f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
         f_project, f_project_num, f_promised_date, f_quantity_billed,
         f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
         f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
         f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
    FROM dw_mgr.po_distributions_curr_fct a
   WHERE EXISTS (
            SELECT 1
              FROM dw_mgr.po_distributions_curr_fct b,
                   dw_mgr.po_lines_curr_fct,
                   dw_mgr.po_header_curr_fct
             WHERE a.ROWID = b.ROWID
               AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
               AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
               AND dw_mgr.po_lines_curr_fct.f_cuic =
                                                dw_mgr.po_header_curr_fct.f_cuic
               AND dw_mgr.po_lines_curr_fct.f_header_id =
                                           dw_mgr.po_header_curr_fct.f_header_id
               AND dw_mgr.po_header_curr_fct.f_header_creation_date &lt;
                                        ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                    -48)
               AND dw_mgr.po_header_curr_fct.f_po_status IN
                                                   ('CLOSED', 'FINALLY CLOSED'))
  
  MINUS 
  
  SELECT expenditure_item_date, expenditure_org, expenditure_type,
         f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
         f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
         f_company_code, f_cost_center_num, f_cuic, f_currency_code,
         f_destination_type_code, f_distribution_id, f_distribution_num,
         f_exchange_rate, f_extract_date, f_gl_account,
         f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
         f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
         f_project, f_project_num, f_promised_date, f_quantity_billed,
         f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
         f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
         f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
    FROM arch_fct.po_distributions_curr_fct a
   WHERE EXISTS (
            SELECT 1
              FROM arch_fct.po_distributions_curr_fct b,
                   arch_fct.po_lines_curr_fct,
                   arch_fct.po_header_curr_fct
             WHERE a.ROWID = b.ROWID
               AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
               AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
               AND arch_fct.po_lines_curr_fct.f_cuic =
                                              arch_fct.po_header_curr_fct.f_cuic
               AND arch_fct.po_lines_curr_fct.f_header_id =
                                         arch_fct.po_header_curr_fct.f_header_id
               AND arch_fct.po_header_curr_fct.f_header_creation_date &lt;
                                        ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                    -48)
               AND arch_fct.po_header_curr_fct.f_po_status IN
                                                   ('CLOSED', 'FINALLY CLOSED'))
  
  ) 
  </code></pre>
  
  <p>And then this. Note that rows with NULL values of F_DISTRIBUTION_ID were inserted into the created table.</p>
  
  <pre><code>SELECT COUNT(*) from MyMinus WHERE F_DISTRIBUTION_ID IS NULL
  </code></pre>
  
  <p>--17 rows</p>
  
  <p>Yet when I execute this:</p>
  
  <pre><code>select 
  *
  FROM
  ---begin main query 
  (
  SELECT expenditure_item_date, expenditure_org, expenditure_type,
         f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
         f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
         f_company_code, f_cost_center_num, f_cuic, f_currency_code,
         f_destination_type_code, f_distribution_id, f_distribution_num,
         f_exchange_rate, f_extract_date, f_gl_account,
         f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
         f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
         f_project, f_project_num, f_promised_date, f_quantity_billed,
         f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
         f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
         f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
    FROM dw_mgr.po_distributions_curr_fct a
   WHERE EXISTS (
            SELECT 1
              FROM dw_mgr.po_distributions_curr_fct b,
                   dw_mgr.po_lines_curr_fct,
                   dw_mgr.po_header_curr_fct
             WHERE a.ROWID = b.ROWID
               AND b.f_cuic = dw_mgr.po_lines_curr_fct.f_cuic
               AND b.f_line_id = dw_mgr.po_lines_curr_fct.f_line_id
               AND dw_mgr.po_lines_curr_fct.f_cuic =
                                                dw_mgr.po_header_curr_fct.f_cuic
               AND dw_mgr.po_lines_curr_fct.f_header_id =
                                           dw_mgr.po_header_curr_fct.f_header_id
               AND dw_mgr.po_header_curr_fct.f_header_creation_date &lt;
                                        ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                    -48)
               AND dw_mgr.po_header_curr_fct.f_po_status IN
                                                   ('CLOSED', 'FINALLY CLOSED'))
  
  MINUS 
  
  SELECT expenditure_item_date, expenditure_org, expenditure_type,
         f_amount_billed, f_amount_billed_fc, f_amount_billed_us,
         f_bl_creation_date, f_catalog_source, f_catalog_type, f_company,
         f_company_code, f_cost_center_num, f_cuic, f_currency_code,
         f_destination_type_code, f_distribution_id, f_distribution_num,
         f_exchange_rate, f_extract_date, f_gl_account,
         f_isms_jamis_project_num, f_line_id, f_local_use, f_location_num,
         f_need_by_date, f_org_id, f_po_line_num, f_po_num, f_po_release_num,
         f_project, f_project_num, f_promised_date, f_quantity_billed,
         f_quantity_cancelled, f_quantity_delivered, f_quantity_ordered,
         f_rel_approved_flag, f_rel_cancelled_flag, f_rel_cancel_date,
         f_rel_closed_code, f_rel_hold_flag, f_rel_revision_num, f_task_num
    FROM arch_fct.po_distributions_curr_fct a
   WHERE EXISTS (
            SELECT 1
              FROM arch_fct.po_distributions_curr_fct b,
                   arch_fct.po_lines_curr_fct,
                   arch_fct.po_header_curr_fct
             WHERE a.ROWID = b.ROWID
               AND b.f_cuic = arch_fct.po_lines_curr_fct.f_cuic
               AND b.f_line_id = arch_fct.po_lines_curr_fct.f_line_id
               AND arch_fct.po_lines_curr_fct.f_cuic =
                                              arch_fct.po_header_curr_fct.f_cuic
               AND arch_fct.po_lines_curr_fct.f_header_id =
                                         arch_fct.po_header_curr_fct.f_header_id
               AND arch_fct.po_header_curr_fct.f_header_creation_date &lt;
                                        ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),
                                                    -48)
               AND arch_fct.po_header_curr_fct.f_po_status IN
                                                   ('CLOSED', 'FINALLY CLOSED'))
  
  ) 
  WHERE
  
  f_distribution_id is null
  </code></pre>
  
  <p>I get 0 rows.</p>
  
  <p>Why does insert the records into a temp table appear to introduce rows with NULL DIST IDs?</p>
  
  <p>This minus query SQL, which was generated dynamically by a custom data archival program, attempts to verify that the data which SHOULD be archived in the DW_MGR schema was in fact copied to the ARCH_FCT (archive) schema. It is returning differences which included 17 records where the F_DISTRIBUTION_ID in the MyMinus temp table do not match those in the source DW_MG.PO_DISTRIBUTIONS_CURR_FCT table because they are are NULL. Hence, the archive process is design when differences are found. The question is why are there differences, i.e., how did NULL values get into the MyMinus table when they are not in the SOURCE PO_DISTRIBUTIONS_CURR_FCT table?</p>
  
  <p>EDIT: </p>
  
  <p>Can someone with Oracle META access please post info on thd following Oracle bugs. I was referred to them but I contract located someone in my co who can tell me what our support ID # is. I will find out eventually, but it would be nice to know sooner. If you would rather not post it, consider the following bug references as potentially related info on my question:</p>
  
  <pre><code>Bug 8209309: MINUS IS SHOWING DIFFERENCES WITH CTAS + INSERT 
  Bug 7834950: WRONG RESULTS WITH MINUS OPERATOR
  </code></pre>