Search code examples
sqldeploymentoracle11gproduction-environmentsetup-deployment

SQL query to return rows from one table that don't exist in another

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.

In a deployment to PROD, one of the steps is to make sure all of the DEV.code_mapping table rows exist in the PROD.code_mapping table.

To do this, I ran the following query (Oracle 11g). I expected it to display every row in DEV.code_mapping that did not exist in PROD.code_mapping.

select * FROM code_mapping D 
where D.source_system_id = '&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);

The query returned zero results.

I falsely concluded that every row in DEV.code_mapping existed in PROD.code_mapping.

It turns out that in actual fact none of the rows existed in the PROD.code_mapping 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 NVL([column],'null') statements seem to make this work.

How can I make this work???

ALSO:

Beside the query correction itself, I'd welcome any best practice pointers you could give me for undertakings like this. I REALLY don't want a go-live like that again.

Solution

  • Personally, I'd use a MINUS

    SELECT *
  FROM code_mapping
 WHERE soure_system_id = '&LHDNUMBER'
MINUS
SELECT *
  FROM dm.code_mapping@prod_check

    MINUS handles NULL comparisons automatically (a NULL on the source automatically matches a NULL on the target).

    If you want to list all differences between the two tables (i.e. list all rows that exist in dev but not prod and prod but not dev), you can add a UNION ALL

    (SELECT a.*, 'In dev but not prod' descriptio
   FROM dev_table a
 MINUS 
 SELECT a.*, 'In dev but not prod' description
   FROM prod_table a)
UNION ALL
(SELECT a.*, 'In prod but not dev' descriptio
   FROM prod_table a
 MINUS 
 SELECT a.*, 'In prod but not dev' description
   FROM dev_table a)