Table of contents
TOC
Collapse the table of content
Expand the table of content

FROM Clause (U-SQL)

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

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 @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.

      • 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
        |   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

© 2018 Microsoft