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 <table>
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
It's returned exactly what you've asked for. You said 'please tell me all the distinct values in col2' - and it did.
Perhaps you were looking for
SELECT DISTINCT col1, col2 from #tmd
?
It's by design...
BOL:
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.
Using 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.
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.
Dear All, Thx a lot. Rgds--Riju Oommen
