I use APPLY to Join a Table Valued Function with rows from another table. The example passes each customer’s delivery city.
In the SQL Server 2016 WideWorldImporters sample, Application.DetermineCustomerAccess accepts a city ID. Qualifying c.DeliveryCityID makes the relationship explicit. The sample database must be installed before this script can run.
USE WideWorldImporters;
SELECT c.CustomerName, a.AccessResult
FROM Sales.Customers AS c
CROSS APPLY Application.DetermineCustomerAccess(c.DeliveryCityID) AS a;
-- Keep customers even if the function returns no row:
SELECT c.CustomerName, a.AccessResult
FROM Sales.Customers AS c
OUTER APPLY Application.DetermineCustomerAccess(c.DeliveryCityID) AS a;CROSS APPLY includes a customer only when the function returns at least one row. OUTER APPLY retains a customer with NULL function columns when the function returns no rows. If a function returns several rows for a city, either form can produce several output rows for that customer.
APPLY allows the right-hand expression to reference columns from the left-hand row source. The optimizer may transform the query; the syntax does not promise a separate physical function execution for every row. An inline function can be expanded into the query. Multi-statement functions have different optimization behavior, including version-dependent improvements, so measure with representative data.
Reference: FROM and APPLY.
APPLY syntax is not a physical execution count, it is a way to express a correlated row source.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
15 Comments. Leave new
We can do normal inner join table value function. CROSS APPLY is where we don’t have any join condition for example, we have table1 and table2. table1 has a column called rowcount. For each row from table1 we need to select first rowcount rows from table2, ordered by table2.id
Hi Pinal. I suscribed your blog from long time ago and I’m curious about this post because this is the first time I see a function like this CROSS APPLY [Application].[DetermineCustomerAccess] (DeliveryCityID) on a SQL query. Is this a property of sql server databases or can be found in another databases like mysql/oracle?
Regards and thanks for your article. Sorry if my question is silly.
Hi,
You can’t use the normal inner join if you had to pass the parameter to the function from the first table.
Hi Pinal, Can you please confirm this?
I am not able to understand this question.
Hi Pinal,
I’ve been playing around with CROSS APPLY and experienced performance issues. When I rewrote my code using a SP with temporary tables, it got much quicker – like 90 seconds with CROSS APPLY to 3 seconds with the SP. Are functions generally slower than procedures?
Regards
Nils
Yes my experience is that functions are generally slower than procedures.
For some reason scaler functions are a lot slower than table functions, at least in SQL Server 2012
That’s because the CROSS APPLY runs once for each row
Cross and Outer apply used with table valued function. Its behave like Inner and outer join respectively. Below is query example to make it clear.
IF(OBJECT_ID(‘dbo.emp’)) IS NOT NULL
DROP TABLE emp
create table emp
(
EmpID int,
EmpName varchar(20),
DepID int
)
INSERT INTO emp
SELECT 1,’Rikhil’,1
UNION ALL
SELECT 2,’Mehul’,2
UNION ALL
SELECT 3,’Neeraj’,NULL
GO
IF(OBJECT_ID(‘dbo.Dep’)) IS NOT NULL
DROP TABLE Dep
create table Dep
(
DepID int,
DepName varchar(20)
)
INSERT INTO Dep
SELECT 1,’IT’
UNION ALL
SELECT 2,’Mechanical’
UNION ALL
SELECT 3,’Electrical’
GO
IF(OBJECT_ID(‘dbo.GetEmpDep’)) IS NOT NULL
DROP FUNCTION GetEmpDep
GO
CREATE FUNCTION GetEmpDep(@DepID int)
RETURNS TABLE
AS
RETURN
select DepID ,
DepName
from Dep
WHERE DepID=@DepID
GO
select ‘CROSS APPLY behave Like Inner join’ AS Title,*
from emp
CROSS APPLY GetEmpDep(DepID)
select ‘OUTER APPLY behave Like Outer join’ AS Title,*
from emp
OUTER APPLY GetEmpDep(DepID)
Great example.
Thank you. :)
thank you
Hi , Pinal,
i want to first value , middle value and last value in datetime column, im getting first and last by using min n max function, but im not getting middle value ie in between 12-1 pm
im using it for attendance project if middle punch is not there then it should be null but
pleaes do needful
👍 Thank you. Good helpful tip