NULL Values in ORDER BY - Informix to Oracle Migration

In Informix, when you sort columns in ORDER BY clause NULL values go first for ascending order, and last for descending order.

In Oracle, the default behavior is different, NULL values go last for ascending order, and first for descending order, but Oracle provides NULLS FIRST and NULLS LAST clauses in ORDER BY to specify sorting for NULL

In Informix, NULL values always go first in ascending order:

Informix:

  -- NULLs are always first in ASC order
  SELECT name
  FROM 
    (SELECT 'Washington' AS name FROM systables WHERE tabid = 1 UNION ALL
     SELECT CAST(NULL AS CHAR) FROM systables WHERE tabid = 1 UNION ALL
     SELECT 'Virginia' FROM systables WHERE tabid = 1) t
  ORDER BY name;

Result:

Oracle:

  -- NULLs are last in default ASC order
  SELECT name
  FROM 
    (SELECT 'Washington' AS name FROM dual UNION ALL
     SELECT NULL  FROM dual UNION ALL
     SELECT 'Virginia'  FROM dual) t
  ORDER BY name;

Result:

You can specify NULLs to go first by adding NULLS FIRST to the ORDER BY:

Oracle:

  -- NULLs are first now in ASC order
  SELECT name
  FROM 
    (SELECT 'Washington' AS name FROM dual UNION ALL
     SELECT NULL  FROM dual UNION ALL
     SELECT 'Virginia'  FROM dual) t
  ORDER BY name NULLS FIRST;

Result:

If ORDER BY contains multiple columns, you can specify NULLS FIRST or NULLS LAST for each column:

Oracle:

  SELECT name, value
  FROM 
    (SELECT 'Washington' AS name, 1 AS value FROM dual UNION ALL
     SELECT NULL, 3  FROM dual UNION ALL
     SELECT 'Virginia', 7  FROM dual UNION ALL
     SELECT 'Virginia', NULL FROM dual) t
  ORDER BY name NULLS FIRST, value DESC NULLS LAST;

Result:

• Informix to Oracle Migration Tools and Reference

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - April 2013.