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?