title: Merging datasets with NULL values
body: <p>Here's the scenario I'm trying to solve. I'm doing this in snowflakeDB but would love to see the approaches in other databases too. I've two sources tables S1 and S2. I need to outer join these tables based on id and load into a target table T. </p>
  
  <p>I'm using a SQL merge statement in order to load into the target T based on the key columns. Not necessarily record will exist in S2 but it can arrive in the future. </p>
  
  <pre><code>
  Source S1
  ==========
  ColA  ColB  EMP_ID
  -------------------
  Hello Fred  100
  Hello Will  101
  
  Source S2
  ==============
  Colc   EMP_ID
  -------------
  World  101
  Test   101
  
  Target T:
  ==========
  ColA | ColB | EMP_ID | ColC
  -------------------------
  Hello| Fred | 100    | NULL
  Hello| Will | 101    | World
  Hello| Will | 101    | Test 
  </code></pre>
  
  <p>In future:</p>
  
  <p>Source S2 will have the records for EMP_ID 100.</p>
  
  <pre><code>Source S2
  ===========
  
  Colc   EMP_ID
  -------------
  World  101
  Test   101
  Unit   100
  Sale   100
  
  Target T should look like below
  
  Target T:
  ==========
  ColA | ColB | EMP_ID | ColC
  -------------------------
  Hello| Fred | 100    | Unit
  Hello| Fred | 100    | Sale
  Hello| Will | 101    | World
  Hello| Will | 101    | Test
  
  Instead of below
  
  Target T:
  ==========
  ColA | ColB | EMP_ID | ColC
  -------------------------
  Hello| Fred | 100    | NULL
  Hello| Fred | 100    | Unit
  Hello| Fred | 100    | Sale
  Hello| Will | 101    | World
  Hello| Will | 101    | Test
  </code></pre>
  
  <p>I could delete the records and re-insert. Are there any other options that I could try?</p>
  
  <p>Sample Code below:</p>
  
  <pre><code>MERGE INTO T 
  using (SELECT COLA, COLB, ID, COLC 
               from S1 left outer join S2
                 on S1.EMP_ID = S2.EMP_ID) Source
  ON (NVL(Source.EMP_ID,0) = NVL(Target.EMP_ID,0))
    WHEN MATCHED THEN UPDATE
    WHEN NOT MATCHED THEN INSERT
  </code></pre>