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

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:

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, Exasol, H2, Informix, Oracle, Redshift, SQLServer, Snowflake, Teradata, Trino

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

DB2

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

DuckDB, Vertica

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

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

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo