Why does UNION returns only one null?

Viewed 582

I understand null represents missing/unknown value, so a null is not equal to another null because two unknown things cannot be compared. For example

if null = null
    select 'nulls are equal'
else
    select 'nulls are not equal'

results in 'nulls are not equal' I used an = instead of is null or is not null here to emphasize the fact that two nulls cannot be compared.

Coming to UNION, UNION is supposed to eliminate duplicate values. I was expecting the below code to return two rows each with null since two null values are not equal, but I get only one null in the result set.

(select null as Col1)
union 
(select null as Col1)

Why does SQL's interpretation of 'null as an unknown value' change in above two statements?

3 Answers
Related