ORDER BY NULLS FIRST or LAST - Oracle to MySQL Migration

Oracle allows you to specify how NULL values are ordered by ORDER BY clause. MySQL does not support this so queries can return different results as NULLs are last in Oracle and first in MySQL by default.

Consider sample data:

  CREATE TABLE colors (name VARCHAR(30), category CHAR(1));
 
  INSERT INTO colors VALUES ('Green', 'G');
  INSERT INTO colors VALUES ('Black', NULL);
  INSERT INTO colors VALUES ('Red', 'R');

Oracle:

  -- Default ordering, NULLs are last
  SELECT name, category FROM colors ORDER BY category;

Result:

MySQL:

  -- Default ordering, NULLs are first (!)
  SELECT name, category FROM colors ORDER BY category;

Result:

If you use DESC ordering then NULLs are first by default in Oracle, and last in MySQL:

Oracle:

  -- DESC order, NULLs are first by default now
  SELECT name, category FROM colors ORDER BY category DESC;

Result:

MySQL:

  -- DESC order, NULLs are last now (!)
  SELECT name, category FROM colors ORDER BY category DESC;

Result:

In Oracle you can specify how NULLs are ordered:

Oracle:

  -- NULLs are first
  SELECT name, category FROM colors ORDER BY category NULLS FIRST;
 
  -- NULLs are last
  SELECT name, category FROM colors ORDER BY category NULLS LAST;

MySQL does not support these clauses:

MySQL:

  SELECT name, category FROM colors ORDER BY category NULLS FIRST;
  # ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your 
  # MySQL server version for the right syntax to use near 'NULLS FIRST' at line 1
 
  SELECT name, category FROM colors ORDER BY category NULLS LAST;
  # ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your 
  # MySQL server version for the right syntax to use near 'NULLS LAST' at line 1

For more information, see Oracle to MySQL Migration.