Understanding Nulls In Oracle Options

codeling Posts: 1057 Points: 4443
Posted: Monday, January 4, 2016 9:14:20 AM

In an Oracle table, if a column in a row has no value, then the column is said to be null, or to contain null. Nulls can appear in columns of any datatype that are not restricted by NOT NULL or PRIMARY KEY integrity constraints. Use a null when the actual value is not known or when a value would not be meaningful.

Do not use null to represent a value of zero, because they are not equivalent. (Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.) Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null. In fact, all operators (except concatenation) return null when given a null operand.

NULL Comparison in Oracle

In Oracle, NULL means the lack of a value, sometimes no value is equivalent in certain circumstances to a specific value (zero for integers, for instance), but Oracle cannot make that determination generally.

So if x is a specific value, it cannot equal no value, this means that x=null and the other comparison operators will just always return false.

If x is null though, x=null will still return false, and the reason for this becomes clear if you think about exactly what you're asking. Suppose we have a table that represents the number of apples owned by people. Alice is in the table, but we don't know how many apples she has, so we insert ('Alice',NULL). Bob is in the table, ditto, ('Bob',NULL). Now we run the query "do Alice and Bob have the same number of apples?" Since we don't know how many apples either of them has, it is impossible to answer the question, so the comparison Alice=Bob will return NULL or FALSE depending how exactly you do it. Now, if not knowing how many apples someone has is equivalent in a particular scenario to them having zero apples (perhaps a better example is a salary/commission table - no commission will in most cases be equivalent to zero commission, although we might still want to distinguish between 0 and NULL so that we don't accuse non-commissioned people of having a really crappy month), then the comparison NVL(Alice,0)=NVL(Bob,0) will work and will return TRUE, because we are now comparing specific values, not NULLs.

The reason you shouldn't use equality for testing NULLs is that it's easy to get confused; if you have if (x=null) do_A else do_B, then do_B will be done because the expression evaluates FALSE. It would therefore seem logical therefore that if we replace x=null with x!=null then do_A should be done. It won't, because the comparison still returns FALSE and do_B will still be done. But if we have if (x is null) instead, then if x is not null do_B will be done, and if we replace the comparison with if (x is not null) then do_A will be done as expected.

Sometimes you'll want to select where col_A = col_B, and want the results to include where col_A and col_B are both NULL; they are not, because NULL=NULL is FALSE as explained above. You could use NVL here, but functions kill indices and there may not be a spare value that can be used, so the correct where clause would be "col_A=col_B or col_A is null and col_B is null" - somewhat clunky but crystal clear in its meaning.


codeling Posts: 1057 Points: 4443
Posted: Monday, January 4, 2016 9:22:37 AM

Nulls in SQL Functions

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.

Most aggregate functions ignore nulls. For example, consider a query that averages the five values 1000, null, null, null, and 2000. Such a query ignores the nulls and calculates the average to be (1000+2000)/2 = 1500.


codeling Posts: 1057 Points: 4443
Posted: Monday, January 4, 2016 9:53:17 AM

Nulls in Conditions

To test for nulls, use only the comparison conditions IS NULL and IS NOT NULL. If you use any other condition with nulls and the result depends on the value of the null, then the result is UNKNOWN. Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function.

Oracle also considers two nulls to be equal if they appear in compound keys. That is, Oracle considers identical two compound keys containing nulls if all the non-null components of the keys are equal.

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

The following table shows examples of various evaluations involving nulls in conditions. If the conditions evaluating to UNKNOWN were used in a WHERE clause of a SELECT statement, then no rows would be returned for that query.

 

If A is: Condition Evaluates to:

10

a IS NULL

FALSE

10

a IS NOT NULL

TRUE

NULL

a IS NULL

TRUE

NULL

a IS NOT NULL

FALSE

10

a = NULL

UNKNOWN

10

a != NULL

UNKNOWN

NULL

a = NULL

UNKNOWN

NULL

a != NULL

UNKNOWN

NULL

a = 10

UNKNOWN

NULL

a != 10

UNKNOWN


codeling Posts: 1057 Points: 4443
Posted: Monday, January 18, 2016 9:00:36 PM
codeling wrote:

Nulls in SQL Functions

All scalar functions (except REPLACE, NVL, and CONCAT) return null when given a null argument. You can use the NVL function to return a value when a null occurs. For example, the expression NVL(COMM,0) returns 0 if COMM is null or the value of COMM if it is not null.

Which of the answer choices can be used in place of #REPLACE# in the following block so that after execution, “1500” will be displayed?

DECLARE

   l_current NUMBER := 1000;

   l_proposed NUMBER := NULL;

   l_average NUMBER := 1500;

BEGIN

    DBMS_OUTPUT.put_line (#REPLACE#);

END;


a.

GREATEST (l_current, l_proposed, l_average)

 

b.

GREATEST ( NVL (l_current, -1), NVL (l_proposed, -1), NVL (l_average, -1))

 

c.

NVL (GREATEST (l_current, l_proposed, l_average), -1)

 

Check the anwser in the next post.

Users browsing this topic
Guest