INNER JOIN vs. CROSS APPLY

INNER JOIN is the most used construct in SQL: it joins two tables together, selecting only those row combinations for which a JOIN condition is true.
This query:

SELECT  * FROM    table1 JOIN    table2 ON      table2.b = table1.a 

reads:

For each row from table1, select all rows from table2 where the value of field b is equal to that of field a

Note that this condition can be rewritten as this:


CROSS APPLY is a Microsoft's extension to SQL, which was originally intended to be used with table-valued functions (TVF's).
The query above would look like this:

 

SELECT  * FROM    table1 CROSS APPLY( 

SELECT  TOP (table1.rowcount) *

FROM    table2

ORDER BY Id

) t2

For each from table1, select first table1.rowcount rows from table2 ordered by id