SELECT Expression (U-SQL)

 

Updated: March 10, 2017

The SELECT expression is the transformation and query workhorse. It basically follows the standard SQL SELECT expression.

The logical processing flow of a SELECT expression is as follows: First the rowsets specified in the SELECT’s FROM clause will be combined into the SELECT expression’s rowset. Then the optional WHERE clause’s filter conditions are applied onto the rowset. The GROUP BY clause is then producing groups of rows, optionally filtered with the HAVING clause. The SELECT clause then projects the specified columns and allows aggregating the grouped data as well as transforming the selected data. Finally the ORDER BY clause with FETCH allows the selection of a limited number of rows based on the specified order.

Note that unlike in other systems, where a SELECT clause can output results to the user at any time, U-SQL’s current batch-mode centric execution model never outputs a SELECT result directly. Instead, a SELECT expression is always assigned to a rowset expression variable and in the end the script results needs to either be output into files or inserted into tables.

Syntax
Select_Expression :=                                                                                     
    Select_Clause
    Select_From_Clause
    [Where_Clause]
    [Group_By_Clause]
    [Order_By_Fetch_Clause].

Semantics of Syntax Elements

  • Select_Clause
    The SELECT clause specifies the resulting structure and values of the rowset of the SELECT expression.

  • Select_From_Clause
    The FROM clause specifies the input rowsets to the SELECT expression and how the rowsets are being combined into the SELECT expression’s rowset.

  • Where_Clause
    The optional WHERE clause specifies the filter conditions of the SELECT expression which will reduces the rows that are being produced as a result.

  • Group_By_Clause
    The optional GROUP BY clause groups the rows based on the provided expression list into groups that then can be aggregated over with the built-in and user-defined aggregator functions.

  • Order_By_Fetch_Clause
    The optional ORDER BY clause allows to order the rowset in order to select a number of rows based on their order using the FETCH subclause.

    Since all rowsets flow unordered through the query processor to provide better optimization, having an ORDER BY clause without a FETCH clause is meaningless. Thus, the ORDER BY clause on a U-SQL SELECT expression has to contain a FETCH clause. In order to get ordered output, use the ORDER BY clause on the OUTPUT statement.

See Also

Community Additions

ADD
Show: