I have created a table with 2 columns (i.e col1 ,col2)
and inserted 2 rows with null values in second column (i.e Col2)
after that i fired below qry
select disinct col2 from
as a result above query returned only single row instead of returning 2 rows,how can it be possible as the two nulls will not be same.
-----Sql Scripts ------------------
create table #tmd(col1 int,col2 int) insert into #tmd(col1)values(1) insert into #tmd(col1)values(2) select distinct col2 from #tmd
Answer by Kev Riley ·
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.
Answer by Fatherjack ·
distinct in a query means that you will get one row in your results for every combination of values in the columns you select. You only got one row returned because there is only one value in the column you requested. As Matt has shown, you will get two rows if you include the other column as that has two different values in it.
Answer by David 1 ·
As others have said, it's by design according to the SQL standard.
You are right to question the logic and wisdom of nulls. Nulls pretty much defy conventional logic and common sense. Experienced database developers often minimise the use of nulls or avoid them altogether in database design. Unfortunately they tend to crop up frequently in SQL even where you would least expect them. So to use SQL effectively you really have to learn all the many and inconsistent rules about how nulls behave and work around them as best you can.