---
layout: docu
title: Lambda Functions
---

> Deprecated DuckDB 1.3.0 deprecated the old lambda single arrow syntax (`x -> x + 1`)
> in favor of the Python-style syntax (`lambda x : x + 1`).
>
> DuckDB 1.3.0 also introduces a new setting to configure the lambda syntax.
>
> ```sql
> SET lambda_syntax = 'DEFAULT';
> SET lambda_syntax = 'ENABLE_SINGLE_ARROW';
> SET lambda_syntax = 'DISABLE_SINGLE_ARROW';
> ```
>
> Currently, `DEFAULT` enables both syntax styles, i.e.,
> the old single arrow syntax and the Python-style syntax.
>
> DuckDB 1.4.0 will be the last release supporting the single arrow syntax without explicitly enabling it.
>
> DuckDB 1.5.0 disables the single arrow syntax on default.
>
> DuckDB 1.6.0 removes the `lambda_syntax` flag and fully deprecates the single arrow syntax,
> so the old behavior will no longer be possible.

Lambda functions enable the use of more complex and flexible expressions in queries.
DuckDB supports several scalar functions that operate on [`LIST`s]({% link docs/preview/sql/data_types/list.md %}) and
accept lambda functions as parameters
in the form `lambda ⟨parameter1⟩, ⟨parameter2⟩, ... : ⟨expression⟩`{:.language-sql .highlight}.
If the lambda function has only one parameter, then the parentheses can be omitted.
The parameters can have any names.
For example, the following are all valid lambda functions:

* `lambda param : param > 1`{:.language-sql .highlight}
* `lambda s : contains(concat(s, 'DB'), 'duck')`{:.language-sql .highlight}
* `lambda acc, x : acc + x`{:.language-sql .highlight}

## Scalar Functions That Accept Lambda Functions

