Power to Build (Software): Gotcha

NULL essentially points to an "undefined" value in the database. It helps to conserve space in database columns. It's useful when we want to store "nothing" in a column. NULL is defined in ANSI, so all databases including ORACLE implement them. But, ORACLE differs from ANSI standards in dealing with NULLs. Some of these could be real gotchas for developers coming from other databases.

Gotcha 1 - Empty String is the same as NULL in Oracle SQL

In case you didn't know, empty String (also known as null string)('') and NULL are considered the same in Oracle. This is not ANSI standard and is a real gotcha for developers coming from other databases, where they are different.

Here is a SQL I found in one of the programs I am supporting:

SELECT * FROM employee WHERE NOT((employee.dept_nbr IS NULL) AND (employee.dept_nbr = '') );

In Oracle, the above Where condition is *not* really checking for 2 different things. Seems to me, the developer wanted to make sure it was not empty and it was not null either. This is because, he/she didn't realize Null String is essentially same as NULL in Oracle.

When in doubt, I always try a simpler query than original. Oracle system tables and the built in table DUAL come in handy for those queries. Here is a sample query that shows empty string is Null.

SELECT 'empty string is null' FROM dual WHERE '' IS NULL;

Another Gotcha - a bug in the SQL above

The above example had another bug. if the NULL was not the same as '', the above query would have failed completely!! (How can same field be NULL AND something else (here null string ('')) at the same time?). The above condition must have been

SELECT * from employee WHERE NOT((employee.dept_nbr IS NULL) OR (employee.dept_nbr = '') );

-- OR even below SQL:

SELECT * from employee WHERE (NOT(employee.dept_nbr IS NULL) AND NOT(employee.dept_nbr = '') );

Gotcha 2 - Checking for NULL

Did you notice "IS NULL" there? When you check for a value being NULL, you cannot use equal to (=). You have to use IS or IS NOT. This is yet another gotcha for many new developers to Oracle. Strange thing is Oracle will not complain if you entered '' = NULL in the above SQL. It simply won't find any rows!! Who better to explain this than Ask Tom?

[caption id="attachment_585" align="alignleft" width="329"]

Oracle Select "IS NULL"[/caption]

[caption id="attachment_587" align="aligncenter" width="412"]

Select equal to NULL results in "no rows found"[/caption]

If you are coming other databases, this may be a surprise to you (SQL Server for e.g., allowed = NULL check). It's a real gotcha in Oracle, because Oracle won't complain if you said "= NULL", but the query may not work as expected. A query may return less number of rows than expected, because of NULLs in some fields in the where condition. For e.g., below SQL will return only 2 rows (I expected the row with NULL to show as well).

SELECT emp_nbr, dept_nbr, first_name || ' ' || last_name || '(' || dept_nbr || ')' AS employee
FROM employee WHERE employee.dept_nbr IN (NULL, 100. 200);

The above WHERE condition must be written as,

WHERE employee.dept_nbr IS NULL OR employee.dept_nbr IN (100, 200);

Another way of doing this is to use the NVL function in Oracle. NVL translates NULL value to the value passed in.

WHERE NVL(employee.dept_nbr, 0) IN (0, 100, 200);

Here NULL is translated to 0 and this we can check in equality or IN condition.

Gotcha-3 Concatenating NULL (Empty String)

When you concatenate text fields together, if any of the value is NULL, the result will *NOT* be NULL in Oracle. In other databases (for e.g., SQL Server, mysql), String + NULL results in NULL. In Oracle, only NULL + NULL results in a NULL.

SELECT emp_nbr, dept_nbr, first_name || ' ' || last_name || '(' || dept_nbr || ')' AS employee
FROM employee WHERE (employee.dept_nbr IS NULL);

In this case, if you expected NULL, you are in for surprise. The concatenation actually works. Only the NULL values will be missing. (For e.g., this will return Sam Varadar() based on the sample data in the screenshots above). Though, Oracle is not promising this in the future versions. See here.

Gotcha-4 Nulls and Indexes

Another hidden gotcha in this is that when we a query uses an index, we are implicitly applying Equality checks. If a column that is in an index has NULL values in some rows, then those rows will not be indexed and thus won't be picked up!

This also means a primary key column cannot have NULL values. This is because a primary key column value identifies each row uniquely and thus they have to be in the primary index on the table.

Other NULLs

We saw above, concatenation of strings works even if one of the values is NULL. This is because, a NULL is also considered an Empty String. Other operations may not be as kind:

For e.g., Adding NULL to a Number will result in NULL.

SELECT 1 + null FROM dual;

In PL/SQL NULL is a NULL statement - a statement that does nothing. It is often needed in control blocks, like if-then-else where we want to leave the if or else part empty, but PL/SQL syntax doesn't allow this.

IF (dept_nbr = 100) THEN
    dbms_output.put_line('Inside IF block');
ELSE
    NULL; -- Do nothing
END IF;

When sorting a query result using ORDER BY, you can sort rows with NULLs to come FIRST or LAST (default).

SELECT * FROM employee ORDER BY dept_nbr DESC NULLS FIRST;

Functions to deal with NULLs

Because of the unpredictable behavior of queries around NULL values, it's often better to translate NULL into something meaningful, so equality (or inequality) tests can be done without worrying about NULLs.

We showed the use of NVL above. It translates NULL into a more meaningful value, 0. NVL2 is a similar function that returns one value for NULL and another for Non-NULL values. COALESCE is another function that could be passed in a series of comma separated values and it will return the first non-NULL value in the list.

These functions can also be used while creating index on a column that may have NULLs and thus help with performance also. See here for more on this.

Other functions include NULLIF, DECODE etc. See here for a nice discussion of the functions related to NULL.

References