11.4 Expressions with NULL

The combination of a NULL with an AND in a predicate will not give you the results you expect. A variable that is NULL has no value. Therefore, you cannot compare it to anything else with meaningful results. An expression of comparison with a NULL does not evaluate. When such an expression exists within an IF statement and there is an ELSE part, the code does not evaluate to TRUE, so it follows the ELSE path . From this, it can appear that the condition evaluates to FALSE. If you then reverse the logic in the IF test with a NOT operator, the same ELSE path is taken.

Consider two variables : OTHER_NAME and MY_NAME. A NULL initializes the variable OTHER_NAME.

 my_name     VARCHAR2(100) := 'SCOTT'; other_name  VARCHAR2(10) := NULL; 

The following expression does not evaluate:

 IF (my_name = 'SCOTT' AND other_name = 'JOHN') THEN 

Because this expression does not evaluate, it cannot possibly evaluate to TRUE. Hence, the following PL/SQL will follow the ELSE path.

 set serveroutput on DECLARE     my_name     VARCHAR2(10) := 'SCOTT';     other_name  VARCHAR2(10) :=  NULL; BEGIN     IF (my_name = 'SCOTT' AND other_name = 'JOHN') THEN         dbms_output.put_line('CONDITION_TRUE');     ELSE         dbms_output.put_line('CONDITION_FALSE');     END IF; END; 

The result of this script is the following (notice the execution path follows the ELSE part).

 CONDITION_FALSE PL/SQL procedure successfully completed. SQL> 

Based on this outcome, the opposite of the IF condition should take the opposite execution path. That is, if we put a NOT before our test, then the code should NOT take the ELSE path and display CONDITION_TRUE. The following PL/SQL block is changed. The only change is to place a NOT in the IF statement.

 set serveroutput on DECLARE     my_name     VARCHAR2(10) := 'SCOTT';     other_name  VARCHAR2(10) :=  NULL; BEGIN     IF NOT (my_name = 'SCOTT' AND other_name = 'JOHN') THEN         dbms_output.put_line('CONDITION_TRUE');     ELSE         dbms_output.put_line('CONDITION_FALSE');     END IF; END; 

The result of this script is the same.

 CONDITION_FALSE PL/SQL procedure successfully completed. SQL> 

Based on the preceding two PL/SQL blocks, the following is never true

 (my_name = 'SCOTT' AND other_name = 'JOHN') 

The following is also never true:

 NOT (my_name = 'SCOTT' AND other_name = 'JOHN') 

The conclusion from this is that PL/SQL conditions with an AND part and a NULL expression do not evaluate. They do not evaluate to TRUE; they do not evaluate to FALSE. If there is an ELSE part in the IF statement, the code will take that path.

A condition with the OR operator that includes at least one test that evaluates to TRUE will behave as expected. The following expression displays CONDITION_TRUE because, at least, the first part, MY_NAME=SCOTT is TRUE.

 set serveroutput on DECLARE     my_name     VARCHAR2(10) := 'SCOTT';     other_name  VARCHAR2(10) :=  NULL; BEGIN     IF (my_name = 'SCOTT' OR other_name = 'JOHN') THEN         dbms_output.put_line('CONDITION_TRUE');     ELSE         dbms_output.put_line('CONDITION_FALSE');     END IF; END; 

This output is TRUE because at least MY_NAME=SCOTT is true.

 CONDITION_TRUE PL/SQL procedure successfully completed. SQL> 

When all parts of an OR condition do not evaluate, the entire expression does not evaluate. In this case, the statement is neither TRUE nor FALSE. The IF test fails to be TRUE because it does not evaluate. This takes the code to the ELSE part and leads one to believe that the condition does not evaluate to TRUE and must be FALSE. This is the case for the following:

 set serveroutput on DECLARE     my_name     VARCHAR2(10) := 'SCOTT';     other_name  VARCHAR2(10) :=  NULL; BEGIN     IF (other_name = 'JOHN' OR other_name = 'SCOTT') THEN         dbms_output.put_line('CONDITION_TRUE');     ELSE         dbms_output.put_line('CONDITION_FALSE');     END IF; END; 

Because no part of the OR test evaluates, the entire test does not evaluate. The result of the preceding block is the following output.

 CONDITION_FALSE PL/SQL procedure successfully completed. SQL> 

Reversing the logic of the IF test does not reverse the execution path, as shown next .

 set serveroutput on DECLARE     my_name     VARCHAR2(10) := 'SCOTT';     other_name  VARCHAR2(10) :=  NULL; BEGIN     IF NOT (other_name = 'JOHN' OR other_name = 'SCOTT') THEN         dbms_output.put_line('CONDITION_TRUE');     ELSE         dbms_output.put_line('CONDITION_FALSE');     END IF; END; 

The result is the same. The IF condition does not evaluate. It follows the execution of the ELSE path, shown here.

 CONDITION_FALSE PL/SQL procedure successfully completed. SQL> 

We can use the NVL operator in these situations.

NVL is a function that takes two arguments. If the first argument is NULL, the NVL function returns the second argument. For the following, NVL returns 1 when ARG is NULL.

 NVL(arg, 1) 

You can nest NVL functions. This allows you to select a NOT NULL value from a list of candidates. Consider variables: A, B, and C. You want A. If it is NULL, then use B. If that is NULL, then use C. If all are NULL, then use zero. The expression for this is:

 NVL(A, NVL(B, NVL(C,0))) 

For the following, we can replace OTHER_NAME with a blank only when it is NULL. This removes NULLs from the Boolean expression.

 set serveroutput on DECLARE     my_name     VARCHAR2(10) := 'SCOTT';     other_name  VARCHAR2(10) :=  NULL; BEGIN     IF         NOT (NVL(other_name,' ') = 'JOHN' OR              NVL(other_name,' ') = 'SCOTT')     THEN         dbms_output.put_line('CONDITION_TRUE');     ELSE         dbms_output.put_line('CONDITION_FALSE');     END IF; END; 

The output from this block is:

 CONDITION_TRUE PL/SQL procedure successfully completed. SQL> 

In summary, use caution when writing PL/SQL procedures that have IN or IN OUT mode parameters and those parameters are to be embedded in IF statements with ELSE logic. Someone may call your procedure passing a NULL and the code will execute a path that makes no sense.

A strategy is to emphasize NOT NULL constraints in the schema tables. By applying NOT NULL constraints, the values read from select statements will not be NULL. A goal should be to reduce the number of NULL variables within an application.

You also have the NVL function as shown earlier. You can use the NVL operator in your SQL select statements. This can be an initial opportunity to translate a NULL to a blank or zero as you pull the data from the database.

You can replace database NULL defaults with a NOT NULL value (see Chapter 3). A SQL INSERT and UPDATE places a NULL into a column that is not referenced in the SQL statement. This is the default behavior. Consider a table column that stores a checkbook balance. Make the default a zero rather than NULL. PL/SQL code that queries checkbook data will get a zero rather than a NULL. The decision to use a zero default rather than a NULL is because the absence of money, in this case, is zero. This eliminates the concern with embedding a checkbook balance within Boolean expressions.