The jOOQ User Manual SQL building Column expressions Window functions NULL treatment

previous : next

NULL treatment

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Some window functions may offer a special NULL treatment clause, which allows for excluding NULL values from being considered for the evaluation of the function.

These include:

• LEAD
• LAG
• FIRST_VALUE
• LAST_VALUE
• NTH_VALUE

Possible clause values include:

• IGNORE NULLS: NULL values are not considered for the evaluation of the function.
• RESPECT NULLS: The default.

An example illustrates the utility of this optional clause

SELECT
  id,
  amount,
  lead(amount) OVER (ORDER BY id) AS respect_nulls,
  lead(amount) IGNORE NULLS OVER (ORDER BY id) AS ignore_nulls
FROM (VALUES
  (1, 10.0),
  (2, 15.0),
  (3, 20.0),
  (4, null),
  (5, 30.0),
  (6, 35.0)
) AS t (id, amount)

The result being

+----+--------+---------------+--------------+
| ID | AMOUNT | RESPECT_NULLS | IGNORE_NULLS |
+----+--------+---------------+--------------+
|  1 |     10 |            15 |           15 |
|  2 |     15 |            20 |           20 |
|  3 |     20 |               |           30 | <-- difference here
|  4 |        |            30 |           30 |
|  5 |     30 |            35 |           35 |
|  6 |     35 |               |              |
+----+--------+---------------+--------------+

If you will, the IGNORE NULLS clause allows for skipping all rows containing NULL values until the next non-NULL value is found. Such a function may still return NULL if no next row is found in the window, e.g. the last row in the above example!

Dialect support

This example using jOOQ:

lead(BOOK.ID).ignoreNulls().over(orderBy(BOOK.ID))

Translates to the following dialect specific expressions:

BigQuery, DuckDB, Vertica

lead(BOOK.ID IGNORE NULLS) OVER (ORDER BY BOOK.ID)

DB2

lead(BOOK.ID, 'IGNORE NULLS') OVER (ORDER BY BOOK.ID)

Exasol, H2, Informix, Oracle, Redshift, SQLServer, Snowflake, Teradata, Trino

lead(BOOK.ID) IGNORE NULLS OVER (ORDER BY BOOK.ID)

ASE, Access, Aurora MySQL, Aurora Postgres, ClickHouse, CockroachDB, Derby, Firebird, HSQLDB, Hana, MariaDB, MemSQL, MySQL, Postgres, SQLDataWarehouse, SQLite, Sybase, YugabyteDB

/* UNSUPPORTED */

The jOOQ User Manual SQL building Column expressions Window functions NULL treatment

previous : next