title: SQL Union but filter out Partial Duplicates
body: <p>This is a very simplified version of what I have.</p>
  
  <p>I have 2 database tables People and Contacts.
  People is the original table but contacts is a new table that is used to override the data in the People table (these are different databases in the real scenario)</p>
  
  <p>On initial Load all the people records are copied to the contacts table but after the initial Load new people can be added to the people or contact table as well as contacts set as deleted.</p>
  
  <p>I want all the people returned but I want the contacts table to be the overriding  one for example</p>
  
  <p>example.</p>
  
  <p><strong>People Table</strong></p>
  
  <pre><code>     PersonGuid | PersonId | FirstName   | LastName
       -----------|----------|-------------|-----
  1      xxxx1    | 1        | John        | Smith
  2      xxxx2    | 2        | Jane        | Doe
  3      xxxx3    | 3        | John        | Doe
  4      xxxx4    | 4        | Alice       | James
  5      xxxx5    | 5        | Claire      | Wheeler
  </code></pre>
  
  <p><strong>Contacts table</strong> 
  (Jane Doe is set as deleted and John Doe doesn't exist due as was added after the intial import and Emma Green has been added. Deleted is NULL for ones that haven't been touched)</p>
  
  <pre><code>     ID         | LinkedId | FirstName   | LastName |  Deleted
       -----------|----------|-------------|----------|----------
  1      xxxx1    | 1        | John        | Smith    |  NULL
  2      xxxx2    | 2        | Jane        | Doe      |  1
  3      xxxx3    | NULL     | Emma        | Green    |  0
  4      xxxx4    | 4        | Alice       | James    |  0
  5      xxxx5    | 5        | Claire      | Wheeler  |  NULL
  </code></pre>
  
  <p><strong>What I want to Return</strong> is: (All records but with Jane Doe Set as Deleted as she is in the contacts table)</p>
  
  <pre><code>     ID         | LinkedId | FirstName   | LastName |  Deleted
       -----------|----------|-------------|----------|----------
  1      xxxx1    | 1        | John        | Smith    |  NULL
  2      xxxx2    | 2        | Jane        | Doe      |  1
  3      xxxx3    | 3        | John        | Doe      |  0
  4      xxxx4    | NULL     | Emma        | Green    |  0
  5      xxxx5    | 4        | Alice       | James    |  0
  6      xxxx6    | 5        | Claire      | Wheeler  |  NULL
  </code></pre>
  
  <p><strong>But what I am getting</strong> is:
  (Jane Doe Twice)</p>
  
  <pre><code>     ID         | LinkedId | FirstName   | LastName |  Deleted
       -----------|----------|-------------|----------|----------
  1      xxxx1    | 1        | John        | Smith    |  NULL
  2      xxxx2    | 2        | Jane        | Doe      |  1
  3      xxxx2    | 2        | Jane        | Doe      |  0
  4      xxxx3    | 3        | John        | Doe      |  0
  5      xxxx4    | NULL     | Emma        | Green    |  0
  6      xxxx5    | 4        | Alice       | James    |  0
  7      xxxx6    | 5        | Claire      | Wheeler  |  NULL
  </code></pre>
  
  <p>I am trying to Union the two tables but I don't know how to filter it to make the contacts data override the people data but also get all data from both tables?</p>
  
  <pre><code>Select PersonGuid as Id,
  Personid,
  Firstname,
  LastName,
  cast(0 as bit) as deleted
  From DB1.People
  
  Union
  
  Select
  Id,
  LinkedId as PersonId,
  FirstName,
  LastName,
  Deleted 
  from DB2.Contacts
  </code></pre>
  
  <p>I have tried searching but I'm not sure what to search by so my terms may be wrong.</p>
  
  <p>EDIT - The real query is no where near as simple as my example and the from is a join of about 6 tables on each query. I've tried to make a simple example to make it clear, but not sure if this has made the solution different?</p>