Database Reference
In-Depth Information
declare
l_some_variable varchar2(25);
begin
if ( some_condition )
then
l_some_variable := f( ... );
end if;
for x in ( select * from T where x = l_some_variable )
loop
...
The goal here was to find all of the rows in T where x was NULL if some condition was not met or where x equaled
a specific value if some condition was met.
The complaint was that, in Oracle, this query would return no data when L_SOME_VARIABLE was not set to a
specific value (when it was left as NULL ). In Sybase or SQL Server, this was not the case—the query would find the
rows where x was set to a NULL value. I see this on almost every conversion from Sybase or SQL Server to Oracle. SQL
is supposed to operate under tri-valued logic and Oracle implements NULL comparisons the way ANSI SQL requires
them to be implemented (where NULL signifies a state of unknown and not a value). Under those rules, comparing x to
a NULL is neither true nor false—it is, in fact, unknown . The following snippet shows what I mean:
EODA@ORA12CR1> select * from dual where null=null;
no rows selected
EODA@ORA12CR1> select * from dual where null <> null;
no rows selected
EODA@ORA12CR1> select * from dual where null is null;
D
-
X
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 (by default;
in current releases of SQL Server, the default behavior may be modified to reflect the ANSI standard). None of
the databases' processing is wrong —it is just different . And all of the databases are, in fact, ANSI compliant (ANSI
compliance does not mean you support 100% of the standard, not by a long shot, see the next section “Impact of
Standards” for details), 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 (if it did,
it would break thousands of existing legacy applications built on that database).
In this case, one solution to the problem is to write the query like this instead:
select *
from t
where ( x = l_some_variable OR (x is null and l_some_variable is NULL ))
However, this leads to another problem. In SQL Server, this query would use an index on x . This might not be the
case in Oracle since a B*Tree index (more on indexing techniques in the chapter on indexes) will not index an entirely
NULL entry. Hence, if you need to find NULL values, B*Tree indexes are not always useful.
 
Search WWH ::




Custom Search