Duplicate NULL [message #443093] |
Fri, 12 February 2010 09:35  |
prakashaa
Messages: 31 Registered: November 2009 Location: Bangalore
|
Member |
|
|
Hi Experts,
It's a challenging question to all experts
Q1)
If we create an UNIQUE Key on a table Why it's accepting morethan one NULL value.
Q2)
If we use DISTINCT key word on a column that contains morethan one NULL value why it's displaying one NULL value.
Q3)
If we use UNION on a column that contains morethan one NULL value, why it returns one NULL value.
Q4)
If we write a query like this
SELECT * FROM emp WHERE comm IS NULL
Why it returns all the employes those contain no comm.
For 1st question some pleople are answering any NULL is not equal to other NULL value so that it accepting multiple NULL values.
For 2nd,3rd question some people are answering because DISTINCT and UNION eliminates duplicate values.
According to the first question answer how it is possible.
For 4th question answer some people are saying all the NULLs are equal so that it displaying all the employess which are having no commision.
Any One Plese help how NULL works in all four scenarios.
Thanks in advance.
|
|
|
|
|
|
|
|
Re: Duplicate NULL [message #443123 is a reply to message #443114] |
Fri, 12 February 2010 15:57   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I think you guys are being a bit unfair. Re-read the post carefully. It' badly presented, but it is clear to me that the OP knows the answers to these questions, but sees the answers as contradictory.
Perhaps a better way of putting it is:
If two NULLs are duplicates for the purpose of DISTINCT, why are they not duplicates for the purpose of a Unique Key.
Put that way, I think it's a good question.
As for Q4, I'm not quite sure what point was being made here. I suspect the point is that if all rows with no commission are returned by the same IS NULL predicate, then that implies they are all "the same", or duplicated. My answer to that is that you can get a similar result from COMM > 0. This will show all of the positive NON-NULL values, but it does not imply that they are duplicated. Don't confuse IS NULL with equality.
Ross Leishman
|
|
|
Re: Duplicate NULL [message #443195 is a reply to message #443093] |
Sat, 13 February 2010 09:20  |
 |
rahulvb
Messages: 924 Registered: October 2009 Location: Somewhere Near Equator.
|
Senior Member |
|
|
well its not exact answer but illustration how and why you get only null value .
consider Internally Database consider Null as 'Value' , but columns with null values for you its like empty room full with Air. so room is Empty full with air but does not occupy space.
[Updated on: Sat, 13 February 2010 09:21] Report message to a moderator
|
|
|