| Name | Description |
|:--|:-------|
| [`list_transform(list, lambda(x))`](#list_transformlist-lambdax) | Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function. See [`list_transform` examples](#list_transform-examples). |
| [`list_filter(list, lambda(x))`](#list_filterlist-lambdax) | Constructs a list from those elements of the input list for which the lambda function returns `true`. DuckDB must be able to cast the lambda function's return type to `BOOL`. The return type of `list_filter` is the same as the input list's. See [`list_filter` examples](#list_filter-examples). |
| [`list_reduce(list, lambda(x, y)[, initial_value])`](#list_reducelist-lambdax-y-initial_value) | Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional `initial_value` argument. See [`list_reduce` examples](#list_reduce-examples) or details. |

### `list_transform(list, lambda(x))`

<div class="nostroke_table"></div>

| **Description** | Returns a list that is the result of applying the lambda function to each element of the input list. The return type is defined by the return type of the lambda function. See [`list_transform` examples](#list_transform-examples). |
| **Example** | `list_transform([4, 5, 6], lambda x : x + 1)`{:.language-sql .highlight} |
| **Result** | `[5, 6, 7]` |
| **Aliases** | `array_transform`, `apply`, `list_apply`, `array_apply` |

### `list_filter(list, lambda(x))`

<div class="nostroke_table"></div>

| **Description** | Constructs a list from those elements of the input list for which the lambda function returns `true`. DuckDB must be able to cast the lambda function's return type to `BOOL`. The return type of `list_filter` is the same as the input list's. See [`list_filter` examples](#list_filter-examples). |
| **Example** | `list_filter([4, 5, 6], lambda x : x > 4)`{:.language-sql .highlight} |
| **Result** | `[5, 6]` |
| **Aliases** | `array_filter`, `filter` |

### `list_reduce(list, lambda(x, y)[, initial_value])`

<div class="nostroke_table"></div>

| **Description** | Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional `initial_value` argument. See [`list_reduce` examples](#list_reduce-examples) or details. |
| **Example** | `list_reduce([1, 2, 3], lambda x, y : x + y, 100)`{:.language-sql .highlight} |
| **Result** | `106` |
| **Aliases** | `array_reduce`, `reduce` |

## Nesting Lambda Functions

All scalar functions can be arbitrarily nested. For example, nested lambda functions to get all squares of even list elements:

```sql
SELECT list_transform(
        list_filter([0, 1, 2, 3, 4, 5], lambda x: x % 2 = 0),
        lambda y: y * y
    );
```

```text
[0, 4, 16]
```

Nested lambda function to add each element of the first list to the sum of the second list:

```sql
SELECT list_transform(
        [1, 2, 3],
        lambda x :
            list_reduce([4, 5, 6], lambda a, b: a + b) + x
    );
```

```text
[16, 17, 18]
```

## Scoping

Lambda functions confirm to scoping rules in the following order:

* inner lambda parameters
* outer lambda parameters
* column names
* macro parameters

```sql
CREATE TABLE tbl (x INTEGER);
INSERT INTO tbl VALUES (10);
SELECT list_apply(
            [1, 2],
            lambda x: list_apply([4], lambda x: x + tbl.x)[1] + x
    )
FROM tbl;
```

```text
[15, 16]
```

## Indexes as Parameters

All lambda functions accept an optional extra parameter that represents the index of the current element.
This is always the last parameter of the lambda function (e.g., `i` in `(x, i)`), and is 1-based (i.e., the first element has index 1).

Get all elements that are larger than their index:

```sql
SELECT list_filter([1, 3, 1, 5], lambda x, i: x > i);
```

```text
[3, 5]
```

## Examples

### `list_transform` Examples

Incrementing each list element by one:

```sql
SELECT list_transform([1, 2, NULL, 3], lambda x: x + 1);
```

```text
[2, 3, NULL, 4]
```

Transforming strings:

```sql
SELECT list_transform(['Duck', 'Goose', 'Sparrow'], lambda s: concat(s, 'DB'));
```

```text
[DuckDB, GooseDB, SparrowDB]
```

Combining lambda functions with other functions:

```sql
SELECT list_transform([5, NULL, 6], lambda x: coalesce(x, 0) + 1);
```

```text
[6, 1, 7]
```

### `list_filter` Examples

Filter out negative values:

```sql
SELECT list_filter([5, -6, NULL, 7], lambda x: x > 0);
```

```text
[5, 7]
```

Divisible by 2 and 5:

```sql
SELECT list_filter(
        list_filter([2, 4, 3, 1, 20, 10, 3, 30], lambda x: x % 2 = 0),
        lambda y: y % 5 = 0
    );
```

```text
[20, 10, 30]
```

In combination with `range(...)` to construct lists:

```sql
SELECT list_filter([1, 2, 3, 4], lambda x: x > #1) FROM range(4);
```

```text
[1, 2, 3, 4]
[2, 3, 4]
[3, 4]
[4]
```

### `list_reduce` Examples

Sum of all list elements:

```sql
SELECT list_reduce([1, 2, 3, 4], lambda acc, x: acc + x);
```

```text
10
```

Only add up list elements if they are greater than 2:

```sql
SELECT list_reduce(
        list_filter([1, 2, 3, 4], lambda x: x > 2),
        lambda acc, x: acc + x
    );
```

```text
7
```

Concat all list elements:

```sql
SELECT list_reduce(['DuckDB', 'is', 'awesome'], lambda acc, x: concat(acc, ' ', x));
```

```text
DuckDB is awesome
```

Concatenate elements with the index without an initial value:

```sql
SELECT list_reduce(
        ['a', 'b', 'c', 'd'],
        lambda x, y, i: x || ' - ' || CAST(i AS VARCHAR) || ' - ' || y
    );
```

```text
a - 2 - b - 3 - c - 4 - d
```

Concatenate elements with the index with an initial value:

```sql
SELECT list_reduce(
        ['a', 'b', 'c', 'd'],
        lambda x, y, i: x || ' - ' || CAST(i AS VARCHAR) || ' - ' || y, 'INITIAL'
    );
```

```text
INITIAL - 1 - a - 2 - b - 3 - c - 4 - d
```

## Limitations

Subqueries in lambda expressions are currently not supported.
For example:

```sql
SELECT list_apply([1, 2, 3], lambda x: (SELECT 42) + x);
```

```console
Binder Error:
subqueries in lambda expressions are not supported
```