Using APPLY

I had never heard or used this clause previously and just came across an example at work of how APPLY can be used to retrieve data from a join.

The 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 right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY. 

CROSS APPLY returns only rows from the outer table that produce a result set from the table-valued function. 

OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.

Often these operators can be used for retrieving SKU pricing, product SKU's, item lookups, etc. For example (got this as close as i can get without copy pasting from the actual script):

SELECT A.PRODUCT_SKU, A.PRODUCT_SKU_DESCRIPTION, B.SKU_LEVEL
FROM DBO.PRODUCT_SKUS A (NOLOCK)
INNER JOIN DBO.SKU_SALES B (NOLOCK)
ON A.PRODUCT_SKU = B.SKU
AND A.STATUS = 'A'
AND A.DISTI_CHANNEL = '007'
AND B.STATUS = 'A'
CROSS APPLY
(
SELECT TOP 1 FROM DBO.SKU_PRICE_ATTRIBUTE_LOOKUP C (NOLOCK)
INNER JOIN DBO.SKU_PRICE_12 D (NOLOCK)
ON C.SKU = D.SKU_ID
AND C.STATUS = 'A'
AND D.STATUS = 'A'
AND D.LAST_UPD_ROW_ID = '3RD-Q-12'
)

I used the same text as in the help section of MSDN, but this is something i would like to remember to use in future and need to spread the word about.