NULLS FIRST and NULLS LAST Fail on MySQL and MariaDB
ORDER BY shipped_at NULLS LAST is the tidy way to put unshipped orders at the end on PostgreSQL and SQLite. MySQL and MariaDB have no such syntax and refuse the query.
SQLite added them in version 3.30. MySQL and MariaDB have no syntax for choosing where NULL sorts.
What Each Engine Answered
On a table made with
CREATE TABLE t (x INTEGER);
INSERT INTO t VALUES (1), (NULL), (2);
the example
SELECT x FROM t ORDER BY x NULLS LAST;
gave these answers:
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 1; 2; NULL |
| PostgreSQL 16.15 | 1; 2; NULL |
| MySQL 8.0.46 | error: You have an error in your SQL syntax ... near 'NULLS LAST' at line 1 |
| MariaDB 10.11.14 | error: You have an error in your SQL syntax ... near 'NULLS LAST' at line 1 |
What to Write Instead
This form answers the same on every engine:
SELECT x FROM t ORDER BY x IS NULL, x;
| Engine | Answer |
|---|---|
| SQLite 3.45.1 | 1; 2; NULL |
| PostgreSQL 16.15 | 1; 2; NULL |
| MySQL 8.0.46 | 1; 2; NULL |
| MariaDB 10.11.14 | 1; 2; NULL |
Sort on x IS NULL first: ORDER BY x IS NULL, x for NULLS LAST and ORDER BY x IS NOT NULL, x for NULLS FIRST.
What BareSQL Portable Reports
Saved as example.sql, the example gets this from baresql lint --all:
$ baresql lint --all example.sql
example.sql:3:28: error: NULLS FIRST and NULLS LAST work on SQLite and PostgreSQL; MySQL and MariaDB refuse them. [nulls-first-last]
NULLS LAST
Sort on x IS NULL first: ORDER BY x IS NULL, x for NULLS LAST and ORDER BY x IS NOT NULL, x for NULLS FIRST.
1 problem: 1 error.
baresql rules nulls-first-last prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.