Cross Apply And Outer Apply With Examples This article describes about Cross Apply and Outer Apply and give some examples with functions. The Apply operator joins two table valued expression, the table on right is evaluated every time for each row of the table on the left which is actually a table-valued function. Here's my top 3 favorite uses for CROSS APPLY and OUTER APPLY: APPLY is fantastic for calling table valued functions. I didn't include questions about those in the quiz, simply for the purposes of keeping the code simple, and because I wanted the quiz to be about thinking through how apply works — but it's still the #1 use.
-->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
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
orOUTER APPLY
.
![Sql Cross Apply Sql Cross Apply](http://sqlity.net/wp-content/uploads/2012/12/cross-apply-example.jpg)
Syntax
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.