title: Missing rows in outer join have zero value for float column instead of null - why?
body: <p>I'm using Microsoft's SQL Server 2000 and have a query that is essentially this (except that Z is a further subquery, not a table):</p>
  
  <pre><code>select 
    A.key1,
    A.key2,
    B.value1
  from A
  left join (select * from Z where value1 &gt; 0) B 
    on A.key1 = B.key1 and A.key2 = B.key2
  order by A.key1, B.key1
  </code></pre>
  
  <p>Column value1 is of type float. For some reason, instead of returning NULL where rows don't exist in subquery B, value1 is set to 0.0.</p>
  
  <p>I worked around it, but can anyone suggest why this happens? Is it a known feature/bug of SQL Server?</p>