Why does UNION only return one null?
I understand what null
the missing / unknown value represents, so is null
not equal to the other null
, because two unknown things cannot compare. for example
if null = null
select 'nulls are equal'
else
select 'nulls are not equal'
leads to 'nulls are not equal'
I used =
instead of is null
or is not null
here to emphasize the fact that two zeros cannot compare.
Coming to UNION
, UNION
it is supposed to eliminate duplicate values. I expected the code below to return two rows with null
since the two values are null
not equal, but I am only getting one zero in the result set.
(select null as Col1)
union
(select null as Col1)
Why does SQL's interpretation of "null as unknown" change in the above two statements?
source to share
NULL is not comparable, but SQL generally has the concept of "IS DISTINCT FROM"
SQL Server has Connect for it
-
1
NONE FROMNULL
= true -
1 = null
false
For completeness, NULL
DISABLE FROM NULL
= false
I would guess that DISTINCT and UNION use IS DISTINCT FROM
(as above P)
SQL Server now has IS DISTINCT FROM
in INTERSECT and 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', *
FROM @t1 t1 WHERE EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);
t1 EXISTS t2 1
t1 EXISTS t2 3
t1 EXISTS t2 3
SELECT DISTINCT 't1 INTERSECT t2', *
FROM @t1 INTERSECT SELECT 't1 INTERSECT t2', * FROM @t2;
t1 INTERSECT t2 NULL
t1 INTERSECT t2 1
t1 INTERSECT t2 3
INTERSECT and EXCEPT remove duplicates as well because they do semi-join
EXISTS is anti-join BTW
For completeness
SELECT 't1 EXISTS t2', *
FROM @t1 t1 WHERE NOT EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);
t1 EXISTS t2 NULL
t1 EXISTS t2 2
t1 EXISTS t2 5
t1 EXISTS t2 5
SELECT 't1 EXCEPT t2', *
FROM @t1 EXCEPT SELECT 't1 EXCEPT t2', * FROM @t2;
t1 EXCEPT t2 2
t1 EXCEPT t2 5
Example from my answer Why does EXCEPT exist in T-SQL? with added NULLs
source to share
UNION
basically SELECT DISTINCT
, so it will eliminate duplicate NULL values, but that's not the same as an equal operation.
Using UNION ALL
will give you all records including NULL duplicates.
As for the first part of the question. NULL is indeed NULL, but not with "=". This will give you the output you expect:
if null IS null
select 'nulls are equal'
else
select 'nulls are not equal'
source to share