My scrapbook about anything which I learned or want to remember, Sometime about tech tips, thoughts and rambling. If you find anything useful don't forget to give thumbs-up :)

Breaking

Sunday, August 14, 2016

NULL behaviour in Oracle


NULL, as known in SQL, a special marker and keyword indicating that something has no value. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. (ref - https://technet.microsoft.com/en-us/library/ms191504(v=sql.105).aspx)

For understanding it, I am using a practical approach where we play around NULL value, sorry No Value, with Oracle database.



** NULL value doesn't count in aggregate functions.
NULL value doesn't count by aggregate functions such as MAX, MIN, SUM, COUNT in SQL.

Example:


** Inserted null string converted to Null.

Example:


** Where can Null be compared?
Example:


Oracle SQL Reference says "In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null."
But this therory varies with DB to DB internal architecture and the way they handled the NULL.Another place where Null can be compared is in range partition definition, where MAXVALUE is greater than Null

**  Unique constraints
Example:


You are able to insert another Null without getting ORA-1 (unique constraint violated).
--
Example:


So if all columns are null, the unique constraint will not be violated. If one or more columns have non-null values, the constraint takes effect.


** Unknown OR True returns True, Unknown AND False returns False
Example:



 
Like the below page to get update  
https://www.facebook.com/datastage4you
https://twitter.com/datagenx
https://plus.google.com/+AtulSingh0/posts
https://datagenx.slack.com/messages/datascience/

No comments:

Post a Comment

Disclaimer

The postings on this site are my own and don't necessarily represent IBM's or other companies positions, strategies or opinions. All content provided on this blog is for informational purposes and knowledge sharing only.
The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of his information.