Clever Geek Handbook

Reconciling Database with SQL Order By and Nulls

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

In SQL Server, performing ascending on request, NULLs are ordered at the top. Should this behavior be consistent across Oracle and DB2?

If instead I have a COALESCE on a column to make sure it is not null in the query, can I hit any performance issues (from table scans, etc.)?

EDIT

The request must be consistent across all three databases, otherwise I will have to handle it in code, so I am thinking about using the COALESCE function

EDIT

I chose Pax as my answer as it covered both parts of the question and provided a useful workaround, however, thanks to me.yahoo.com/a/P4tXrx for linking to here

0


source to share


3 answers


I know that DB2 Express and DB2 (at least up to v8) do not support NULLS FIRST

.

If you want a portable solution, you might need something like:

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

I think that the result of the join (at least in DB2, you will need to check the rest) is guaranteed to be correctly ordered.



The merge will have performance implications since you use a function for each returned row. However, this depends on the number of rows in the database.

You may have to resort to running two queries in your code for two different recordsets and then process them in order.

EDIT: I just checked the SQL standard and did not guarantee that the queries associated with UNION ALL

are ordered; they can be mixed. So it looks like you might have to resort to code with two different requests as above.

+3


source


In SQL Server, performing ascending on request, NULLs are ordered at the top. Should this behavior be consistent across Oracle and DB2?

This appears to be a relative newcomer to the standard .



Standard SQL engine functionality does not explicitly define the default sort order for Nulls. With SQL Extension: 2003 T611 "Elementary OLAP Operations", zeros can be sorted before or after all data values ​​using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. However, not all DBMS vendors implement this functionality. Vendors who do not implement this functionality can specify different Null sorting methods in the DBMS.

+1


source


In oracle, you can do this:

ORDER BY value NULLS FIRST

or

ORDER BY value NULLS LAST

Try it in SQL Server

0


source






More articles:


All Articles