Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
Ordering and NULLS
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A few databases support the SQL standard "null ordering" clause in sort specification lists, to define whether NULL
values should come first or last in an ordered result.
SELECT AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME FROM AUTHOR ORDER BY LAST_NAME ASC, FIRST_NAME ASC NULLS LAST
create.select( AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) .from(AUTHOR) .orderBy(AUTHOR.LAST_NAME.asc(), AUTHOR.FIRST_NAME.asc().nullsLast()) .fetch();
If your database doesn't support this syntax, jOOQ emulates it using a CASE expression
Dialect support
This example using jOOQ:
select(AUTHOR.FIRST_NAME).from(AUTHOR).orderBy(AUTHOR.FIRST_NAME.asc().nullsLast())
Translates to the following dialect specific expressions:
Access, SQLServer
SELECT AUTHOR.FIRST_NAME FROM AUTHOR ORDER BY iif(AUTHOR.FIRST_NAME IS NOT NULL, 0, 1), AUTHOR.FIRST_NAME ASC
ASE, Aurora MySQL, MemSQL, MySQL, SQLDataWarehouse, Sybase
SELECT AUTHOR.FIRST_NAME FROM AUTHOR ORDER BY CASE WHEN AUTHOR.FIRST_NAME IS NOT NULL THEN 0 ELSE 1 END, AUTHOR.FIRST_NAME ASC
Aurora Postgres, BigQuery, ClickHouse, CockroachDB, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Hana, Informix, Oracle, Postgres, SQLite, Snowflake, Teradata, Trino, YugabyteDB
SELECT AUTHOR.FIRST_NAME FROM AUTHOR ORDER BY AUTHOR.FIRST_NAME ASC NULLS LAST
DB2, MariaDB, Redshift, Vertica
SELECT AUTHOR.FIRST_NAME FROM AUTHOR ORDER BY nvl2(AUTHOR.FIRST_NAME, 0, 1), AUTHOR.FIRST_NAME ASC
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!