APPLY operator joins two table expressions. Also, I'll show how it differs from regular JOINs.
Operator: CROSS APPLY
Similar: INNER JOIN
When to Use: Use a CROSS APPLY when no easy join exists and when the right table is an expression or table-valued function.
It's like an INNER JOIN since rows must exist in both tables/expressions for SQL to return results.
When NOT to Use: If you can get the same results with an inner join, don't use a CROSS APPLY. It is not clear and leaves people scratching their heads.
Operator: OUTER APPLY
Similar : LEFT JOIN
When to Use : Use OUTER APPLY when no easy join exists and when the right table is an expression or table-valued function.
It's like a LEFT JOIN since rows do not need to exist on the right side to return results from the left table.
When NOT to Use: If you can get the same results with a LEFT OUTER JOIN, don't use an OUTER APPLY for the reason mentioned above.
DECLARE @test TABLE (ID int IDENTITY(1,1), val CHAR(1), dt DATETIME);
INSERT INTO @test (val, dt) VALUES
('A','2018-01-04 13:04:00'),
('B','2018-01-05 15:04:00'),
('C','2018-01-06 05:33:00'),
('D','2018-01-07 08:22:00'),
('A','2018-01-08 11:02:00'),
('B','2018-01-09 12:12:00'),
('C','2018-01-10 19:05:00'),
('D','2018-01-11 21:21:00');
--SELECT * FROM @test
SELECT T.*, fldt.minDt,fldt.maxDt,minDttttt,maxDttttt
FROM @test t
CROSS APPLY
(
SELECT MIN(dt) minDt, MAX(dt) maxDt
FROM @test t2
WHERE t.val = t2.val
) fldt
CROSS APPLY
(
SELECT MIN(dt) minDt, MAX(dt) maxDt
FROM @test t2
WHERE t.val = t2.val
) AnyNameAliace(minDttttt,maxDttttt);
--------------same work----------------
SELECT *
FROM @test t
INNER JOIN ( SELECT val, MIN(dt) minDt, MAX(dt) maxDt FROM @test group by val) t1(val,minDttt,maxDtttt) ON t1.val = t.val
order by 1
----------Top N per group queries (can be more efficient for some cardinalities)--------------------
--------from INNER JOIN, We used subquey and row_number apply rn<=2
SELECT pr.name,
pa.name
FROM sys.procedures pr
OUTER APPLY (SELECT TOP 2 *
FROM sys.parameters pa
WHERE pa.object_id = pr.object_id
ORDER BY pr.name) pa
ORDER BY pr.name,
pa.name
------------------Calling a Table Valued Function for each row in the outer query-----------------
SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
--------------------------------------Reusing a column alias---------------------------------------
SELECT number,
doubled_number,
doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)
------------------------- Unpivoting more than one group of columns-----------------
DECLARE @PivotTable TABLE
(
Id INT PRIMARY KEY,
Foo1 INT, Foo2 INT, Foo3 INT
);
INSERT INTO @PivotTable
VALUES
(1,1,2,3)
SELECT * FROM @PivotTable
SELECT PT.Id,Foo,value
FROM @PivotTable PT
CROSS APPLY (VALUES('Foo1',Foo1),('Foo2',Foo2),('Foo3',Foo3)) V(Foo,value)
SELECT Id,
Foo,
value
FROM @PivotTable
CROSS APPLY (SELECT 'Foo1', Foo1
UNION ALL
SELECT 'Foo2', Foo2
UNION ALL
SELECT 'Foo3', Foo3 ) V(Foo, value);
SELECT Id, FooName, ValueName
FROM @PivotTable
UNPIVOT
(
ValueName FOR FooName IN ( Foo1, Foo2, Foo3)
) AS unpvt;
------------------------------Misc..-------------------------------------------
Select * FROM (VALUES('A',1),('B',2)) V(Col,Value)
SELECT * FROM (
SELECT ED.firstname,ED.lastname FROM HR.Employees ED
) A(FName,LName)
--------------------------------------------------------------------------