---
layout: docu
redirect_from:
- /docs/preview/sql/functions/lambda
- /docs/stable/sql/functions/lambda
title: Lambda Functions
---
DuckDB lambda functions (a.k.a. 'lambdas' or 'lambda expressions') are expressions that define an inline anonymous function.
Lambdas are passed as argument to certain [list functions]({% link docs/current/sql/functions/list.md %}), which evaluate the passed lambda expression against its elements.
The value resulting from the lambda's evaluation is then processed further by the list function to create its return value.
Lambdas may also be used in the [`COLUMNS` clause]({% link docs/current/sql/expressions/star.md %}#columns-lambda-function).
In that context, the lambda function acts as a filter: if it evaluates to `true`, the column is retained, and discarded otherwise.
`COLUMNS`-clause lambdas have a mandatory parameter that receives the column name. Optionally, a second parameter may be declared that receives the (1-based) column index.
The syntax for a lambda expression is:
```sql
lambda ⟨parameters⟩ : ⟨expression⟩
```
* `⟨parameters⟩`{:.language-sql .highlight} is a comma-separated list of parameter names. Parameters may be given any name, and their name may be referenced in the `⟨expression⟩`{:.language-sql .highlight}.
* The number of allowed and required parameters depends on the list function to which it is passed.
* There is always a mandatory parameter that is used by the list function to pass the current element of the list. This is typically the first parameter.
* There is always an optional parameter used by the list function to pass the (1-based) index of the current element. This is always the last parameter.
* The expression must be a scalar expression. Additionally, it cannot contain a subquery, table function, and the like.
* The expression may contain macro invocations. However, only scalar macros are allowed, and only if they do not rely on a subquery.
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}
Lambda functions enable the use of more complex and flexible expressions in queries.
## Scalar Functions That Accept Lambda Functions
| Function | Description |
|:--|:-------|
| [`apply(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. |
| [`array_apply(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. |
| [`array_filter(list, lambda(x))`](#list_filterlist-lambdax) | Alias for `list_filter`. |
| [`array_reduce(list, lambda(x,y)[, initial_value])`](#list_reducelist-lambdaxy-initial_value) | Alias for `list_reduce`. |
| [`array_transform(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. |
| [`filter(list, lambda(x))`](#list_filterlist-lambdax) | Alias for `list_filter`. |
| [`list_apply(list, lambda(x))`](#list_transformlist-lambdax) | Alias for `list_transform`. |
| [`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]({% link docs/current/sql/functions/lambda.md %}#list_filter-examples). |
| [`list_reduce(list, lambda(x,y)[, initial_value])`](#list_reducelist-lambdaxy-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]({% link docs/current/sql/functions/lambda.md %}#list_reduce-examples). |
| [`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]({% link docs/current/sql/functions/lambda.md %}#list_transform-examples). |
| [`reduce(list, lambda(x,y)[, initial_value])`](#list_reducelist-lambdaxy-initial_value) | Alias for `list_reduce`. |
#### `list_filter(list, lambda(x))`
| **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]({% link docs/current/sql/functions/lambda.md %}#list_filter-examples). |
| **Example** | `list_filter([3, 4, 5], lambda x : x > 4)` |
| **Result** | `[5]` |
| **Aliases** | `array_filter`, `filter` |
#### `list_reduce(list, lambda(x,y)[, initial_value])`
| **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]({% link docs/current/sql/functions/lambda.md %}#list_reduce-examples). |
| **Example** | `list_reduce([1, 2, 3], lambda x, y : x + y)` |
| **Result** | `6` |
| **Aliases** | `array_reduce`, `reduce` |
#### `list_transform(list, lambda(x))`
| **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]({% link docs/current/sql/functions/lambda.md %}#list_transform-examples). |
| **Example** | `list_transform([1, 2, 3], lambda x : x + 1)` |
| **Result** | `[2, 3, 4]` |
| **Aliases** | `apply`, `array_apply`, `array_transform`, `list_apply` |
## 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 conform 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
```
2) The expression can contain macro calls, but only if the macro is a scalar macro. In addition, the macro must not contain a subquery or rely on another macro that contains a subquery.
3) lambda functions must be defined inline as argument to the list function. You cannot return a lambda function from a macro (but you can invoke a macro in the lambda's expression).
## Deprecated Syntax
> Deprecated DuckDB v1.3 deprecated the old lambda single arrow syntax (`x -> x + 1`)
> in favor of the Python-style syntax (`lambda x : x + 1`).
>
> DuckDB v1.3 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 v1.5 is the last release supporting the single arrow syntax without explicitly enabling it.
>
> DuckDB v2.0 will disable the single arrow syntax by default.
>
> DuckDB v2.1 will remove the `lambda_syntax` flag and fully deprecates the single arrow syntax,
> so the old behavior will no longer be possible.