NULL Values in ORDER BY - Oracle to MySQL Migration

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

In MySQL, the behavior is different, NULL values go first for ascending order, and last for descending order.

Note that Oracle provides NULLS FIRST and NULLS LAST clauses in ORDER BY to specify sorting for NULLs.

Oracle:

  -- NULLs are last in default ASC order
  SELECT name
  FROM 
    (SELECT 'Singapore' AS name FROM dual UNION ALL
     SELECT NULL  FROM dual UNION ALL
     SELECT 'France'  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 'Singapore' AS name FROM dual UNION ALL
     SELECT NULL  FROM dual UNION ALL
     SELECT 'France'  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 'Singapore' AS name, 1 AS value FROM dual UNION ALL
     SELECT NULL, 2  FROM dual UNION ALL
     SELECT 'France', 3  FROM dual UNION ALL
     SELECT 'France', NULL FROM dual) t
  ORDER BY name NULLS FIRST, value DESC NULLS LAST;

Result:

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

MySQL:

  -- NULLs are always first in ASC order
  SELECT name
  FROM 
    (SELECT 'Singapore' AS name UNION ALL
     SELECT NULL UNION ALL
     SELECT 'France') t
  ORDER BY name;

Result:

• Oracle to MySQL 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.