title: SQL Case statement to check for NULLS and Non-existent records
body: <p>I am doing a join between two tables and want to select the columns based on whether they have a record or not. I'm trying to avoid having multiple of the same field and am trying to condense them into single columns. Something like:</p>
  
  <pre><code>Select 
      id = (CASE WHEN a.id IS NULL THEN b.id ELSE a.id END),
      name = (CASE WHEN a.name IS NULL THEN b.name ELSE a.name END)
  From Table1 a
  Left Join Table2 b
  On a.id = b.id
  Where a.id = @id
  </code></pre>
  
  <p>I'd like <code>id</code> to populate from <code>Table1</code> if a record exists, but if not pull from <code>Table2</code>. The previous code returns no records because there are no <code>NULL</code> values in <code>Table1</code> so my question is how do I run a check to see if any records even exist? Also if anyone knows of a better way to accomplish what I am trying to do I appreciate guidance and constructive criticism.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>It looks like <code>COALESCE</code> will work for what I'm trying to accomplish. I'd like to give a little more info on exactly what I am working with and get some advice on whether I am using the best method.</p>
  
  <p>I have a bloated table <code>Table2</code> and it is in production. I'm working on building new web applications for this system but can't justify a complete database redesign so I am trying to do one "on the fly". I've created a new table <code>Table1</code> and I am writing stored procedures for the following methods <code>Get</code>(<code>Select</code>), <code>Set</code>(<code>Update</code>), <code>Add</code>(<code>Insert</code>), <code>Remove</code>(<code>Delete</code>). This way, to my code, it will seem that I am working with a single table that is not bloated. My code will simply call one of the SP methods and then the stored procedure will handle the data between the old table and the new. I am currently working on the <code>Get</code> method and I need to check the old table <code>Table2</code> for a record if it doesn't exist in <code>Table1</code>.
  Thanks to the suggestions here my query currently looks like this:</p>
  
  <pre><code>Select
      id = coalesce(a.id, b.student_number),
      first_name = coalesce(a.first_name, b.first_name),
      last_name = coalesce(a.last_name, b.last_name),
      //etc
  From Table1 a
  Full Outer Join Table2 b
  On a.id = b.student_number
  Where (a.id = @id Or b.student_number = @id)
  </code></pre>
  
  <p>This works for what I'm trying to accomplish, I'd like to throw it out there to the experienced crowd for any tips or suggestions if there are better or more correct ways to go about this.</p>
  
  <p>Thanks</p>