In this article, I will explain how to use the APPLY operator to join the results of a table valued function to a table.  The APPLY operator allows a table column to be supplied as an input parameter to a table valued function.

Microsoft describes the APPLY operator as "The new APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression" (source).  Basically what that means is that you can join a table valued function to another table.  Except you don't use the JOIN operator.  You have to use either the CROSS APPLY or the OUTER APPLY operators.

Under certain circumstances you can use the simple join syntax.  Specifically, when the function has no parameters or the parameters are variables or literals.  But when the input parameters for the table valued function come from a table, attempting to do so will generate an error.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "xxxxxxxxx" could not be bound.

In order to demonstrate use of the APPLY syntax, we will create a sample table and load it with some sample data.  We will use the fn_DelimitedToTable function as demonstrated in the article titled Converting a delimited string into a table.

create table [dbo].[test_data_1]
     (ident         int identity primary key clustered
     ,username      varchar(100) not null
     ,websites      varchar(100) null
     )
GO
insert into [dbo].[test_data_1](username,websites) 
  values('Galderisi,Brandon', 'SQLServerNation.com,experts-exchange.com')
insert into [dbo].[test_data_1](username,websites) 
  values('Chapman,Tim', 'SQLServerNation.com,experts-exchange.com')
insert into [dbo].[test_data_1](username,websites) 
  values('Wills,Mark', 'SQLServerNation.com,experts-exchange.com')
insert into [dbo].[test_data_1](username,websites) 
  values('Smith,Joe', NULL)
GO

There are two variations of the APPLY operator.  CROSS and OUTER.  The CROSS APPLY operates just like an INNER  JOIN.  It will only return values from the table that yield results from the function.  The OUTER APPLY operator, as you probably guessed, is like an OUTER JOIN.  That means that all records from the table will be returned regardless of whether or not function returns data.

Below is the syntax for each, and we will then review the results.

select t1.*,f1.theValue from [dbo].[test_data_1] t1
  cross apply [dbo].[fn_DelimitedToTable](t1.websites,',') f1

select t1.*,f1.theValue from [dbo].[test_data_1] t1
  outer apply [dbo].[fn_DelimitedToTable](t1.websites,',') f1

The results show that each record returned from the function, yields a new row.  As stated previously the CROSS APPLY operator will operate like and INNER JOIN.  Therefore, the record for Jim Smith is omitted from the first result set because the value used as the input parameter is NULL and the function we are using only returns data if data is input.

I hope that you have found this useful.  As always, if you have any questions about how this can apply to your situation, stop by our forums.  If you would prefer you may also email me at BrandonGalderisi {AT} our website dot com.

create table [dbo].[test_data_1]
     (ident         int identity primary key clustered
     ,delimited     varchar(100)
     )
GO
insert into [dbo].[test_data_1](delimited) values('Galderisi,Brandon')
insert into [dbo].[test_data_1](delimited) values('Chapman,Tim')
insert into [dbo].[test_data_1](delimited) values('Wills,Mark')
GO