FROM Clause (U-SQL)
Updated: April 10, 2017
The FROM clause specifies the input rowsets to the SELECT expression and how the rowsets are being combined into the SELECT expression’s rowset.
| Syntax |
|---|
Select_From_Clause :=
'FROM' Rowset_Source.
|
Semantics of Syntax Elements
Rowset_Source
There are many rowset sources from which can be selected:Syntax Rowset_Source := Aliased_Rowset | Table_Value_Constructor | Join_Expression | Apply_Expression | '(' Join_Expression ')'.Aliased_Rowset
Aliased rowsets are rowsets that may or may not need a table alias.Syntax Aliased_Rowset := Rowset [Alias] | Rowset_Expression Alias.Alias
The rowset alias gives a name to the rowset that can be used in the remainder of the SELECT expression to refer to that specific rowset. It can be a quoted or unquoted identifier:Syntax Alias := 'AS' Quoted_or_Unquoted_Identifier.Rowset
The simplest rowset sources are a rowset variable such as@rowsetthat has been defined in a previous statement of the script and a table that has been created in the account’s catalog:Syntax Rowset := Rowset_Variable | Identifier.A table can be referenced either with its fully 3-part qualified name, within the current database context with a 2-part name, or within the current database and schema context with a single-part name. Optionally, a table alias can be provided for an input rowset variable or table which then can be used in the remainder of the SELECT expression.
Providing a rowset alias is optional.
Rowset_Expression
U-SQL also provides the ability to query over nested query expressions, table-valued function calls or querying external rowsets. Follow the links for more details on each.In these cases, a rowset alias has to be provided and cannot be left out.
Syntax Rowset_Expression := '(' Query_Expression ')' | Function_Call | External_Rowset_Expression | | Unpivot_Expression.
Table_Value_Constructor
U-SQL’s SELECT can also select from the VALUES table value constructor that creates an inline table.Join_Expression
U-SQL’s SELECT can also select from many different types of join expressions that combine several rowsets.Apply_Expression
U-SQL’s SELECT can also select from CROSS and OUTER APPLY expressions that turn scalar values in the left-hand rowset’s rows into rowsets. Follow the above link for more details.
Unlike most SQL versions, U-SQL does not support the ability to join rowsets with the comma notation. Instead one needs to use the explicit ANSI SQL join syntax. |
For example, this implicit join expression is not supported
SELECT * FROM T1, T2 WHERE T1.val == T2.val;
Instead the query expression has be written as
SELECT * FROM T1 INNER JOIN T2 ON T1.val == T2.val;