>>>UNION will filter the duplicate records, BUT you will never have ones, because of CAST('' AS char(50))
>>>
>>>

>>>SELECT 1, 'abracadabra'
>>>UNION
>>>SELECT 1, ''
>>>

>>>will generate two records
>>
>>I see now, you're right. We do need a left join in the second portion with is null in where clause.
>
>Ergo your original assertion is false, which is exactly what I figured.

Nope, it is still correct. Every left join is equal to inner join + union for the rest. You can use NOT EXISTS and the same query as at the top or use CTE in SQL Server 2005. There was an interesting discussion in tek-tips forum on a similar problem, but I'm not going to search for it right now.