The terminology used with CROSS APPLY and OUTER APPLY is somewhat misleading. A better name for CROSS APPLY would be INNER APPLY. The APPLY ( CROSS APPLY, OUTER APPLY) operator allows value(s) from the main (outer) query to be used as input to a table-valued function. The rows from the main query and table-valued function are combined. As an example, the orgchart subtree table-valued function may return 290 rows in subtree format for the CEO of AdventureWorks but only 10 rows for the marketing manager. The following T-SQL example scripts will demonstrate how CROSS APPLY and OUTER APPLY can be used in practical database applications.
In the following example we demonstrate CROSS APPLY & OUTER APPLY with group by derived table.
USE AdventureWorks;
DECLARE @Year INT,
@Month INT
SET @Year = 2003;
SET @Month = 9
-- SQL cross apply
-- SQL Server cross apply
-- SQL group by-- SQL correlated subquery
SELECT [Customer] = s.Name,
-- Special money data type currency formatting option
[TotalSalesforMonth] = '$' +
Convert(VARCHAR,Convert(MONEY,SalesAmount.OrderTotal),1)
FROM Sales.Customer AS c
-- The customer name is in this table
INNER JOIN Sales.Store AS s ON s.CustomerID = c.CustomerID
-- Cross apply
CROSS APPLY (SELECT soh.CustomerId,
Sum(sod.LineTotal) AS OrderTotal
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderId = soh.SalesOrderId
-- Correlation to the outer query
WHERE soh.CustomerId = c.CustomerId
-- Filter data AND Year(OrderDate) = @Year
AND Month(OrderDate) = @Month
GROUP BY soh.CustomerId) AS SalesAmount
ORDER BY [Customer]
/* Partial results
(174 row(s) affected)
Customer TotalSalesforMonth
Accessories Network $283.95
Acclaimed Bicycle Company $3,405.17
Action Bicycle Specialists $92,278.05
Advanced Bike Components $68,906.65
Aerobic Exercise Company $48.59
*/
-- SQL outer apply
-- SQL Server outer apply
SELECT [Customer] = s.Name,
-- Special money data type currency formatting option [TotalSalesforMonth] = '$' +
Convert(VARCHAR,Convert(MONEY,SalesAmount.OrderTotal),1) FROM Sales.Customer AS c
-- The customer name is in this table INNER JOIN Sales.Store AS s
ON s.CustomerID = c.CustomerID
OUTER APPLY (SELECT soh.CustomerId,
Sum(sod.LineTotal) AS OrderTotal
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderId = soh.SalesOrderId
-- Correlation to the outer query
WHERE soh.CustomerId = c.CustomerId
-- Filter data
AND Year(OrderDate) = @Year
AND Month(OrderDate) = @Month
GROUP BY soh.CustomerId) AS SalesAmount
ORDER BY [Customer]
GO
/* Partial results
(701 row(s) affected)
Customer TotalSalesforMonth
Acceptable Sales & Service NULL
Accessories Network $283.95
Acclaimed Bicycle Company $3,405.17
Ace Bicycle Supply NULL
Action Bicycle Specialists $92,278.05
Active Cycling NULL
*/
In the following example we demonstrate CROSS APPLY & OUTER APPLY with table-valued function. We start with creating a table-valued function. In the second step, we will create data and store it in a table variable. USE AdventureWorks;
GO
-- Minimum function
-- Maximum function
-- SQL minmax function
-- T-SQL table-valued function
CREATE FUNCTION fnMinimumMaximum
(@Input1 MONEY,
@Input2 MONEY)
RETURNS @MinMax TABLE (Minimum money, Maximum money)
AS
BEGIN
IF @Input1 is NULL and @Input2 is NULL RETURN
INSERT @MinMax
SELECT CASE
WHEN @Input1 < @Input2 THEN @Input1
WHEN @Input2 < @Input1 THEN @Input2
ELSE COALESCE(@Input1,@Input2)
END AS Minimum,
CASE
WHEN @Input1 > @Input2 THEN @Input1
WHEN @Input2 > @Input1 THEN @Input2
ELSE COALESCE(@Input1,@Input2)
END AS Maximum;
RETURN
END -- function
GO
-- SELECT * FROM dbo.fnMinimumMaximum(NULL, NULL)
-- SQL table variable create and population
-- Cross or outer apply will select the maximum dimension
DECLARE @Product TABLE (ProductName varchar(30), Width int, Height int)
INSERT @Product VALUES ( 'PortraitFrame', 8, 11)
INSERT @Product VALUES ( 'LandscapeFrame', 12, 7)
INSERT @Product VALUES ( 'Notepad Computer', NULL, NULL)
INSERT @Product VALUES ( 'CircleFrame', 10, 10)
INSERT @Product VALUES ( 'Ringbinder', NULL, NULL)
-- OUTER APPLY includes nomatches (null data)
-- SQL outer apply
-- Outer apply T-SQL
-- Outer apply mssql
-- SQL outer apply with table-valued function
SELECT ProductName, MaxDimension=Maximum
FROM @Product p
OUTER APPLY dbo.fnMinimumMaximum (Width, Height)
/*
ProductName MaxDimension
PortraitFrame 11.00
LandscapeFrame 12.00
Notepad Computer NULL
CircleFrame 10.00
Ringbinder NULL
*/
-- CROSS APPLY excludes nomatches (null data)
-- SQL cross apply
-- Cross apply T-SQL
-- Cross apply mssql
-- SQL cross apply with table-valued function
SELECT ProductName, MaxDimension=Maximum
FROM @Product p
CROSS APPLY dbo.fnMinimumMaximum (Width, Height)
GO
/*
ProductName MaxDimension
PortraitFrame 11.00
LandscapeFrame 12.00
CircleFrame 10.00
*/
------------
The following example applies a derived table from a correlated subquery instead of table-valued function (UDF).
------------
-- OUTER APPLY versus CROSS APPLY using derived table
------------
-- Create and populate tables for testing
USE tempdb;
GO
SET NOCOUNT ON;
CREATE TABLE Account (
AccountID INT IDENTITY PRIMARY KEY,
AccountName VARCHAR(50),
CreateDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP),
IsActive BIT DEFAULT (1))
GO
CREATE TABLE [Transaction] (
TransactionId INT IDENTITY PRIMARY KEY,
AccountID INT,
Amount MONEY,
TransactionDate SMALLDATETIME DEFAULT (getdate()))
GO
INSERT Account(AccountName) VALUES ('Roger Smith')
INSERT Account(AccountName) VALUES ('Linda White')
INSERT Account(AccountName) VALUES ('Corner Hardware')
INSERT Account(AccountName) VALUES ('Laptop Land')
INSERT Account(AccountName) VALUES ('Cellphone City')
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1400.0)
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1200.0)
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1300.0)
INSERT [Transaction] (AccountID, Amount) VALUES(1, 1100.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 400.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 200.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 300.0)
INSERT [Transaction] (AccountID, Amount) VALUES(2, 900.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 33400.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 11200.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 22300.0)
INSERT [Transaction] (AccountID, Amount) VALUES(3, 12100.0)
GO
-- CROSS APPLY returns only matching data
-- SQL cross apply
-- SQL derived table
-- SQL correlated subquery
SELECT a.AccountName,
TopAmount=tt.Amount
FROM ACCOUNT a
CROSS APPLY (SELECT TOP ( 1 ) Amount
FROM [Transaction] t
WHERE t.AccountID = a.AccountID
ORDER BY Amount DESC) tt
ORDER BY AccountName
/*
AccountName TopAmount
Corner Hardware 33400.00
Linda White 900.00
Roger Smith 1400.00
*/
-- OUTER APPLY returns matching and non-matching data
-- SQL outer apply
SELECT a.AccountName,
TopAmount=tt.Amount
FROM ACCOUNT a
OUTER APPLY (SELECT TOP ( 1 ) Amount
FROM [Transaction] t
WHERE t.AccountID = a.AccountID
ORDER BY Amount DESC) tt
ORDER BY AccountName
/*
AccountName TopAmount
Cellphone City NULL
Corner Hardware 33400.00
Laptop Land NULL
Linda White 900.00
Roger Smith 1400.00
*/
-- Cleanup
DROP TABLE tempdb.dbo.Account
DROP TABLE tempdb.dbo.[Transaction]
------------
The following example applies AdventureWorks data. Following is the T-SQL code sample script to create a function to be used with "APPLY":
USE AdventureWorks;
USE AdventureWorks;
GO
-- SQL user-defined function
-- SQL table-valued function
CREATE FUNCTION Sales.fnCustomerOrderTotal
(@CustomerID INT)
RETURNS @Result TABLE(OrderTotal MONEY)
AS
BEGIN
INSERT @Result
SELECT SUM(sod.LineTotal) AS OrderTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.CustomerID = @CustomerID
GROUP BY soh.CustomerID
RETURN
END
GO
OUTER APPLY will return rows also for customers without order as nulls. In the example below null is translated into 0.0. This is logical businesswise.
-- SQL outer apply
-- SQL Server outer apply
-- Outer apply T-SQL
SELECT STORE = s.Name,
OrderTotal = convert(VARCHAR,isnull(cot.OrderTotal,0.0),1)
FROM Sales.Store AS s
JOIN Sales.Customer AS c
ON s.CustomerID = c.CustomerID
OUTER APPLY Sales.fnCustomerOrderTotal(c.CustomerID) AS cot
ORDER BY STORE
This is the partial result set:
| Store | OrderTotal |
| A Bicycle Association | 0 |
| A Bike Store | 85,177.08 |
| A Cycle Shop | 0 |
| A Great Bicycle Company | 9,055.29 |
| A Typical Bike Shop | 83,457.11 |
| Acceptable Sales & Service | 1,258.38 |
| Accessories Network | 2,165.79 |
| Acclaimed Bicycle Company | 7,300.83 |
Following is the way to use "CROSS APPLY". The result set will not contain 0.0 (null) orders.
-- SQL cross apply
-- SQL Server cross apply
-- Cross apply mssql
SELECT STORE = s.Name,
OrderTotal = convert(VARCHAR,isnull(cot.OrderTotal,0.0),1)
FROM Sales.Store AS s
JOIN Sales.Customer AS c
ON s.CustomerID = c.CustomerID
CROSS APPLY Sales.fnCustomerOrderTotal(c.CustomerID) AS cot
ORDER BY STORE
This is the partial result set:
| Store | OrderTotal |
| A Bike Store | 85,177.08 |
| A Great Bicycle Company | 9,055.29 |
| A Typical Bike Shop | 83,457.11 |
| Acceptable Sales & Service | 1,258.38 |
| Accessories Network | 2,165.79 |
| Acclaimed Bicycle Company | 7,300.83 |
| Ace Bicycle Supply | 3,749.13 |
| Action Bicycle Specialists | 321,752.83 |
| Active Cycling | 1,805.45 |
| Active Life Toys | 200,013.37 |
| Active Systems | 639.98 |
| Active Transport Inc. | 88,245.87 |
| Activity Center | 42,650.40 |
------------
In the following example the supervisory subtree function returns the entire orgchart for a supervisory personnel at any level. For CEO, Ken Sanchez, it returns 290 employees including himself. For non-supervisory staff the table-valued function returns an empty table.
CROSS APPLY returns only correlated data. Therefore, for marketing staff Mary Gibson it returns nothing.
OUTER APPLY will return non-correlated data as well, placing NULLs into the missing cells.
USE tempdb;
GO
-- Create table for demonstration
-- SQL select into create table
-- SQL inner join
SELECT e.EmployeeID,
e.Title,
StaffName = LastName + ', ' + FirstName,
ManagerID,
Department = d.Name
INTO Employee
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.HumanResources.EmployeeDepartmentHistory edh
ON e.EmployeeID = edh.EmployeeID
INNER JOIN AdventureWorks.HumanResources.Department d
ON edh.DepartmentID = d.DepartmentID
INNER JOIN AdventureWorks.Person.Contact c
ON e.ContactID = c.ContactID
WHERE edh.EndDate IS NULL
GO
SELECT COUNT(*) FROM Employee
GO
-- 290 - Entire staff of AdventureWorks Cycles
-- Function to return OrgChart for supervisors only
-- For non-supervisory staff it will have empty return
-- SQL table-valued function
-- SQL user-defined function UDF
-- Tree processing function
CREATE FUNCTION fnSupervisorySubTree
(@EmployeeID AS INT)
RETURNS @TREE TABLE(EmployeeID INT,
EmployeeName VARCHAR(35),
Department VARCHAR(30),
ManagerID INT,
ManagerName VARCHAR(35),
SubTreeLevel INT)
AS
BEGIN
-- SQL common table expression - CTE
-- SQL recursive CTE
WITH cteSubTree(EmployeeID,EmployeeName,Department,ManagerID,ManagerName,SubTreeLevel)
AS (-- Anchor (root) node
SELECT e1.EmployeeID,
e1.StaffName,
e1.Department,
e1.ManagerID,
e2.StaffName,
0
FROM Employee e1
LEFT JOIN Employee e2
ON (e1.ManagerID = e2.EmployeeID)
WHERE e1.EmployeeID = @EmployeeID
UNION ALL
-- Recursive nodes to leaf level
SELECT e1.EmployeeID,
e1.StaffName,
e1.Department,
e1.ManagerID,
e2.StaffName,
cte.SubTreeLevel + 1
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID
JOIN cteSubTree AS cte
ON e1.ManagerID = cte.EmployeeID)
-- Return results
INSERT INTO @TREE
SELECT *
FROM cteSubTree
-- IF NOTE SUPERVISOR, RETURN AN EMPTY SET (TABLE)
IF (SELECT COUNT(* ) FROM @TREE) = 1
DELETE @TREE
RETURN
END
GO
-- Test UDF - user-defined function
-- Generate orgchart for Marketing, David Bradley manager
SELECT EmpID = EmployeeID,
EmpName = EmployeeName,
Department,
MgrID = ManagerID,
MgrName = ManagerName,
SubLvl = SubTreeLevel
FROM dbo.fnSupervisorySubTree(6)
GO
/* Results
EmpID EmpName Department MgrID MgrName SubLvl
6 Bradley, David Marketing 109 Sánchez, Ken 0
2 Brown, Kevin Marketing 6 Bradley, David 1
46 Harnpa…, Sariya Marketing 6 Bradley, David 1
106 Gibson, Mary Marketing 6 Bradley, David 1
119 Williams, Jill Marketing 6 Bradley, David 1
203 Eminhizer, Terry Marketing 6 Bradley, David 1
269 Benshoof, Wanida Marketing 6 Bradley, David 1
271 Wood, John Marketing 6 Bradley, David 1
272 Dempsey, Mary Marketing 6 Bradley, David 1
*/
-- CROSS APPLY returns no row for staff Mary Gibson
-- SQL cross apply
SELECT Employee = e.StaffName,
e.Title,
oc.Department,
Staff = oc.EmployeeName,
Supervisor = oc.ManagerName
FROM Employee e
CROSS APPLY dbo.fnSupervisorySubTree(e.EmployeeID) AS oc
WHERE e.EmployeeID = 106
/*
Employee Title Department Staff Supervisor
*/
-- OUTER APPLY returns a row for staff Mary Gibson
-- SQL outer apply
SELECT Employee = e.StaffName,
e.Title,
oc.Department,
Staff = oc.EmployeeName,
Supervisor = oc.ManagerName
FROM Employee e
OUTER APPLY dbo.fnSupervisorySubTree(e.EmployeeID) AS oc
WHERE e.EmployeeID = 106
GO
/*
Employee Title Department Staff Supervisor
Gibson, Mary Marketing Specialist NULL NULL NULL
*/
-- Cleanup
DROP TABLE tempdb.dbo.Employee
DROP FUNCTION dbo.fnSupervisorySubTree
GO
No comments:
Post a Comment