SELECT Expression (U-SQL)

  • Article

Summary

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.

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.

For ordered output, use the ORDER BY clause on the OUTPUT statement.

Syntax

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

Remarks

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

See Also