sql server - Why does UNION returns only one null? -
i understand null
represents missing/unknown value, null
not equal null
because 2 unknown things cannot compared. example
if null = null select 'nulls equal' else select 'nulls not equal'
results in 'nulls not equal'
used =
instead of is null
or is not null
here emphasize fact 2 nulls cannot compared.
coming union
, union
supposed eliminate duplicate values. expecting below code return 2 rows each null
since 2 null
values not equal, 1 null in result set.
(select null col1) union (select null col1)
why sql's interpretation of 'null unknown value' change in above 2 statements?
null not comparable, sql have concept of "is distinct from"
sql server has connect item it
1
distinctnull
= true1 = null
false
for completeness, null
distinct null
= false
i guess distinct , union use is distinct from
(as pரதீப் mentioned above)
now, sql server does have is distinct from
in intersect , except
declare @t1 table (t1col int); insert @t1 values (1), (null), (2), (3), (3), (5), (5); declare @t2 table (t2col int); insert @t2 values (1), (null), (3), (4); select distinct 't1 exists t2', * @t1 t1 exists (select * @t2 t2 t1.t1col = t2.t2col); t1 exists t2 1 t1 exists t2 3 t1 exists t2 3 select distinct 't1 intersect t2', * @t1 intersect select 't1 intersect t2', * @t2; t1 intersect t2 null t1 intersect t2 1 t1 intersect t2 3
intersect , except remove duplicates because semi-join
exists anti-join btw
for completeness
select 't1 exists t2', * @t1 t1 not exists (select * @t2 t2 t1.t1col = t2.t2col); t1 exists t2 null t1 exists t2 2 t1 exists t2 5 t1 exists t2 5 select 't1 except t2', * @t1 except select 't1 except t2', * @t2; t1 except t2 2 t1 except t2 5
example taken answer why except exist in t-sql? added nulls
Comments
Post a Comment