Treatment of NULLs by Oracle & SQL Server

Oracle & SQL Server behave subtly differently when processing columns containing nulls. Consider the following series of SQL statements:

CREATE TABLE tempone (
creditlimit NUMBER(6)); -- Oracle
CREATE TABLE tempone (
creditlimit INT); -- SQL Server
INSERT INTO tempone VALUES (null);
INSERT INTO tempone VALUES (2000);
INSERT INTO tempone VALUES (1000);
SELECT COUNT(*), SUM(creditlimit),
AVG(creditlimit), MIN(creditlimit),
MAX(creditlimit)
FROM tempone;

Reassuringly the result of executing the last of the above statements is the same for both Oracle and SQL Server. It returns values of 3, 3000, 1500, 1000 and 2000 respectively. Note that the AVG function computes the arithmetic mean, which excludes from its considerations the missing (null) value, hence the result of the AVG function is 1500, not 1000.

However, the following statement produces different results:

SELECT creditlimit
FROM tempone
ORDER BY creditlimit;

In SQL Server, the order of the results is:

NULL
1000
2000

In Oracle, the order of the results is:

1000
2000
NULL

If you are developing software that will be installable on a range of DBMSs or migrating from one DBMS to another you should be careful when your code includes ORDER BYs on nullable columns.