title: Fill NULL and get distinct record
body: <p>I am ingesting data from two different source (source A and source B). Although data may be coming from two different sources, there is a possibility that an ID for a record may exist in both sources.</p>
  <p>The problem is, there are cases when one source is more complete than the other. In the example below, Source A has a NULL for column C, however Source B has a complete record.</p>
  <p>I would like to enrich the data where I get a complete record back.</p>
  <ul>
  <li>Note: the 'Source' field below is just for illustration purposes, and not an actual field in the solution.</li>
  </ul>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  <th>Source</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>asc123</td>
  <td>1</td>
  <td>2</td>
  <td>NULL</td>
  <td>4</td>
  <td>Source A</td>
  </tr>
  <tr>
  <td>asc123</td>
  <td>1</td>
  <td>2</td>
  <td>3</td>
  <td>4</td>
  <td>Source B</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The end result should be as follows:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>A</th>
  <th>B</th>
  <th>C</th>
  <th>D</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>asc123</td>
  <td>1</td>
  <td>2</td>
  <td>3</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I have attempting something like this, but have not been successful:</p>
  <pre><code>SELECT  ID
         ,A
         ,B
         ,COALESCE(A.C, B.C) AS C
         ,D
         ,Source
      FROM(
      SELECT * FROM (
          SELECT 'asc123' AS ID, 1 AS 'A', 2 AS 'B', NULL AS 'C', 4 AS 'D', 'Source A' AS 'Source'
          ) A
      UNION
  
      SELECT * FROM (
          SELECT 'asc123' AS ID, 1 AS 'A', 2 AS 'B', 3 AS 'C', 4 AS 'D', 'Source B' AS 'Source'
          ) B
      ) X
  </code></pre>