U-SQL SELECT Selecting from CROSS APPLY and OUTER APPLY

  • Article

Summary

Often when processing some more complex value in a column, such as a byte array, a string, a MAP, ARRAY, JSON or XML document, one would like to extract more than just one value, such as a whole rowset of information per column value.

U-SQL provides the CROSS APPLY and OUTER APPLY operator which evaluates the rowset generating expressions on the right side against each row and its individual column cells of the rowset on the left. The result is the combination of the columns of both rowsets where the values of the left rowset get repeated for each result of the right rowset expression.

When CROSS APPLY is specified, no rows are produced for the row of the left rowset when the right-side rowset expression returns an empty rowset for that row.

When OUTER APPLY is specified, one row is produced for each row of the left rowset even when the right-side rowset expression returns an empty rowset for that row.

This operator is also known as LATERAL in other SQL dialects.

Because implementing a fully generic CROSS APPLY in a scale-out query processor is a difficult problem, U-SQL supports only a few special types of expressions that can be applied to a rowset source. The first expression type uses the built-in EXPLODE() expression and the second one is an expression that provides a user-defined operator called an Applier.

Syntax

Apply_Expression :=                                                                                      
     Rowset_Source Apply_Operator Explode_Expression  
|    Rowset_Source Apply_Operator Applier_Expression
|    Rowset_Source Apply_Operator Table_Value_Constructor_Expression Derived_Table_Alias

Remarks

  • Rowset_Source
    Identifies the input on which the explode or applier expression is being applied row-by-row. For more details on the rowset source see U-SQL SELECT FROM Clause.

  • Apply_Operator
    Is specifying the type of the apply operation: INNER or OUTER APPLY.

Syntax

  Apply_Operator :=                                                                                   
       'CROSS' 'APPLY'
  |    'OUTER' 'APPLY'.

When CROSS APPLY is specified, no rows are produced for the row of the left rowset when the right-side rowset expression returns an empty rowset for that row.

When OUTER APPLY is specified, one row is produced for each row of the left rowset even when the right-side rowset expression returns an empty rowset for that row.

  • Explode_Expression
    Turns an instance of type SQL.ARRAY or SQL.MAP into a rowset where each item or key/value pair respectively is mapped into a row. For more details see EXPLODE (U-SQL).

  • Applier_Expression
    Is a U-SQL expression that returns an instance of an IApplier user-defined operator, that in turn takes each row of the input rowset into 0 to n rows of the specified schema of the applier clause. For more details see U-SQL Using APPLY with an Applier UDO.

See Also