Search code examples
sqlsql-serverjoincross-apply

Inner join and cross apply. How does it get evaluated?

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?

Solution

  • 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'.