What is the Diff B/W Cross Join and Cross Apply? I think i'm going out of scope, else provide me reference to understand it in detail.
For a JOIN (any join, either INNER, OUTER, or CROSS), both sources must be either a table or view, or a table-valued function or subquery that is completely independant of the rest of the query. So you can not for instance write:
FROM Table1 AS a CROSS JOIN dbo.TableValuedFunction(a.Column1) AS b
FROM Table1 AS a CROSS JOIN (SELECT Col1, Col2 FROM Table2 AS b WHERE b.Col3 = a.Col3) AS b
This is a limitation that comes from the defintion of joins in the ANSI standard, which says to first project both sources, then make each possible row-pair (a cross join), and then filter out those that don't satisfy the join condition. You can not "first" project the second source in this case, because it can be different for each row from the first source.
Some vendors have chosen to work around this implementation by allowing code like the above. Microsoft has made almost the same decision, but they chose to introduce a special (non-ANSI) keyword for this: APPLY. So the two code fragments above can be made into valid T-SQL code as follows:
FROM Table1 AS a CROSS APPLY dbo.TableValuedFunction(a.Column1) AS b
FROM Table1 AS a CROSS APPLY (SELECT Col1, Col2 FROM Table2 AS b WHERE b.Col3 = a.Col3) AS b
The logical definition of these queries is: for each row in Table1, evaluate the second source (either the function or the subquery) using the "current" row in Table1, then join all the results from the result of that evaluation to that "current" row in Table1. (The physical implementation can be different - in many cases, the optimizer is able to come up with a faster way to achieve the same result).
And to paint a complete picture - apart from CROSS APPLY, you can also use OUTER APPLY. This works the same, with only one difference - if, for a given row in Table1, the function or subquery produces an empty result set, CROSS APPLY would discard that row from the result (like a CROSS JOIN to an empty table, or an INNER JOIN with no match); OTER APPLY would keep that row in the result set, with NULL values in all the columns that normally come from the function or subquery (similar to an OUTER JOIN).