DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

CROSS APPLY versus OUTER APPLY

By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP: DBA, Database Developer, BI Developer

January 25, 2009

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. CROSS APPLY is frequently used in DMV / DMF queries:

 

-- Get query source code and cached plan DMV information

SELECT  ecp.*,

        est.text

FROM  sys.dm_exec_cached_plans ecp

  CROSS APPLY  sys.dm_exec_sql_text (ecp.plan_handle) AS est

------------

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;

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

------------

Related articles:

Using APPLY

Using T-SQL CROSS APPLY and OUTER APPLY

http://sqlusa.com/articles2005/crossapply/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE