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.

Oracle MySQL
Ascending order NULLs last NULLs first
Descending order NULLs first NULLs last

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:

name
France
Singapore
NULL

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:

name
NULL
France
Singapore

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:

name value
NULL 2
France 3
France NULL
Singapore 1

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:

name
NULL
France
Singapore

More Information

About SQLines

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.

You could leave a comment if you were logged in.