title: SQL Server - Providing priority to where clause condtions
body: <p>Please consider the following SQL.</p>
  
  <pre><code>declare @t1 table(site int, id int, name varchar(2))
  declare @t2 table(site int, id int, mark int)
  
  insert into @t1
  select 1,1,'A'
  union select 1,2,'B'
  union select 1,3,'C'
  union select 2,2,'D'
  union select 2,3,'C'
  
  insert into @t2
  select 1,1,10
  union select 1,2,20
  union select 0,3,30
  union select 1,3,40
  union select 2,3,40
  union select 2,3,40
  
  select distinct a.site, a.id,a.name,b.mark
  from @t1 a 
  inner join @t2 b
  on (a.site =b.site or b.site = 0) and a.id = b.id
  where a.site=1
  </code></pre>
  
  <p>It produces the following result</p>
  
  <pre>
  site id name mark
  ----------------------------
  1    1    A    10
  1    2    B    20
  1    3    C    30
  1    3    C    40
  </pre>
  
  <p>It's correct.</p>
  
  <p>But I want a person's data exactly once. The SQL should first check whether there is an entry for a person in @t2 for a specific site. If entry is found, then use it. If not, the mark of that person will be the person's mark who has the same name in site 0.</p>
  
  <p>In this case, I want the result as follows.</p>
  
  <pre>
  site id name mark
  ----------------------------
  1    1    A    10
  1    2    B    20
  1    3    C    40
  </pre>
  
  <p>But if (1,3,40) isn't in @t2, The result should be as follows.</p>
  
  <pre>
  site id name mark
  ----------------------------
  1    1    A    10
  1    2    B    20
  1    3    C    30
  </pre>
  
  <p>How can I do this?
  I can do it using Common Table Expression.
  So please provide me a faster way.
  I'll run it on about 100 millions rows. </p>