CROSS APPLY versus OUTER APPLY

OUTER APPLY returns all rows from the outer table along with the results returned by
the function when rows match.
while CROSS APPLY returns only the rows from the
outer table where a match exists within the function results.

Here we can you match 2 table or we match by using function

Example 1:
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]
------------

EXAMPLE2:

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

*/

Reference
http://www.sqlusa.com/articles2005/crossapplyversusouterapply/