## selectStatement
A SQL select statement retrieves data from one or multiple data containers. A select statement can be composed of multiple data sets retrieved from many platforms, combined by set operators such as 'union'.
Often the performance of cloud platforms is less than traditional database platforms. With the 'limit' clause a limited number of rows can be retrieved quickly from a table or view after applying sorting as specified by the possibly present 'order by'. An alternative for a 'limit' clause is to use the 'top' clause.
Each statement in the SQL batch will be executed consecutively. Execution will be stopped when an error occurs during execution of a statement.
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
selectStatement_start((START))
selectStatement_start --> selectStatement_0_0[uniqueSelectStatement]
selectStatement_0_0 --> selectStatement_0_1[setOperatorSelectStatement]
selectStatement_0_1 --> selectStatement_0_2[orderBy]
selectStatement_0_2 --> selectStatement_0_3[limitClause]
selectStatement_0_3 --> selectStatement_0_4[forClause]
selectStatement_0_4 --> selectStatement_end((END))
```
## setOperatorSelectStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
setOperatorSelectStatement_start((START))
setOperatorSelectStatement_start --> setOperatorSelectStatement_0_0[UNION]:::quoted
setOperatorSelectStatement_0_0 --> setOperatorSelectStatement_0_1[ALL]:::quoted
setOperatorSelectStatement_0_1 --> setOperatorSelectStatement_0_2[DISTINCT]:::quoted
setOperatorSelectStatement_0_2 --> setOperatorSelectStatement_0_3[ON]:::quoted
setOperatorSelectStatement_0_3 --> setOperatorSelectStatement_0_4[columnList]
setOperatorSelectStatement_0_4 --> setOperatorSelectStatement_0_5[MINUS_C]:::quoted
setOperatorSelectStatement_0_5 --> setOperatorSelectStatement_0_6[INTERSECT]:::quoted
setOperatorSelectStatement_0_6 --> setOperatorSelectStatement_0_7[uniqueSelectStatement]
setOperatorSelectStatement_0_7 --> setOperatorSelectStatement_end((END))
```
## Purpose
SQL is based upon a solid mathematical foundation named 'set theory' with some exceptions. The set operators of Invantive UniversalSQL enable you to combine sets of data sets such as merging two sets of data. Please note that SQL actually uses 'bags', which opposed to 'sets', allow duplicates. To change bags of data into sets, either use 'distinct' or the 'union' set operator without 'all'. In general, the extensive use of 'distinct' signals bad database design.
The 'union' set operator returns the union of the data on the left and right side of the union while removing duplicate rows. The 'union all' set operator returns the union of the data on the left and right side of the union without removing duplicate rows. The 'minus' set operator returns all rows from the left side which do not occur in the right side. The 'intersect' set operator returns all rows that occur both in the left and right side.
The 'union' set operator has an extension to facilitate sieving overlapping data sets of decreasing quality using 'distinct on'. The 'union distinct on' set operator returns the union of the data sets listed, but only the first match on the column list is returned. The first match is defined as a fallthrough from left to right. In general, the preferred source of a set of data is listed first, followed by one or multiple 'union distinct on' set operators, each with a data set of less preference.
## uniqueSelectStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
uniqueSelectStatement_start((START))
uniqueSelectStatement_start --> uniqueSelectStatement_0_0[SELECT]:::quoted
uniqueSelectStatement_0_0 --> uniqueSelectStatement_0_1[executionHints]
uniqueSelectStatement_0_1 --> uniqueSelectStatement_0_2[DISTINCT]
uniqueSelectStatement_0_2 --> uniqueSelectStatement_0_3[topClause]
uniqueSelectStatement_0_3 --> uniqueSelectStatement_0_4[selectList]
uniqueSelectStatement_0_4 --> uniqueSelectStatement_0_5[INTO]:::quoted
uniqueSelectStatement_0_5 --> uniqueSelectStatement_0_6[pSqlVariableList]
uniqueSelectStatement_0_6 --> uniqueSelectStatement_0_7[FROM]:::quoted
uniqueSelectStatement_0_7 --> uniqueSelectStatement_0_8[dataSource]
uniqueSelectStatement_0_8 --> uniqueSelectStatement_0_9[joinStatements]
uniqueSelectStatement_0_9 --> uniqueSelectStatement_0_10[whereClause]
uniqueSelectStatement_0_10 --> uniqueSelectStatement_0_11[groupBy]
uniqueSelectStatement_0_11 --> uniqueSelectStatement_end((END))
```
## Purpose
Retrieves a data set from one or more data containers.
## dataSource
One of:
- [tableOrFunctionSpec]]
- [[embeddedSelect]]
- [[XML Table (xmlTable)]]
- [[CSV Table (csvTableSpec)]]
- [[JSON and NDJSON Table (jsonTableSpec)]]
- [[Excel Table (excelTableSpec)]]
- [[HTML Table (htmlTable)]]
- [[String Split (stringSplitSpec)]]
- [[Internet Table (internetTableSpec)]]
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
dataSource_0_2 --> dataSource_0_5[dataSource]
dataSource_0_5 --> dataSource_0_6[tableSpec]
dataSource_0_6 --> dataSource_0_10[aliased]
dataSource_0_10 --> dataSource_0_11[pivotClause]
dataSource_0_11 --> dataSource_end((END))
```
## Purpose
A data source can be a table, a table with parameters or a nested select (an 'inline view'). Also, a data source can be a binary or text string being interpreted as XML, CSV, JSON or binary Excel Open XML format.
## topClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
topClause_start((START))
topClause_start --> topClause_0_0[TOP]:::quoted
topClause_0_0 --> topClause_0_1[numericConstant]
topClause_0_1 --> topClause_end((END))
```
## Purpose
With the 'top' clause a limited number of rows can be retrieved quickly from a table or view after applying sorting as specified by the possibly present 'order by'.
## limitClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
limitClause_start((START))
limitClause_start --> limitClause_0_0[LIMIT]:::quoted
limitClause_0_0 --> limitClause_0_1[numericConstant]
limitClause_0_1 --> limitClause_end((END))
```
## Purpose
With the 'limit' clause a limited number of rows can be retrieved quickly from a table or view after applying sorting as specified by the possibly present 'order by'.
## numberedOrNamedExpressionList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
numberedOrNamedExpressionList_start((START))
numberedOrNamedExpressionList_start --> numberedOrNamedExpressionList_0_0[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
numberedOrNamedExpressionList_0_0 --> numberedOrNamedExpressionList_0_1[","]:::quoted
numberedOrNamedExpressionList_0_1 --> numberedOrNamedExpressionList_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
numberedOrNamedExpressionList_0_2 --> numberedOrNamedExpressionList_0_3[namedExpression]
numberedOrNamedExpressionList_0_3 --> numberedOrNamedExpressionList_0_4[","]:::quoted
numberedOrNamedExpressionList_0_4 --> numberedOrNamedExpressionList_0_5[namedExpression]
numberedOrNamedExpressionList_0_5 --> numberedOrNamedExpressionList_end((END))
```
## expressionList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
expressionList_start((START))
expressionList_start --> expressionList_0_0[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
expressionList_0_0 --> expressionList_0_1[","]:::quoted
expressionList_0_1 --> expressionList_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
expressionList_0_2 --> expressionList_end((END))
```
## Purpose
An ordered comma-separated list of value expressions.
## namedExpressionList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
namedExpressionList_start((START))
namedExpressionList_start --> namedExpressionList_0_0[namedExpression]
namedExpressionList_0_0 --> namedExpressionList_0_1[","]:::quoted
namedExpressionList_0_1 --> namedExpressionList_0_2[namedExpression]
namedExpressionList_0_2 --> namedExpressionList_end((END))
```
## Purpose
An unordered list of value expressions, identified by the parameter name.
## namedExpression
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
namedExpression_start((START))
namedExpression_start --> namedExpression_0_0[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>]
namedExpression_0_0 --> namedExpression_0_1[ASSOCIATION_OPERATOR]:::quoted
namedExpression_0_1 --> namedExpression_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
namedExpression_0_2 --> namedExpression_end((END))
```
## Purpose
A value expression, identified by the parameter name, the association operator '=>' and the value expression.
## distributedAliasDirective
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
distributedAliasDirective_start((START))
distributedAliasDirective_start --> distributedAliasDirective_0_0["@"]:::quoted
distributedAliasDirective_0_0 --> distributedAliasDirective_0_1[dataContainerAlias]
distributedAliasDirective_0_1 --> distributedAliasDirective_end((END))
```
## Purpose
The distributed alias after the at-sign specifies a specific data source to be used, such as 'exactonlinerest..journals@eolbe' specifying the use of Exact Online Belgium when 'eolbe' is associated by the database definitions in settings.xml with Exact Online Belgium.
A number of special so-called 'service providers' are always present, such as 'datadictionary' for use by an alias.
## dataContainerAlias
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> dataContainerAlias
dataContainerAlias -->| | exprNode["<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>"]
exprNode["<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>"] --> End((END))
```
## Purpose
When multiple data containers have been defined in settings.xml for a database, each one is assigned an alias. An alias typically takes the form of a limited number of characters. The presence of an alias allows Invantive UniversalSQL to precisely determine to what data container forward a request for data.
## passingSourceOrPathExpression
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
passingSourceOrPathExpression_start((START))
passingSourceOrPathExpression_start --> passingSourceOrPathExpression_0_0[PASSING]
passingSourceOrPathExpression_0_0 --> passingSourceOrPathExpression_0_1[FILE]
passingSourceOrPathExpression_0_1 --> passingSourceOrPathExpression_0_2[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
passingSourceOrPathExpression_0_2 --> passingSourceOrPathExpression_end((END))
```
## Purpose
The passing option specifies the source of the contents to be interpreted. The contents can be specified as the outcome of an expression such as from a previous read_file() table function, a URL downloaded using httpget() or a string concatenation. The contents can also be specified as to be taken from a specific file identified by it's file name and path as an expression.
## groupBy
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
groupBy_start((START))
groupBy_start --> groupBy_0_0["GROUP BY"]:::quoted
groupBy_0_0 --> groupBy_0_2[columnList]
groupBy_0_2 --> groupBy_end((END))
```
## Purpose
Grouping of multiple rows into groups is specified by the groupBy. A group will be introduced for each distinct combination of column values for the columns listed. The values of grouped columns can be used in the select clause. Columns not being grouped upon can only be used within the context of a group function listed as 'aggregateFunction'.
## orderBy
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
orderBy_start((START))
orderBy_start --> orderBy_0_0["ORDER BY"]:::quoted
orderBy_0_0 --> orderBy_0_2[sortedColumnList]
orderBy_0_2 --> orderBy_end((END))
```
## Purpose
Sort the rows returned as specified by the list of columns. Values are either sorted ascending (the default) or descending.
## pivotClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
pivotClause_start((START))
pivotClause_start --> pivotClause_0_0["PIVOT"]:::quoted
pivotClause_0_0 --> pivotClause_0_1["("]:::quoted
pivotClause_0_1 --> pivotClause_0_2[aggregateFunction]
pivotClause_0_2 --> pivotClause_0_3["FOR"]:::quoted
pivotClause_0_3 --> pivotClause_0_4[column]
pivotClause_0_4 --> pivotClause_0_5["IN"]:::quoted
pivotClause_0_5 --> pivotClause_0_6["("]:::quoted
pivotClause_0_6 --> pivotClause_0_7[columnNoAliasList]
pivotClause_0_7 --> pivotClause_0_8[")"]:::quoted
pivotClause_0_8 --> pivotClause_0_9[")"]:::quoted
pivotClause_0_9 --> pivotClause_0_10[aliased]
pivotClause_0_10 --> pivotClause_end((END))
```
## sortDirection
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> sortDirection
sortDirection -->| | ASC["ASC"]:::quoted
ASC --> End((END))
sortDirection -->| | DESC["DESC"]:::quoted
DESC --> End((END))
```
## Purpose
A sort direction can be either 'asc' for 'ascending' (the default) or 'desc' for 'descending'.
## columnList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
columnList_start((START))
columnList_start --> columnList_0_0[column]
columnList_0_0 --> columnList_0_1[","]
columnList_0_1 --> columnList_0_2[column]
columnList_0_2 --> columnList_end((END))
```
## Purpose
A comma-separated list of columns.
## sortedColumnList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
sortedColumnList_start((START))
sortedColumnList_start --> sortedColumnList_0_0[sortedColumn]
sortedColumnList_0_0 --> sortedColumnList_0_1[","]:::quoted
sortedColumnList_0_1 --> sortedColumnList_0_2[sortedColumn]
sortedColumnList_0_2 --> sortedColumnList_end((END))
```
## Purpose
An ordered comma-separated list of column values to sort upon.
## sortedColumn
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
sortedColumn_start((START))
sortedColumn_start --> sortedColumn_0_0[column]
sortedColumn_0_0 --> sortedColumn_0_1[sortDirection]
sortedColumn_0_1 --> sortedColumn_end((END))
```
## Purpose
A column values to sort upon.
## column
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
column_start((START))
column_start --> column_0_0[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>]
column_0_0 --> column_0_1["."]:::quoted
column_0_1 --> column_0_2[<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>]
column_0_2 --> column_end((END))
```
## Purpose
A column is identified by an identifier, possibly prefixed by the name of the table or the alias of the table from which the column is to be taken.
## columnNoAliasList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
columnNoAliasList_start((START))
columnNoAliasList_start --> columnNoAliasList_0_0[columnNoAlias]
columnNoAliasList_0_0 --> columnNoAliasList_0_1[","]:::quoted
columnNoAliasList_0_1 --> columnNoAliasList_0_2[columnNoAlias]
columnNoAliasList_0_2 --> columnNoAliasList_end((END))
```
## columnNoAlias
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> columnNoAlias
columnNoAlias -->| | exprNode["<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>"]
exprNode["<a href="Invantive UniversalSQL/General/Identifiers" class="internal-link">identifier</a>"] --> End((END))
```
## whereClause
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
whereClause_start((START))
whereClause_start --> whereClause_0_0[WHERE]:::quoted
whereClause_0_0 --> whereClause_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
whereClause_0_1 --> whereClause_end((END))
```
## Purpose
The where-clause restricts the number of rows in a result set by applying one or more boolean condiditions which rows must satisfy.
## joinStatements
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> joinStatements
joinStatements -->| | joinStatement
joinStatement --> End((END))
```
## Purpose
A list of join statements.
## joinStatement
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
joinStatement_start((START))
joinStatement_start --> joinStatement_0_0[joinCategory]
joinStatement_0_0 --> joinStatement_0_1[JOIN]:::quoted
joinStatement_0_1 --> joinStatement_0_2[dataSource]
joinStatement_0_2 --> joinStatement_0_3[joinConditions]
joinStatement_0_3 --> joinStatement_end((END))
```
## Purpose
A join statement combines two result sets. Only combinations of rows taken from both result sets are returned when they meet the join conditions.
## joinCategory
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
joinCategory_start((START))
joinCategory_start --> joinCategory_0_0[INNER]
joinCategory_0_0 --> joinCategory_0_1[joinSubCategory]
joinCategory_0_1 --> joinCategory_0_2[OUTER]
joinCategory_0_2 --> joinCategory_0_3[CROSS]
joinCategory_0_3 --> joinCategory_end((END))
```
## Purpose
The join category specifies what combinations of rows are considered. The following variants can be used:
- inner join, as indicated by 'join' or 'inner join': an inner join returns all combinations of rows from both result sets that meet the join conditions.
- left outer, as indicated by 'left outer join': a left outer join returns the same rows as an inner join, extended by one row for each row in the left result set having no matching rows in the right result set. Each column that originates from the right result set is assigned a null value.
- right outer, as indicated by 'right outer join': a right outer join returns the same rows as an inner join, extended by one row for each row in the right result set having no matching rows in the left result set. Each column that originates from the left result set is assigned a null value.
- full outer, as indicated by 'full outer join': a full outer join returns the same rows as an inner join, extended by one row for each row in the right result set having no matching rows in the left result set. Each column that originates from the left result set is assigned a null value. The results are also extended by one row for each row in the left result set having no matching rows in the right result set. Each column that originates from the right result set is assigned a null value.
- cross join, as indicated by 'cross join': a cross join returns a Cartesian product of the rows from both result sets. A 'Cartesian product' is a term from set theory, which indicates that all combinations are returned.
## joinSubCategory
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> joinSubCategory
joinSubCategory -->| | LEFT:::quoted
LEFT --> End((END))
joinSubCategory -->| | RIGHT:::quoted
RIGHT --> End((END))
joinSubCategory -->| | FULL:::quoted
FULL --> End((END))
```
## Purpose
The join sub-category refines the join category. Please see 'joinCategory' for an explanation.
## joinConditions
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
joinConditions_start((START))
joinConditions_start --> joinConditions_0_0[ON]
joinConditions_0_0 --> joinConditions_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>]
joinConditions_0_1 --> joinConditions_end((END))
```
## Purpose
A boolean expression which defines valid combinations of the join.
## selectList
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
selectList_start((START))
selectList_start --> selectList_0_0[selectPart]
selectList_0_0 --> selectList_0_1[","]:::quoted
selectList_0_1 --> selectList_0_2[selectPart]
selectList_0_2 --> selectList_end((END))
```
## selectPart
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
selectPart_start((START))
selectPart_start --> selectPart_0_0[part]
selectPart_0_0 --> selectPart_0_1[aliased]
selectPart_0_1 --> selectPart_0_2[labeled]
selectPart_0_2 --> selectPart_end((END))
```
## aliased
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
aliased_start((START))
aliased_start --> aliased_0_0[AS]:::quoted
aliased_0_0 --> aliased_0_1[alias]
aliased_0_1 --> aliased_end((END))
```
## labeled
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
labeled_start((START))
labeled_start --> labeled_0_0[LABEL]:::quoted
labeled_0_0 --> labeled_0_1[stringConstant]
labeled_0_1 --> labeled_end((END))
```
## Purpose
Defines the textual label of an expression. The label may contain resources in the format '{res:resource code}' such as 'My {res:itgen_description}'. Similar to the data type and alias of an expression, the label is maintained across selections. Application of a calculation or a SQL function resets the label to the empty value. User interfaces can choose to display the label when available instead of the column name to provide a more natural interface.
## part
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> part
part -->| | exprNode["<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>"]
exprNode["<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>"] --> End((END))
part -->| | aggregateFunction
aggregateFunction --> End((END))
part -->| | allColumnsSpec
allColumnsSpec --> End((END))
```
## allColumnsSpec
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
allColumnsSpec_start((START))
allColumnsSpec_start --> allColumnsSpec_0_0[allColumnsSpecId]
allColumnsSpec_0_0 --> allColumnsSpec_0_1[allColumnsSpecColumnNamePrefix]
allColumnsSpec_0_1 --> allColumnsSpec_0_2[allColumnsSpecColumnNamePostfix]
allColumnsSpec_0_2 --> allColumnsSpec_0_3[allColumnsSpecLabelPrefix]
allColumnsSpec_0_3 --> allColumnsSpec_0_4[allColumnsSpecLabelPostfix]
allColumnsSpec_0_4 --> allColumnsSpec_end((END))
```
## Purpose
Selects all columns from an SQL statement or from one or more data sources used. The except clause allows retrieval of all columns except the listed names. The except clause is typically used with wide result sets with possibly varying column lists from which a few pre-defined columns need to excluded.
## allColumnsSpecId
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
allColumnsSpecId_start((START))
allColumnsSpecId_start --> allColumnsSpecId_0_0[alias]
allColumnsSpecId_0_0 --> allColumnsSpecId_0_1["."]
allColumnsSpecId_0_1 --> allColumnsSpecId_0_2["*"]
allColumnsSpecId_0_2 --> allColumnsSpecId_0_3["EXCEPTION"]:::quoted
allColumnsSpecId_0_3 --> allColumnsSpecId_0_4[columnList]
allColumnsSpecId_0_4 --> allColumnsSpecId_end((END))
```
## allColumnsSpecColumnNamePrefix
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
allColumnsSpecColumnNamePrefix_start((START))
allColumnsSpecColumnNamePrefix_start --> allColumnsSpecColumnNamePrefix_0_0[PREFIX]:::quoted
allColumnsSpecColumnNamePrefix_0_0 --> allColumnsSpecColumnNamePrefix_0_1[WITH]:::quoted
allColumnsSpecColumnNamePrefix_0_1 --> allColumnsSpecColumnNamePrefix_0_2[stringConstant]
allColumnsSpecColumnNamePrefix_0_2 --> allColumnsSpecColumnNamePrefix_end((END))
```
## Purpose
Defines the aliases of columns selected by the select all ('') operator as their original alias, prefixed with the specified text. For instance, the select operator changes the alias of the column 'code' with the prefix 'pjt_' into 'pjt_code'.
## allColumnsSpecColumnNamePostfix
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
allColumnsSpecColumnNamePostfix_start((START))
allColumnsSpecColumnNamePostfix_start --> allColumnsSpecColumnNamePostfix_0_0[POSTFIX]:::quoted
allColumnsSpecColumnNamePostfix_0_0 --> allColumnsSpecColumnNamePostfix_0_1[WITH]:::quoted
allColumnsSpecColumnNamePostfix_0_1 --> allColumnsSpecColumnNamePostfix_0_2[stringConstant]
allColumnsSpecColumnNamePostfix_0_2 --> allColumnsSpecColumnNamePostfix_end((END))
```
## Purpose
Defines the aliases of columns selected by the select all ('') operator as their original alias, postfixed with the specified text. For instance, the select operator changes the alias of the column 'code' with the postfix 'from_table2' into 'code_from_table2'.
## allColumnsSpecLabelPrefix
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
allColumnsSpecLabelPrefix_start((START))
allColumnsSpecLabelPrefix_start --> allColumnsSpecLabelPrefix_0_0[LABEL]:::quoted
allColumnsSpecLabelPrefix_0_0 --> allColumnsSpecLabelPrefix_0_1[PREFIX]:::quoted
allColumnsSpecLabelPrefix_0_1 --> allColumnsSpecLabelPrefix_0_2[WITH]:::quoted
allColumnsSpecLabelPrefix_0_2 --> allColumnsSpecLabelPrefix_0_3[stringConstant]
allColumnsSpecLabelPrefix_0_3 --> allColumnsSpecLabelPrefix_end((END))
```
## Purpose
Defines the label of columns selected by the select all ('') operator as their original label, prefixed with the specified text. For instance, the select operator changes the label 'Name' of the column 'name' with the prefix 'Project ' into 'Project Name'.
## allColumnsSpecLabelPostfix
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart TD
allColumnsSpecLabelPostfix_start((START))
allColumnsSpecLabelPostfix_start --> allColumnsSpecLabelPostfix_0_0[LABEL]:::quoted
allColumnsSpecLabelPostfix_0_0 --> allColumnsSpecLabelPostfix_0_1[POSTFIX]:::quoted
allColumnsSpecLabelPostfix_0_1 --> allColumnsSpecLabelPostfix_0_2[WITH]:::quoted
allColumnsSpecLabelPostfix_0_2 --> allColumnsSpecLabelPostfix_0_3[stringConstant]
allColumnsSpecLabelPostfix_0_3 --> allColumnsSpecLabelPostfix_end((END))
```
## Purpose
Defines the label of columns selected by the select all ('') operator as their original label, postfixed with the specified text. For instance, the select operator changes the label 'Name' of the column 'name' with the postfix ' from Table2' into 'Name from Table2'.
## orReplace
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
orReplace_start((START))
orReplace_start --> orReplace_0_0[OR]
orReplace_0_0 --> orReplace_0_1[REPLACE]:::quoted
orReplace_0_1 --> orReplace_end((END))
```