title: Select from other table if value exist
body: <p>I created a fiddle for this, at this link:
  <a href="http://www.sqlfiddle.com/#!2/7e007" rel="nofollow">http://www.sqlfiddle.com/#!2/7e007</a></p>
  
  <p>I could'nt find SQL compact / CE so it's in MySQL. </p>
  
  <p>The tables looks like this</p>
  
  <pre><code>Records                      Clients
  ID | NAME    | AGE           ID | NAME    
  ------------------           ----------------
  1  | John    | 20            1  | John
  2  | Steven  | 30            2  | Daniel
  3  | Abraham | 30            3  |
  4  | Donald  | 25            5  | Lisa 
  6  |         | 35            6  | Michael
  7  |         | 42            7  |
  </code></pre>
  
  <p>I would like to select from both tables, and if the id is in both tables and both have names I would like the the name from "Clients" as the default. If the name in Records is blank, use the client name (if any) and if the Clients.Name is blank; use the records.Name.</p>
  
  <p>From tables above, i would like this:</p>
  
  <pre><code>ID | NAME    | AGE 
  ------------------
  1  | John    | 20
  2  | Daniel  | 30
  3  | Abraham | 30
  4  | Donald  | 25
  5  | Lisa    |
  6  | Michael | 35
  7  |         | 42
  </code></pre>
  
  <p>How do i do this in SQL Compact? </p>
  
  <p>EDIT:
  Thanks to great answers below i've managed to come up with this query, which ALMOST works:</p>
  
  <pre><code>SELECT t.id, t.name, t.age FROM 
  (
      (
          SELECT r.id,
          CASE WHEN r.name = NULL THEN c.name ELSE r.name END  as name,
          r.age 
          FROM Records r 
          LEFT JOIN Clients c ON c.id = r.id
      )
      UNION 
      (
          SELECT c.id, c.name, null as age FROM Clients c where c.id NOT IN (select id from Records)
      )
  ) as t ORDER BY t.id 
  </code></pre>
  
  <p>This gives me this output:</p>
  
  <pre><code>ID | NAME    | AGE 
  ------------------
  1  | John    | 20
  2  | Daniel  | 30
  3  | Abraham | 30
  4  | Donald  | 25
  5  | Lisa    |
  6  |         | 35
  7  |         | 42
  </code></pre>
  
  <p>"Michael" (should be on #6) is missing in this case. Why?!</p>