If Inner join is: For each row in the left table, find row in the right table where the condition is met.
What's is cross apply? I have read that it's just inner join which gets evaluated row by row, but isn't inner join also evaluated row by row?
How do you explain cross apply in plain English? Is it just inner join but allows more complicated joins?
Inner Join (or simply join):
Given 2 tables, A, and B, and a condition C that correlates A and B (most commonly, an equality relation between 2 fields, one from A, and one from B), joining table A with B based on C means that, for each row in A, check for rows in B where C is met - and return them.
Translating it into an example:
SELECT * FROM A inner join B on A.field1 = B.field5
Here, for each row in A, check for rows in B where A's field1 equals B's field5. Return all such rows.
Cross Join:
A join that is not based on an explicit condition - rather - it combines every row from A with every row from B, and returns such rows.
Assuming A has 10 rows and B 20, you would get a result set of 200 rows.
Cross Apply: (which I have just learned about thanks to you :)
A cross apply is indeed related to a cross join, hence it bears "cross" in its name as well. What happens in a cross apply, as far as I understand, is:
Given a table A, and a function F, for each row selected by a given select statement from A, cross join it with the results of F. Let's say A has 10 rows, and F is simply a function that returns 3 constant rows, like
1
2
3
For each one of the 10 rows from A, you will cross join the 3 resulting rows from F. Resulting in a result set of 30 rows.
Now, for which purpose was this statement created, I think I can't help much. What I can think of, after reading some SO threads, is that it provides performance gains in such cross join operations (you could achieve the same results without using a function such as F and the "Cross-Apply").
This post provides an example of a scenario where such performance gain is achieved.
APPLY is different from JOIN in that it allows for correlated subqueries. For instance:
SELECT ...
FROM outer
APPLY (
SELECT ..
FROM inner WHERE outer.column = inner.column
)
At first this does not seems much of a difference, until you consider relational functions. Since APPLY accepts correlations from the other side, it means you can pass it values as arguments to the function:
SELECT ...
FROM outer
APPLY function(outer.column)
This is something not possible with JOIN.
The CROSS vs. OUTER is the same as with JOINs'.
