SQL Reference

Table-Valued Functions

Overview
These functions output a table based on its defined arguments and inputs. Because they output a table, they only can appear in FROM clauses. 
Syntax
The format for <tableValuedFunction> is:
SQL
<tvfName> (<colName> | <literal>, …) [WITH <tvfCorrelationClause>, …] OVER (<fullSelect>, …)
<tvfName> (<colName> | <literal>, …) [WITH <tvfCorrelationClause>, …] OVER (<fullSelect>, …)
﻿
Where tvfName is the name of the table-valued function (heretofore known as a TVF), which must be a valid, unquoted identifier. The arguments to the TVF are the column names and literals provided in the first set of parentheses. The OVER clause specifies the input sets of the TVF. Each entry in each full select can be referenced and potentially output by the operation that the TVF executes. Like regular functions, each TVF will have restrictions on what its arguments are allowed to be. Each TVF can also have restrictions on how many different input sources (i.e. full selects in the OVER) there are, the number of entries in the input fullSelect list, and the columns selected or unselected by the input fullSelects.
The format for <tvfCorrelationClause> is:
SQL
identifier [AS] [<identifier> | (<identifier>, …)]?
identifier [AS] [<identifier> | (<identifier>, …)]?
﻿
These clauses are used to pass options to a TVF. See each TVF description for details.
Function
Syntax
Purpose
REPLACEMENT_JOIN
REPLACEMENT_JOIN(column compressed, column key, column value, string literal decompressedColName) OVER (SELECT compressed, [anything you want to pass through the join]…​ FROM compressed_table, SELECT key, value FROM lookup_table)
Replace the contents of a column, compressed, based on a lookup table. The elements in the compressed column that match with an element in the lookup table’s key column will be replaced with the corresponding value element in the matching (key, value) entry. If no match exists with an element, the element is replaced with null. The resulting replaced compressed column (i.e. the decompressed column) will be named decompressedColName. The output of the replacement_join table can contain other columns besides the compressed, decompressed, key, and value; you can output any columns you want from the first SELECT input. If the compressed column’s type is any ARRAY type, the innermost elements of the array will be replaced instead of the entire array. This means that the key type has to match the innermost element type of the compressed column, not the entire compressed column type. For example, if you have an ARRAY type ARRAY(INT), [1, 3, 5, 1], you could decompress it to ["cat", "kibble", "meow", "cat"]. You could also decompress ARRAY(ARRAY(ARRAY(INT))), [[[1]], []] to [[["cat"]], []].
UNNEST
UNNEST(column_name, …​) OVER (SELECT …​)
Expand each element in an input array out to an individual row. For example, unnesting an array column of type ARRAY(INT) with values [2, 6] yields two result rows, with integers 2 and 6. The values of the other columns in each input row are unchanged in each corresponding output row. Multiple array columns can be specified to unnest the specified arrays from each row in parallel.
﻿
This Table Valued Function accepts the following correlation clauses:
﻿
ORDINALITY [identifier] - Generate a new integer column that starts at 1 for each input row and increments by 1 for each output row. This corresponds to the index of each output element in the original array. The identifier controls the name of the ordinality column, which defaults to [original_column_name]_ord if not specified.
﻿
VALUE [identifier] - Rename the unnested column, which defaults to [original_column_name]_val if not specified.
﻿
NULL_INPUT [no] - When the input column array is empty or NULL, replace it with an array with one NULL element. This is useful for preserving the other contents of those input rows.
﻿
Per the SQL standard, this Table Valued Function can also be used in a select list. e.g., SELECT UNNEST(int_column) FROM …​[1] WHERE …​[2] is equivalent to SELECT * FROM unnest(int_column) OVER (…​[1]) WHERE …​[2].
Examples
These examples use the following data.
Text
a   b        c        d     e      f
--------------------------------------------
1   [1, 2]   [6, 9]   []    NULL   [[1, 2], [5], []]
2   [7, 5]   [4]      [3]   [8]    [[6], [9, 4, 7], [3, 8]]
a   b        c        d     e      f
--------------------------------------------
1   [1, 2]   [6, 9]   []    NULL   [[1, 2], [5], []]
2   [7, 5]   [4]      [3]   [8]    [[6], [9, 4, 7], [3, 8]]
﻿
SELECT List
Result
Notes
unnest(cast(NULL as T[])
unnest(T[]())
(column of type T, no rows)
Empty and NULL input arrays are equivalent because in both cases there are no values to unnest
a, unnest(cast(NULL as T[])
a, unnest({})
1  NULL
2  NULL
When specified in a SELECT list alongside other items, NULL_INPUT is on by default. Explicitly specifying NULL_INPUT NO suppresses this.
unnest(b)
1
2
7
5


unnest(b) WITH ORDINALITY
1  1
2  2
7  1
5  2


unnest(d)
3
Using NULL_INPUT would emit another row with NULL.
unnest(e)
8


unnest(f)
[1, 2]
[5]
[]
[6]
[9, 4, 7]
[3, 8]
UNNEST only unnests one layer
unnest(b), unnest(c)
1  6
1  9
2  6
2  9
7  4
5  4
Multiple UNNESTs in select list produces a per-row Cartesian product of all the unnested values
unnest(c), unnest(b)
6  1
6  2
9  1
9  2
4  7
4  5
reverse order case of above
unnest(b, c)
1  6
2  9
7  4
5  NULL
unnest arrays in parallel
unnest(b, d)
1  NULL
2  NULL
7  3
5  NULL


unnest(unnest(f))
1
2
5
6
9
4
7
3
8
multiple unnest
﻿
Notation is for brevity, must be written as SELECT unnest(f_val) FROM (SELECT unnest(f) …​
﻿

Function	Syntax	Purpose
REPLACEMENT_JOIN	REPLACEMENT_JOIN(column compressed, column key, column value, string literal decompressedColName) OVER (SELECT compressed, [anything you want to pass through the join]… FROM compressed_table, SELECT key, value FROM lookup_table)	Replace the contents of a column, compressed, based on a lookup table. The elements in the compressed column that match with an element in the lookup table’s key column will be replaced with the corresponding value element in the matching (key, value) entry. If no match exists with an element, the element is replaced with null. The resulting replaced compressed column (i.e. the decompressed column) will be named decompressedColName. The output of the replacement_join table can contain other columns besides the compressed, decompressed, key, and value; you can output any columns you want from the first SELECT input. If the compressed column’s type is any ARRAY type, the innermost elements of the array will be replaced instead of the entire array. This means that the key type has to match the innermost element type of the compressed column, not the entire compressed column type. For example, if you have an ARRAY type ARRAY(INT), [1, 3, 5, 1], you could decompress it to ["cat", "kibble", "meow", "cat"]. You could also decompress ARRAY(ARRAY(ARRAY(INT))), [[[1]], []] to [[["cat"]], []].
UNNEST	UNNEST(column_name, …) OVER (SELECT …)	Expand each element in an input array out to an individual row. For example, unnesting an array column of type ARRAY(INT) with values [2, 6] yields two result rows, with integers 2 and 6. The values of the other columns in each input row are unchanged in each corresponding output row. Multiple array columns can be specified to unnest the specified arrays from each row in parallel. This Table Valued Function accepts the following correlation clauses: ORDINALITY [identifier] - Generate a new integer column that starts at 1 for each input row and increments by 1 for each output row. This corresponds to the index of each output element in the original array. The identifier controls the name of the ordinality column, which defaults to [original_column_name]_ord if not specified. VALUE [identifier] - Rename the unnested column, which defaults to [original_column_name]_val if not specified. NULL_INPUT [no] - When the input column array is empty or NULL, replace it with an array with one NULL element. This is useful for preserving the other contents of those input rows. Per the SQL standard, this Table Valued Function can also be used in a select list. e.g., SELECT UNNEST(int_column) FROM …[1] WHERE …[2] is equivalent to SELECT * FROM unnest(int_column) OVER (…[1]) WHERE …[2].

SELECT List	Result	Notes
unnest(cast(NULL as T[]) unnest(T[]())	(column of type T, no rows)	Empty and NULL input arrays are equivalent because in both cases there are no values to unnest
a, unnest(cast(NULL as T[]) a, unnest({})	1 NULL 2 NULL	When specified in a SELECT list alongside other items, NULL_INPUT is on by default. Explicitly specifying NULL_INPUT NO suppresses this.
unnest(b)	1 2 7 5
unnest(b) WITH ORDINALITY	1 1 2 2 7 1 5 2
unnest(d)	3	Using NULL_INPUT would emit another row with NULL.
unnest(e)	8
unnest(f)	[1, 2] [5] [] [6] [9, 4, 7] [3, 8]	UNNEST only unnests one layer
unnest(b), unnest(c)	1 6 1 9 2 6 2 9 7 4 5 4	Multiple UNNESTs in select list produces a per-row Cartesian product of all the unnested values
unnest(c), unnest(b)	6 1 6 2 9 1 9 2 4 7 4 5	reverse order case of above
unnest(b, c)	1 6 2 9 7 4 5 NULL	unnest arrays in parallel
unnest(b, d)	1 NULL 2 NULL 7 3 5 NULL
unnest(unnest(f))	1 2 5 6 9 4 7 3 8	multiple unnest Notation is for brevity, must be written as SELECT unnest(f_val) FROM (SELECT unnest(f) …

Updated 21 Nov 2023

Did this page help you?

Sorted Aggregate Functions

Time Zone Functions