Answered by:
Cross Apply in SqlServer
Question
Answers

The APPLY operator is similar to the JOIN operator, but the difference is that the righthand side operator of APPLY can reference columns from the lefthand side. Here are two very quick examples:
SELECT a.col, b.resultcol FROM dbo.tbl a CROSS APPLY dbo.mytblfunc(a.somecol) AS b
That is, you call a tablevalued function where the parameters comes from the table.
SELECT C.CustomerName, O.* FROM Customers C OUTER APPLY (SELECT TOP 1 * FROM Orders O WHERE C.CustomerID = O.CustomerID ORDER BY O.OrderDate DESC, O.OrderID DESC) AS O
In this example we list all customers and the most recent order.
As you in the examples there are both CROSS APPLY and OUTER APPLY. The difference between these two is what happens when the righthand side returns no rows. With OUTER APPLY, the row from the lefthand side is retained, showing NULLs in all columns from the righthand side. With CROSS APPLY, the row on the lefthand side is removed from the result set.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Proposed as answer by Shkumar Friday, December 02, 2011 9:53 AM
 Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:06 PM

Another use of the CROSS APPLY operator is to create aliases that can be reused by the query. For example:
select 100*a/line_Total as a_Percent, 100*b/line_Total as b_Percent, 100*c/line_Total as c_Percent From a_Table cross apply ( select nullif(a+b+c, 0) ) x(Line_Total)
 Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:06 PM

APPLY operator was a topic in SQL Tuesday #17
Here is Brad Schulz take on this topic
TSQL Tuesday #017: APPLY: It Slices! It Dices! It Does It All!
For every expert, there is an equal and opposite expert.  Becker's Law
My blog Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:07 PM
All replies

Very simple example how it works:
TSQL:
DECLARE @TableCA1 TABLE (ID VARCHAR(5)) INSERT INTO @TableCA1 SELECT 'A' UNION ALL SELECT 'B' UNION ALL SELECT 'C' UNION ALL SELECT 'D' DECLARE @TableCA2 TABLE (VAL VARCHAR(5)) INSERT INTO @TableCA2 SELECT 'X' SELECT CA1.ID,CA2.VAL FROM @TableCA1 CA1 CROSS APPLY @TableCA2 CA2
Shatrughna. 
Hi,
Cross Join in SQL:The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.
for mor details wit Cross Join Query check this link
http://www.w3resource.com/sql/joins/crossjoin.php
PS.Shakeer Hussain 
The APPLY operator is similar to the JOIN operator, but the difference is that the righthand side operator of APPLY can reference columns from the lefthand side. Here are two very quick examples:
SELECT a.col, b.resultcol FROM dbo.tbl a CROSS APPLY dbo.mytblfunc(a.somecol) AS b
That is, you call a tablevalued function where the parameters comes from the table.
SELECT C.CustomerName, O.* FROM Customers C OUTER APPLY (SELECT TOP 1 * FROM Orders O WHERE C.CustomerID = O.CustomerID ORDER BY O.OrderDate DESC, O.OrderID DESC) AS O
In this example we list all customers and the most recent order.
As you in the examples there are both CROSS APPLY and OUTER APPLY. The difference between these two is what happens when the righthand side returns no rows. With OUTER APPLY, the row from the lefthand side is retained, showing NULLs in all columns from the righthand side. With CROSS APPLY, the row on the lefthand side is removed from the result set.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Proposed as answer by Shkumar Friday, December 02, 2011 9:53 AM
 Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:06 PM

Another use of the CROSS APPLY operator is to create aliases that can be reused by the query. For example:
select 100*a/line_Total as a_Percent, 100*b/line_Total as b_Percent, 100*c/line_Total as c_Percent From a_Table cross apply ( select nullif(a+b+c, 0) ) x(Line_Total)
 Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:06 PM

APPLY operator was a topic in SQL Tuesday #17
Here is Brad Schulz take on this topic
TSQL Tuesday #017: APPLY: It Slices! It Dices! It Does It All!
For every expert, there is an equal and opposite expert.  Becker's Law
My blog Marked as answer by Kalman TothModerator Wednesday, December 07, 2011 9:07 PM

The following article is also on CROSS APPLY:
http://www.sqlusa.com/articles2005/crossapply/
Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012 Edited by Kalman TothModerator Sunday, September 30, 2012 1:14 PM