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!

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

References to this page

Feedback

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

The jOOQ Logo