Missing rows in outer join have zero value for float column instead of null - why?

Question

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):

select 
  A.key1,
  A.key2,
  B.value1
from A
left join (select * from Z where value1 > 0) B 
  on A.key1 = B.key1 and A.key2 = B.key2
order by A.key1, B.key1

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.

I worked around it, but can anyone suggest why this happens? Is it a known feature/bug of SQL Server?

Answer 1

Floats are not exact, so the value might be slightly larger than 0 but still display as 0. Does it help if you change the query to:

left join (select * from Z where value1 > 0.000001) B

EDIT: After your comment, I checked on a SQL Server 2000 machine:

select t2.col2
from (select 1 as col1) t1
left join (select cast(1.0 as float) as col2) as t2 on 1=0

This displays NULL, not 0.0, for me. What tool are you using to run the query?