Share via

FROM Clause (U-SQL)

  • Article

Summary

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.

Remarks

  • 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 @rowset that 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.

Syntax

      Rowset_Expression :=                                                                      
          '(' Query_Expression ')'
      |   Function_Call
      |   External_Rowset_Expression
      |   Pivot_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.

Note

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;

See Also