Order by and nulls in SQL

Now and then you will have an ORDER BY clause in a SELECT over a column which can have NULL values. The order where the NULL values will be placed depends on the DMBS; by default Oracle will place NULLs at the end for ascending sort and at the beginning for descending sort.

However many databases (including Oracle and PostgreSQL, but excluding SQL Server, SQLite and MySQL) allow you to use the standard SQL clause NULLS FIRST or NULLS LAST to override this behavior, or for portability:

SELECT *
  FROM myTable
 ORDER BY myField NULLS FIRST

If your database doesn’t support this clause, you can use something like this to achieve the same result:

SELECT *
  FROM myTable
 ORDER BY (CASE WHEN myField IS NULL THEN 0 ELSE 1 END), myField