ON Clause
You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY.
Syntax Element … |
Specifies … |
ON table_name ON view_name |
Table or view expression that is input to the table operator. |
ON (query_expression) |
Query expression that is input to the table operator. You can have up to 16 ON clauses. You can use multiple ON clauses with all supported languages for protected and non‑protected mode table operators. If you use multiple ON clauses, the order of the ON clauses must match the order of the stream numbers in the table operator that you use. Cogroups are used for table operators with multiple ON clauses. For information about cogroups, see SQL Functions, Operators, Expressions, and Predicates. |
AS correlation_name |
an alias for the table. |
HASH BY column_name column_position column_expression |
Optional set of column names on which to hash order globally the columns input to a table function or table operator. You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY. When you use a multiple input table operator that has multiple hash by clauses, the following restrictions apply: If you specify a LOCAL ORDER BY clause along with a HASH BY clause, the following restrictions apply: |
PARTITION BY column_name column_position column_expression |
Partition for a table specified as input to a table operator. You can specify a column by name or position, or use an expression that resolves to a column. You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY. You cannot specify a row-level security constraint column as a partitioning column. If you have multiple PARTITION BY clauses, the following restrictions apply: When you specify an ORDER BY clause with a PARTITION BY clause, the following restrictions apply: Note: Partition keys are important to performance. If the partition keys hash to the same AMP, then some AMPs may be overloaded compared with others. Performance may also be affected if the partition key is a VARCHAR, because VARCHARs may take longer to compare than some other data types. |
PARTITION BY ANY |
Specifies a table with no partitioning or order by attributes. PARTITION BY ANY preserves the existing distribution of the rows on the AMPs. A PARTITION BY ANY clause followed by ORDER BY clause means that all the rows are ordered by the ORDER BY clause on that partition, and it is functionally equivalent to using LOCAL ORDER BY without using a HASH BY clause. The following restrictions apply: |
DIMENSION |
Specifies that a duplicate copy of the dimension table is created for every partition on which the function operates. You can specify zero or more DIMENSION clauses for each ON clause. DIMENSION input is useful when the input table is a small look up table for facts or is a trained model, such as that used for machine learning. For a look up table using DIMENSION, the rows are duplicated to all the AMPs. Each AMP holds one instance of the look up table in memory and uses it to process each row of another input. For machine learning, you can store a model in the database that predicts the outcome of a particular data set and then use the stored model as dimensional input to a function. The following restrictions apply: For information about the SCRIPT table operator, see SQL Functions, Operators, Expressions, and Predicates. |
LOCAL ORDER BY |
Qualified rows are ordered on each AMP in preparation to be input to a table function. You cannot specify a PARTITION BY ANY clause with a LOCAL ORDER BY clause in the same ON clause. You cannot specify a DIMENSION clause with a LOCAL ORDER BY clause in the same ON clause. If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, all other ON clauses can only specify the DIMENSION option. If you use multiple ON clauses, you cannot use a LOCAL ORDER BY clause in addition to a DIMENSION clause in the same ON clause. If you specify multiple HASH BY clauses with LOCAL ORDER BY clauses, the following restrictions apply: |
ORDER BY |
how result sets are sorted. If you do not use this clause, result rows are returned unsorted. You cannot specify ORDER BY as the only option in an ON clause. You must combine it with a PARTITION BY, PARTITION BY ANY, HASH BY, or DIMENSION clause. If you specify an ORDER BY clause along with a PARTITION BY clause, the following restrictions apply: See “ORDER BY Clause” on page 202. When NORMALIZE and ORDER BY are specified in a query, the rows are normalized first and the result is ordered on the normalized result. |