Order of NULL in SQL result

This article explains the order of the NULL value in the ORDER BY clause in sql, with reference to the official document for each dialect.

The default behavior

The following SQL dialects consider NULL as the largest value (the last position in ascending order of any value type): PostgreSQL, Oracle.

In contrast, these SQL dialects treat the NULL value as smallest (the first value in ascending order of any value type): SQLite, MySQL, SQL Server.

The official document

PostgreSQL's query order.

By default, null values sort as if larger than any non-null value

Oracle document: (search for the following paragraph)

If the null ordering is not specified then the handling of the null values is:
* NULLS LAST if the sort is ASC
* NULLS FIRST if the sort is DESC

SQLite document:

SQLite considers NULL values to be smaller than any other values for sorting purposes

MySQL document:

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

SQL Server document:

Null values are treated as the lowest possible values.

Change the default behavior

PostgreSQL, Oracle, SQLite support modifying the order of the NULL value in the ORDER BY clause by adding NULLS FIRST/LAST to the order by clause.
NULLS FIRST/LAST specifies the order of NULL values regardless of the sorting order.

For example: with ORDER BY "subscribedAt" ASC NULLS FIRST/ORDER BY "subscribedAt" DESC NULLS FIRST, record with subscribedAt being NULL always come first.

Note: currently, MySQL, SQL Server do not support this feature.

Workaround with MySQL, SQL Server

There are various ways to achieve the required sorting order.

With reverse operator

The trick: minus of NULL is still a NULL, NOT of NULl is still a NULL.

For values of types that can be applied with the reverse operator such as number, date, or boolean, reserving the order (DESC to ASC, vice versa), and adding the reverse operator to the sorted value swap NULL values' order.

For example, change ORDER BY cnt ASC to ORDER BY -cnt DESC.

Change ORDER BY "isSubscribed" DESC to ORDER BY NOT "isSubscribed" ASC.

Sort the NULL value first

Use multiple sorts to sort the NULL value first.

For example, ORDER BY cnt IS NULL DESC, cnt DESC.

Note: cnt IS NULL is simply a boolean value. For boolean values, True/False is equivalent to 1/0, respectively when sorting.

Use COALESCE function

COALESCE(val_1, val_2, ..., val_n) returns the first non-null value. With COALESCE, you can provide a default value if the sorted expression is NULL.

For example, ORDER BY COALESCE(cnt, 0) DESC.

Note: This workaround is very different for all the above-mentioned ways, it is even different from the NULLS LAST/FIRST clause. Because it treats NULL and the default value in the same order, while in other methods, the null value always has a strictly higher or lower value than any other value.

Simpler approaches in specific use cases

Similar to the effect of the method using the COALESCE function, in some cases, you may want to make the NULL value have the same order with some default value.

If the value's type is boolean, you can simply use ORDER BY "isSubscribed" IS TRUE DESC. This will make False and NULL same order.

If you need a more specific complex logic, you can replace the expression in the order by clause. For example: ORDER BY (CASE WHEN val IS NULL then 1 WHEN val IS 1 THEN 2 ELSE 3) DESC (parenthesis are optional).

If you need exactly this strict order true, null, false, then use ORDER BY COALESCE(val::INTEGER * 2, 1) DESC.

Knex usage

Because knex supports many dialects (including MySQl, SQL Server), it does not support NULLS LAST/FIRST clause natively.

.orderBy('col', 'asc', 'first') generates ORDER BY col IS NULL ASC.

To mimic the NULL priority clause, you need to use multiple order by:

queryBuilder.orderBy([
    {column: 'col', order: 'ASC', nulls: 'first'},
    {column: 'col', order: 'ASC'}
])