How to do NULLS LAST in SQLite?

In SQLite, implementing NULLS LAST can be indirectly achieved by using a CASE statement within the ORDER BY clause. SQLite's default sorting behavior treats NULL values as the smallest, so they appear at the front when using ASC (ascending) sorting and at the back when using DESC (descending) sorting. If you want NULL values to appear at the end during ascending sorting, you need to customize the sorting logic.

Example:

Suppose we have a table called employees with two fields, name and salary, and we want to sort by salary in ascending order but have NULL values appear after all non-NULL values.

sql
SELECT name, salary
FROM employees
ORDER BY
  CASE WHEN salary IS NULL THEN 1 ELSE 0 END, -- First order by this expression to push NULLs to the end
  salary ASC; -- Then order by salary

In this example, the CASE statement generates a new column for sorting. When salary is NULL, this expression returns 1; otherwise, it returns 0. Thus, all non-NULL salary values (producing 0) will appear before NULL values (producing 1). Subsequently, for those non-NULL values, they are sorted in ascending order based on the actual salary values.

Using this method, even though SQLite does not directly support the NULLS LAST syntax, you can still achieve a similar effect.