Menu
In Sql Server, NULL IS NOT EQUAL TO NULL.(Why does NULL = NULL evaluate to false in SQL server) Then why the following code returns single NULL.
CREATE TABLE #TEMP1
(ID INT)
INSERT INTO #TEMP1
SELECT NULL
UNION ALL
SELECT NULL
SELECT DISTINCT ID FROM #TEMP1
DROP TABLE #TEMP1
ID
------
NULL
I expected
ID
------
NULL
NULL
850
In SQL, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.
And while the SQL Server documentation specifies that DISTINCT will include nulls while COUNT(DISTINCT) will not , this is not something that many people find intuitive.
There are way to create a unique index that allows multiple nulls in MS SQL Server, but it's not the default. The default in MS is to allow just one NULL. But that's not the ANSI standard. ANSI standards 92, 99, and 03 explicitly allow multiple nulls with unique.
If you apply the DISTINCT clause to a column that has NULL, the DISTINCT clause will keep only one NULL and eliminates the other. In other words, the DISTINCT clause treats all NULL “values” as the same value. SQL Server SELECT DISTINCT examples For the demonstration, we will use the customers table from the sample database.
It's by design... For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.
Sometimes, you may want to get only distinct values in a specified column of a table. To do this, you use the SELECT DISTINCT clause as follows: SELECT DISTINCT column_name FROM table_name; Code language: SQL (Structured Query Language) (sql)
What Exactly Is NULL? A NULL is a term with a value other than 0 or an empty string, i.e. an unknown value that data is missing. As per Wikipedia, "Null or NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database.
The handling of NULLs for DISTINCT comparison is clearly called out in the documentation. Excerpt:
DISTINCT
Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.
164
Not only is it called out in the documentation, it’s a different kind of equality test. Does unknown equal unknown? Who knows, maybe yes, maybe no. But that is exactly why Distinct should return only 1. If it returned 2 different values, that would be saying that the unknowns were different. There exists at least one unknown value so, it should be in the list, whether it is different from any of the other unknown values is, well, unknown.
Null values compare as equal for Intersect as well.
Select 1
Where exists (select null intersect select null)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
