title: Replace NULL column after SQL UNION of select
body: <p>I am using a <code>UNION</code> to combine the results of two select statements. In the returned result, the NULL values need to be filled up:</p>
  
  <pre><code>SELECT OrderNumber, OrderName 
  FROM tblOrder 
  WHERE OrderStatus ='New' 
  
  UNION 
  
  SELECT OrderNumber, OrderName 
  FROM tblOrderHistory 
  WHERE OrderStatus ='New' 
  ORDER BY OrderNumber, OrderName
  </code></pre>
  
  <p>The result:</p>
  
  <pre><code>OrderNumber OrderName
  ---------------------
  A001            abc
  A001            abc
  A001            NULL    
  A001            NULL
  A001            NULL
  B002            xyz
  B002            NULL
  C003            abc
  </code></pre>
  
  <p>My expected result:</p>
  
  <pre><code>OrderNumber OrderName
  ---------------------
  A001            abc
  A001            abc
  A001            abc
  A001            abc
  B002            xyz
  B002            xyz
  C003            abc
  </code></pre>
  
  <p>How to fill up the <code>NULL</code> values by matching the order number from <code>tblOrder</code>, the <code>OrderName</code> value in <code>tblOrderHistory</code> rows is always <code>NULL</code>. </p>