I was working on a problem and I was desperate to do a clean job and not create a mess that no one can maintain it, and accidentally I read an article about SQL
APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
The table-valued function acts as the right input and the outer table expression acts as the left input. The advantage over inner join is that it is faster and you can handle conditional joins easily using this trick. A quick reminder on the terms.
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.
SELECT * FROM tblUser JOIN tblProfile ON tblUser.Id = tblProfile.UserId
But for some tasks the sets are not self-sufficient. For instance, let’s consider the following query:
tblProfile has a column called
For each row from
tblUser we need to select first
rowcount rows from
tblProfile, ordered by
We cannot come up with a join condition here. The join condition, should it exist, would involve the row number, which is not present in
tblProfile, and there is no way to calculate a row number only from the values of columns of any given row in
That’s where the
CROSS APPLY can be used:
1 2 3 4 5 6 7 8 9 SELECT * FROM tblUser CROSS APPLY ( SELECT TOP (tblUser.rowcount) * FROM tblProfile ORDER BY Id ) t2
While most queries which employ
CROSS APPLY can be rewritten using an
CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs.