Introduction
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 treated 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. Here is a simple query to verify that:
SELECT 'empty string is null' FROM dual WHERE '' IS NULL;
Another Gotcha (1a) – A bug in SQL 1 above
The above example had a semantic bug. If the requirement was to check that the dept_nbr was not null and it was not empty, then the AND condition would be incorrect. How can same field be NULL AND something else (here null string (")) at the same time? The original developer must have meant an OR condition there. But, luckily, Oracle treats NULL and empty string as the same, so the SQL worked for many years.
The gotcha here is that the original SQL 1 (with the AND condition)
would have failed to retrieve any rows, if not for Oracle treating ''
(empty string) as NULL.
In any case, if we wanted to keep both the conditions, it must be rewritten with OR instead of AND as shown below:
SELECT * from employee WHERE NOT((employee.dept_nbr IS NULL) OR (employee.dept_nbr = '') );
SELECT * from employee WHERE (NOT(employee.dept_nbr IS NULL) AND NOT(employee.dept_nbr = '') );
This is what you would need to do in T-SQL, for e.g., to make sure the column is not NULL and is not empty either. See here for an example. However, since both the clauses are checking for the same thing in Oracle, the above SQL can be simplified as:
SELECT * from employee WHERE employee.dept_nbr IS NOT NULL;
(Couple of side notes: The above condition cannot be written as dept_nbr != '' (result=no rows), nor can you write dept_nbr IS '' (syntax error) though there are the same).
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?
Oracle Select "IS NULL" | 
Select equal to NULL results in "no rows found" |
If you are coming from 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 values 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 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 <a href="http://www.adp-gmbh.ch/ora/misc/dual.html">dual</a>;
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;
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
- http://psoug.org/reference/null.html
- http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null
- http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17320984423926
- http://www.sqlines.com/oracle/string_concat
- http://www.oracle-base.com/articles/misc/null-related-functions.php
- http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
Filed under: CodeProject, Oracle
Tagged: Oracle, SQL
