This can be confusing the first time you see it — it proves
that, in Oracle, Null is neither equal to nor not equal to Null.
SQL Server, by default, does not do it that way: in SQL Server
and sybase, Null is equal to Null. Neither Oracle's, sybase nor
SQL Server's SQL processing is wrong — they are just
different. Both databases are in fact ANSI compliant
databases but they still work differently. There are ambiguities,
backward compatibility issues, and so on, to be overcome. For
example, SQL Server supports the ANSI method of Null comparison,
just not by default (it would break thousands of existing legacy
applications built on that database).
In this case, one solution to the problem was to write the query
like this instead:
where ( x = l_some_variable OR
(x is null and
l_some_variable is NULL ))
[The highlighted lines above are one line. They have been split for
However, this leads to another problem. In SQL Server, this query
would have used an index on x. This is not the case in Oracle
since a B*Tree index (more on indexing techniques in Chapter 7)
will not index an entirely Null entry. Hence, if you need to find
Null values, B*Tree indexes are not very useful.
What we did in this case, in order to minimize impact on the
code, was to assign X some value that it could never in reality
assume. Here, X, by definition, was a positive number — so
we chose the number —1. Thus, the query became:
where nvl(x,-1) = nvl(l_some_variable,-1)
And we created a function-based index:
create index t_idx on t( nvl(x,-1) );
With minimal change, we achieved the same end result. The
important points to recognize from this are that:
- Databases are different. Experience in one will in part carry
over to another but you must be ready for some fundamental
differences as well as some very minor differences.
- Minor differences (such as treatment of Nulls) can have as
big an impact as fundamental differences (such as concurrency
- Being aware of the database and how it works and how its
features are implemented is the only way to overcome these
Developers frequently ask me (usually more than once a day) how
to do something specific in the database. For example, they will
ask the question 'How do I create a temporary table in a stored
procedure?' I do not answer such questions with a direct answer
— I always respond with a question: 'Why do you want to do
that?. Many times, the answer will come back: 'In SQL Server we
created temporary tables in our stored procedures and we need to
do this in Oracle.' That is what I expected to hear. My response,
then, is easy — 'you do not want to create temporary tables
in a stored procedure in Oracle (you only think you do).' That
would, in fact, be a very bad thing to do in Oracle. If you
created the tables in a stored procedure in Oracle you would find
- Doing DDL is a scalability inhibitor.
- Doing DDL constantly is not fast.
- Doing DDL commits your transaction.
- You would have to use Dynamic SQL in all of your stored
procedures in order to access this table — no static SQL.
- Dynamic SQL in PL/SQL is not as fast or as optimized as
The bottom line is that you don't want to do it exactly as you
did it in SQL Server (if you even need the temporary table in
Oracle at all). You want to do things as they are best done in
Oracle. Just as if you were going the other way from Oracle to
SQL Server, you would not want to create a single table for all
users to share for temporary data (that is how Oracle does it).
That would limit scalability and concurrency in those other
databases. All databases are not created equal — they are
all very different.