Click here to Skip to main content
15,867,453 members
Articles / Oracle

Gotcha – Oracle: Null

Rate me:
Please Sign up or sign in to vote.
4.38/5 (5 votes)
1 Jan 2014CPOL5 min read 12.6K   2   2
Oracle: Null gotcha

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:

  1. SQL
    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:

SQL
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:

SQL
-- (Note: these 2 SQLs are identical; see the truth table example in comments)
SQL
SELECT * from employee WHERE NOT((employee.dept_nbr IS NULL) OR (employee.dept_nbr = '') );
-- which can also be written as, (see the truth table example in comments)
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:

SQL
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?

Image 1

Oracle Select "IS NULL"

Image 2

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).

SQL
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:

SQL
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.

SQL
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.

SQL
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.

SQL
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.

SQL
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).

SQL
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

  1. http://psoug.org/reference/null.html
  2. http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null
  3. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17320984423926
  4. http://www.sqlines.com/oracle/string_concat
  5. http://www.oracle-base.com/articles/misc/null-related-functions.php
  6. http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

Filed under: CodeProject, Oracle
Tagged: Oracle, SQL
Image 3 Image 4 Image 5 Image 6 Image 7 Image 8 Image 9

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) City of Los Angeles
United States United States
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.

I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.

The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!

Comments and Discussions

 
QuestionAnother Gotcha – a bug in the SQL above is incorrect Pin
Member 100926782-Jan-14 7:26
Member 100926782-Jan-14 7:26 
GeneralRe: Another Gotcha – a bug in the SQL above is incorrect Pin
Sam Varadarajan2-Jan-14 9:00
professionalSam Varadarajan2-Jan-14 9:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.