Database consistency with SQL Order By and nulls

Question

I have a column in my database (a flag) with type varchar(1) that is populated either Y or NULL (this is how it is, not in my control).

In SQL Server, doing an ascending order by query, NULL is ordered at the top. Should this behaviour be consistent for Oracle and DB2?

If, instead I have a COALESCE on the column to ensure it is not null in the query, am I likely to hit any performance issues (due to table scans and the like)?

EDIT

The query needs to be consistent over all 3 databases, otherwise I will have to handle it in code, hence my thinking of using the COALESCE function

EDIT

I chose Pax as the answer, as it dealt with both parts of the question and gave a helpful workaround, however, thanks to me.yahoo.com/a/P4tXrx for the link to here

Answer 1

In oracle you can do this:

ORDER BY value NULLS FIRST

or

ORDER BY value NULLS LAST

Try it in SQL Server

Answer 2

I know for a fact that DB2 Express and DB2 (at least up to v8) does not support the NULLS FIRST clause.

If you want a portable solution, you may have to opt for something like:

select * from tbl where fld is null
    union all select * from tbl where fld is not null

I think the result of the union (at least in DB2, you'll need to check the others) is guaranteed to be ordered correctly.

The coalesce will have performance implications since you're running a function for every row returned. However, it depends on the number of rows in the database.

You may well have to resort to doing two queries in code into two different record sets, then processing them in order.

EDIT: I've just checked the SQL standard and it's not guaranteed that the queries joined with a UNION ALL are sequenced; they might be inter-mixed. So it looks like you may have to resort to code running two different queries as mentioned above.

Answer 3

In SQL Server, doing an ascending order by query, NULL is ordered at the top. Should this behaviour be consistent for Oracle and DB2?

Apparently this is a relative newcomer to the standard.

The SQL standard's core functionality does not explicitly define a default sort order for Nulls. With the SQL:2003 extension T611, "Elementary OLAP operations", nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.