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, EXASOL, H2, INFORMIX, ORACLE, REDSHIFT, SNOWFLAKE, SQLSERVER, 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)

-- ACCESS, ASE, AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, DERBY, FIREBIRD, HANA, HSQLDB, MARIADB, MEMSQL, MYSQL, 
-- POSTGRES, SQLDATAWAREHOUSE, SQLITE, SYBASE, YUGABYTEDB
/* UNSUPPORTED */

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

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

previous : next