5 Surprising Oracle SQL Behaviors That Very Few People Know

This article describes five(5) misconceptions about the behaviour of the popular Oracle database system. These misconceptions often lead to unexpected results that developers with SQL(Structured Query Language)experience may face when migrating to Oracle database. Make sure to read to the end as I can assure you, surprises await you but don’t worry, I’m sharing my experience so you don’t have to go through what I went through. I just want your programming experience to be successful and your programming skills advanced.

1. Tests on NULL values always return FALSE

Would you agree that combining the two requests below gives the Persons table?

SELECT * FROM Persons WHERE SALARY > 1000
SELECT * FROM Persons WHERE SALARY <= 1000

Well, if so, then you would be wrong. Or, more precisely, you would be wrong if the table were to contain a row where SALARY is NULL.

In fact, the conditions SALARY > 1000 and SALARY <= 1000, both implicitly exclude the rows where SALARY is NULL.

Oracle’s handling of NULL values in boolean expressions might seem confusing for beginners, but it’s actually pretty straight forward, and purely mechanical.

It’s fundamental knowledge and the rules are pretty simple. Take aside the two logical operators that are dedicated to testing whether a value is NULL or NOT. These are, respectively:

IS NULL
IS NOT NULL

And their behaviour is self-explanatory.

All the remaining logical operators will evaluate to FALSE if any of their operands is NULL. For example, all the below expressions are FALSE.

NULL = 1
NULL != 1
NULL > 1
NULL = "ABC"
NULL != "ABC"
NULL LIKE "Steve%"
NULL NOT LIKE "Steve%"
NULL = NULL

So, although one might consider that conditions above that are in bold are TRUE, Oracle still evaluates them all to FALSE. It is up to the developer of a query to adjust the conditions to make them return the required data.

For example, if we want the list of people where the salary is less than 1000 or NULL, the request would then become:

SELECT * FROM Persons WHERE SALARY < 1000 OR SALARY IS NULL.

Additionally, to completely cover the table Persons mentioned in the introduction, we would still need to add all the rows where SALARY is NULL. This we can do by using the following request:

SELECT * FROM Persons WHERE SALARY IS NULL

2. There are no empty strings in Oracle

The behaviour I am going to describe now — just like the others — may come as a surprise to software developers and non-Oracle database developers of any level of programming experience. A long time ago, I was trying to retrieve the list of users who didn’t have emails. My first attempt for getting this list was a simple query like:

Select * from Users where Email = '' //Nothing between the quotes

To my surprise, this query did not return any results so I attempted the one below, which wasn’t successful either.

Select * from Users where LENGTH(Email) = 0

At this point, I decided to check some users with no email directly in the table and found out that, surprisingly, they all had NULL inside the email field.

So, I reviewed my query and changed it as shown below:

Select * from Users where Email IS NULL

This time, bingo! I was relieved to see many rows returned by my query. Although a bit satisfied with my small achievement, I did not understand why the test on the length of the string did not work out, and why the strings were being represented by NULL instead of a simple empty string. The whole notion was counter-intuitive to even the most experienced developers.

The answer to the first question came quickly after checking the documentation. It turns out that Length(NULL) returns NULL instead of 0, the value that I was expecting.

The answer to the second questions did not come until a few months later when I was struggling to correctly express the condition ‘made out of spaces’.

My first bet was to use something like:

RTRIM(COL) = '' //Nothing between the quotes

but this was returning 0 rows, just like the more conservative version:

LENGTH(RTRIM(COL)) = 0

A long session of research followed, leading me to discover that Oracle does not support zero-length strings.

If we try to insert the literal empty string ‘’ in a column, the request would be executed without warnings but NULL would be inserted in the column. Moreover, a condition like COL = ‘’ is simply interpreted as if it was COL = NULL, which is always FALSE. Recall from earlier than comparisons to NULL always yield FALSE.

This can be verified quickly by running the below queries:

CREATE TABLE EMPTY_STRING ( Col varchar(255) );
Insert into EMPTY_STRING values( '' );
Select * from EMPTY_STRING;
Select * from EMPTY_STRING Where COL is NULL;
Select * from EMPTY_STRING Where COL = '';

One line with NULL

COL is NULL works as expected

Col = ‘’ returns no results

Moving back to our condition for detecting the columns made from spaces. Since zero-length strings do not exist in Oracle, RTRIM of a string made of spaces can’t return anything other than NULL. So, the correct test is:

RTRIM(COL) is NULL

3. Using aliases in order by or where clauses

One thing that I find frustrating is the need to repeat the definition of my columns inside Order by or Where clauses. Oracle never accepts requests like the ones below:

SELECT NAME, age , weight / ( height * height ) AS BMI FROM persons
ORDER BY BMI
SELECT NAME, age , weight / ( height * height ) AS BMI FROM persons
WHERE BMI > 25

That database design insists on having the definition of the column alias repeated as in the queries below:

SELECT NAME, age , weight / ( height * height ) AS BMI FROM persons
ORDER BY weight / ( height * height )
SELECT NAME, age , weight / ( height * height ) AS BMI FROM persons
WHERE weight / ( height * height ) > 25

You might think that I am being too picky, but remember that this is a very simple example.In real-life queries, we can have several long formulae that need to be repeated several times in the same query.

Fortunately, there is a better way to go about this. Let me show you the new syntax and then I will give you some explanations.

SELECT * FROM
(SELECT NAME, age, weight / (height * height) AS BMI FROM persons) 
WHERE BMI > 25
SELECT * FROM
(SELECT NAME, age, weight / (height * height) AS BMI FROM persons) 
ORDER BY BMI

Here I am using what is technically known as an inline view. So, I am - theoretically - asking Oracle to create a temporary table containing:

Select Name, Age, Weight/Height*Height as BMI from Persons

And then filtering or ordering on the columns of this temporary table (that clearly has a column named BMI).

As we can see, it’s an easy fix that is flexible enough to handle all scenarios. I believe that some caution should be exercised when the inline view is huge since it might slow down the execution of the request. But, based on my experience, it seems that the query optimizer manages most of the time to retrieve the data without actually creating the temporary table.

4.Using * in combination with selected columns

We all know that the asterisk can be used in SQL as a shortcut for all the columns in a table. So, the below request would clearly display all the columns of the table mytable:

Select * from mytable

One annoying problem in Oracle is that it does not accept the usage of the asterisk together with column names. So, for example, the below queries are not accepted by Oracle.

Select SelectedCol1, * from Mytable
Select SelectedCol1, SelectedCol2, * from Mytable

The good news is, there is a quick solution for this problem that consists of prefixing the asterisk symbol with the table name (or table alias name).

Select col1, Mytable.* from Mytable
Select col1, m.* from Mytable m

5. Getting the top N rows from a query

To get the first N rows from a query in Oracle, we use the pseudo-column rownum, which represents the row number of the returned row. Recall that Pseudo-columns are not actual columns in a table but they behave like columns.

So, if we want to get the first 3 rows, we can use :

Select * from mytable where rownum < 4

Rownum can also be included in the list of fields, allowing us to display the number of each row:

select rownum, MYTABLE.* from MYTABLE where rownum < 4

One should keep in mind that the filtering (Where clause) is executed before the sorting (Order By). So, for example:

Select Name, Grade from Students where rownum < 4 order by grade

will most likely return the sorted list of the first 3 students in the table — Not very useful.

The proper way to get the top N rows of a sorted query is to use an inline view as shown below:

Select * from
(Select Name, Grade from Students order by grade)
Where rownum < 4

Also, one should exercise some caution when filtering on rownum as the first row that breaks the condition on rownum will stop the execution of the query. The below conditions, for example, will yield no results since they fail on the first row of the table.

rownum > 1
rownum = 3

There is a way to get, say, the 3rd row, again using inline views.

Select * from
(Select rownum as lineNb, mytable.* from mytable where rownum < 4)
Where lineNb = 3

Although this article covered only 5 surprises, they may be more. However, I hope you have found these points helpful in your journey as an Oracle database developer. Stay tuned for more tips, pointers, and guidelines.

Thanks for reading. For more articles, visit my website boutroschalouhy.com