union operator
This article describes union operator.
Takes two or more tables and returns the rows of all of them.
Syntax
[ T | ] union [ UnionParameters ] [kind= inner|outer] [withsource= ColumnName] [isfuzzy= true|false] Tables
[ T | ] union [kind= inner|outer] [withsource= ColumnName] [isfuzzy= true|false] Tables
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | string | The input tabular expression. | |
| UnionParameters | string | Zero or more space-separated parameters in the form of Name = Value that control the behavior of the row-match operation and execution plan. See supported union parameters. | |
kind | string | Either inner or outer. inner causes the result to have the subset of columns that are common to all of the input tables. outer causes the result to have all the columns that occur in any of the inputs. Cells that aren’t defined by an input row are set to null. The default is outer.With outer, the result has all the columns that occur in any of the inputs, one column for each name and type occurrences. This means that if a column appears in multiple tables and has multiple types, it has a corresponding column for each type in the union’s result. This column name is suffixed with a ‘_’ followed by the origin column type. | |
withsource=ColumnName | string | If specified, the output includes a column called ColumnName whose value indicates which source table has contributed each row. If the query effectively references tables from more than one database including the default database, then the value of this column has a table name qualified with the database. cluster and database qualifications are present in the value if more than one cluster is referenced. | |
isfuzzy | bool | If set to true, allows fuzzy resolution of union legs. The set of union sources is reduced to the set of table references that exist and are accessible at the time while analyzing the query and preparing for execution. If at least one such table was found, any resolution failure yields a warning in the query status results, but won’t prevent the query execution. If no resolutions were successful, the query returns an error. The default is false.isfuzzy=true only applies to the union sources resolution phase. Once the set of source tables is determined, possible additional query failures won’t be suppressed. | |
| Tables | string | One or more comma-separated table references, a query expression enclosed with parenthesis, or a set of tables specified with a wildcard. For example, E* would form the union of all the tables in the database whose names begin E. |
Supported union parameters
| Name | Type | Required | Description |
|---|---|---|---|
hint.concurrency | int | Hints the system how many concurrent subqueries of the union operator should be executed in parallel. The default is the number of CPU cores on the single node of the cluster (2 to 16). | |
hint.spread | int | Hints the system how many nodes should be used by the concurrent union subqueries execution. The default is 1. |
| Name | Type | Required | Description |
|---|---|---|---|
| T | string | The input tabular expression. | |
kind | string | Either inner or outer. inner causes the result to have the subset of columns that are common to all of the input tables. outer causes the result to have all the columns that occur in any of the inputs. Cells that aren’t defined by an input row are set to null. The default is outer.With outer, the result has all the columns that occur in any of the inputs, one column for each name and type occurrences. This means that if a column appears in multiple tables and has multiple types, it has a corresponding column for each type in the union’s result. This column name is suffixed with a ‘_’ followed by the origin column type. | |
withsource=ColumnName | string | If specified, the output includes a column called ColumnName whose value indicates which source table has contributed each row. If the query effectively references tables from more than one database including the default database, then the value of this column has a table name qualified with the database. cluster and database qualifications are present in the value if more than one cluster is referenced. | |
isfuzzy | bool | If set to true, allows fuzzy resolution of union legs. The set of union sources is reduced to the set of table references that exist and are accessible at the time while analyzing the query and preparing for execution. If at least one such table was found, any resolution failure yields a warning in the query status results, but won’t prevent the query execution. If no resolutions were successful, the query returns an error. However, in cross-workspace and cross-app queries, if any of the workspaces or apps is not found, the query will fail. The default is false.isfuzzy=true only applies to the union sources resolution phase. Once the set of source tables is determined, possible additional query failures won’t be suppressed. | |
| Tables | string | One or more comma-separated table references, a query expression enclosed with parenthesis, or a set of tables specified with a wildcard. For example, E* would form the union of all the tables in the database whose names begin E.Whenever the list of tables is known, refrain from using wildcards. Some workspaces contains very large number of tables that would lead to inefficient execution. Tables may also be added over time leading to unpredicted results. |
Returns
A table with as many rows as there are in all the input tables.
Examples
Tables with string in name or column
union K* | where * has "Kusto"
Rows from all tables in the database whose name starts with K, and in which any column includes the word Kusto.
Distinct count
union withsource=SourceTable kind=outer Query, Command
| where Timestamp > ago(1d)
| summarize dcount(UserId)
The number of distinct users that have produced
either a Query event or a Command event over the past day. In the result, the ‘SourceTable’ column will indicate either “Query” or “Command”.
Query
| where Timestamp > ago(1d)
| union withsource=SourceTable kind=outer
(Command | where Timestamp > ago(1d))
| summarize dcount(UserId)
This more efficient version produces the same result. It filters each table before creating the union.
Using isfuzzy=true
// Using union isfuzzy=true to access non-existing view:
let View_1 = view () { print x=1 };
let View_2 = view () { print x=1 };
let OtherView_1 = view () { print x=1 };
union isfuzzy=true
(View_1 | where x > 0),
(View_2 | where x > 0),
(View_3 | where x > 0)
| count
Output
| Count |
|---|
| 2 |
Observing Query Status - the following warning returned:
Failed to resolve entity 'View_3'
// Using union isfuzzy=true and wildcard access:
let View_1 = view () { print x=1 };
let View_2 = view () { print x=1 };
let OtherView_1 = view () { print x=1 };
union isfuzzy=true View*, SomeView*, OtherView*
| count
Output
| Count |
|---|
| 3 |
Observing Query Status - the following warning returned:
Failed to resolve entity 'SomeView*'
Source columns types mismatch
let View_1 = view () { print x=1 };
let View_2 = view () { print x=toint(2) };
union withsource=TableName View_1, View_2
Output
| TableName | x_long | x_int |
|---|---|---|
| View_1 | 1 | |
| View_2 | 2 |
let View_1 = view () { print x=1 };
let View_2 = view () { print x=toint(2) };
let View_3 = view () { print x_long=3 };
union withsource=TableName View_1, View_2, View_3
Output
| TableName | x_long1 | x_int | x_long |
|---|---|---|---|
| View_1 | 1 | ||
| View_2 | 2 | ||
| View_3 | 3 |
Column x from View_1 received the suffix _long, and as a column named x_long already exists in the result schema, the column names were de-duplicated, producing a new column- x_long1
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.