7.4 Using NULLs in Compound Queries

We discussed union compatibility conditions at the beginning of this chapter. The union compatibility issue gets interesting when NULLs are involved. As you know, NULL doesn't have a data type, and NULL can be used in place of a value of any data type. If you purposely select NULL as a column value in a component query, Oracle no longer has two data types to compare to see whether the two component queries are compatible. This is particularly an issue with older Oracle releases. Oracle9i Database, and also later releases of Oracle, are "smart enough" to know which flavor of NULL to use in a compound query. The following examples, generated from an Oracle9i database, demonstrate this:

SELECT 1 num, 'DEFINITE' string FROM DUAL

UNION

SELECT NULL num, 'UNKNOWN' string FROM DUAL;



       NUM STRING

---------- --------

         1 DEFINITE

           UNKNOWN



SELECT 1 num, SYSDATE dates FROM DUAL

UNION

SELECT 2 num, NULL dates FROM DUAL;



       NUM DATES

---------- ---------

         1 06-JAN-02

         2

If you are using Oracle8i or prior, these queries may cause errors. The examples in the rest of this section are executed against an Oracle8i database.

When your set operation includes a character column that corresponds to a NULL literal, you won't have any problems from the use of NULL. All releases of Oracle handle this case. For example, from an Oracle8i installation:

SELECT 1 num, 'DEFINITE' string FROM DUAL

UNION

SELECT 2 num, NULL string FROM DUAL;



       NUM STRING

---------- --------

         1 DEFINITE

         2

Notice that Oracle8i considers the character string 'DEFINITE' from the first component query to be compatible with the NULL value supplied for the corresponding column in the second component query.

However, if a NUMBER or a DATE column of a component query is set to NULL, you must explicitly tell Oracle what "flavor" of NULL to use. Otherwise, you'll encounter errors. For example:

SELECT 1 num, 'DEFINITE' string FROM DUAL

UNION

SELECT NULL num, 'UNKNOWN' string FROM DUAL;

SELECT 1 num, 'DEFINITE' string FROM DUAL

       *

ERROR at line 1:

ORA-01790: expression must have same datatype as corresponding expression

Note that the use of NULL in the second component query causes a data type mismatch between the first column of the first component query, and the first column of the second component query. Using NULL for a DATE column causes the same problem, as in the following example:

SELECT 1 num, SYSDATE dates FROM DUAL

UNION

SELECT 2 num, NULL dates FROM DUAL;

SELECT 1 num, SYSDATE dates FROM DUAL

              *

ERROR at line 1:

ORA-01790: expression must have same datatype as corresponding expression

In these cases, you need to cast the NULL to a suitable data type to fix the problem, as in the following examples:

SELECT 1 num, 'DEFINITE' string FROM DUAL

UNION

SELECT TO_NUMBER(NULL) NUM, 'UNKNOWN' string FROM DUAL;



       NUM STRING

---------- --------

         1 DEFINITE

           UNKNOWN



SELECT 1 num, SYSDATE dates FROM DUAL

UNION

SELECT 2 num, TO_DATE(NULL) dates FROM DUAL;



       NUM DATES

---------- ---------

         1 06-JAN-02

         2

Remember, you'll only encounter these problems of union compatibility when using literal NULL values in Oracle8i and earlier releases. The problems go away beginning with the Oracle9i Database release.