Home » SQL & PL/SQL » SQL & PL/SQL » Duplicate NULL (Oracle9i)
Duplicate NULL [message #443093] Fri, 12 February 2010 09:35 Go to next message
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 #443094 is a reply to message #443093] Fri, 12 February 2010 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Duplicate NULL [message #443098 is a reply to message #443093] Fri, 12 February 2010 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
interesting posting history.
start thread with basic Read The Fine Manual question & never respond again.


Re: Duplicate NULL [message #443100 is a reply to message #443093] Fri, 12 February 2010 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is NOT challenging, it is basic interview/exam questions, refer to Database SQL Reference and Database Concepts, chapter 5 Schema Objects.

Regards
Michel
Re: Duplicate NULL [message #443111 is a reply to message #443093] Fri, 12 February 2010 12:39 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
prakashaa
It's a challenging question to all experts

If I remember correctly, now and then "challenging" topics appear on the Forum, but it appears that the challenge was only on the OP's side, not the experts one. But that's OK, I guess (finding something to be challenging); however, suggesting that experts should answer questions that can easily be read in the manual is, well, not that OK.
Re: Duplicate NULL [message #443114 is a reply to message #443111] Fri, 12 February 2010 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
They just hope that with "challenging" in the title we'll take a closer look to their questions but it is the opposite that happens: without this "challenging" label I'd take time to extensively answer, there it is disapointing and the answer is then just a pointer to the doc.

Regards
Michel
Re: Duplicate NULL [message #443123 is a reply to message #443114] Fri, 12 February 2010 15:57 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: how to re write the update statment if high volume records are there
Next Topic: schema and user
Goto Forum:
  


Current Time: Wed Feb 19 15:59:52 CST 2025