In a relational database the use of JOIN is invaluable when combining data in one table to another. Through stored procedures the ability to use JOIN to combine physical tables to views, temporary tables, derived tables and table variables is equally important in our realm of data retrieval activities.

In SQL Server 2000, the table-valued function was introduced. The table-valued function provides a user defined function the ability to return a table object. The ability to combine the results of a table-valued function has been available through the use of JOIN; but presented some challenges when the argument that is to be passed is a value that changes for each record of the parent table. The following statement will result in a syntax error:

    SELECT
        *
    FROM
        [TABLE] A
        INNER JOIN [TABLE-VALUED FUNCTION] (A.[FIELD]) B
            ON A.[FIELD] =  B.[FIELD]

If the argument value was one that was not dependent upon the parent table and remained constant through out the query the above approach would work without issue.

SQL Server 2005 introduced the APPLY operator. This little savior provided the ability to overcome the shortcomings of the use of JOIN with table-valued functions. The syntax of using APPLY would appear as follows:

    SELECT
        *
    FROM
        [TABLE] A
        [CROSS/OUTER] APPLY [TABLE-VALUED FUNCTION] (A.[FIELD]) B

For more specifics of how to use the APPLY operator, please refer to the following link:
http://msdn.microsoft.com/en-us/library/ms175156.aspx