title: t-sql select one column if record exists, different column if not
body: <p>I often find myself with a need to select a value from one of two different tables, tableA and tableB.  If a certain record exists in tableA, I grab the value from tableA. If the record does not exist, I instead need a value from tableB. </p>
  
  <p>I am working with an ERP application that will create an event based record in a sales order ship-to address table if the user changes the default ship-to address when entering a sales order.  If the user does not change the ship-to address, the system assumes the product is being shipped to the address on the customer file and does not write anything to the sales order ship-to table. I need the ship-to address for every sales order shipment, but I am not safe in grabbing it directly from either tableA or tableB. </p>
  
  <p>What I've done in the past is something like this:</p>
  
  <pre><code>SELECT
      CASE 
          WHEN isnull(tableA.SalesOrderNumber, 'StackOverflow') = 'StackOverflow'
              THEN tableB.address1
          ELSE tableA.address1
          END AS address1
  </code></pre>
  
  <p>Which works. But then you end up repeating this same thing over and over again for every field with similar logic, which sucks, but it is not unworkable if the value you are checking for (<code>SalesOrderNumber</code>) is character based data and/or a primary key. If the value I am checking for is not a primary key or if it is numeric type data then this approach becomes much more fragile. </p>
  
  <p>There's got to be a better way, right?  </p>
  
  <p>I have tried a few different approaches with the <code>EXISTS</code> operator and gotten nowhere. </p>