容易遗忘的一些小代码之 Cross apply and Outer apply

总有些小代码用的时候很熟悉, 不用的时候时间长了, 再看到的时候就有些生了, 不知道是我一个人这样还是都是这样, 人老了, 好记性不如烂笔头吧!

-- Cross Apply and Out Apply
IF OBJECT_ID('AppDemo1','U') IS NOT NULL
DROP TABLE AppDemo1
GO
 
IF OBJECT_ID('AppDemo2','U') IS NOT NULL
DROP TABLE AppDemo2
GO
 
IF OBJECT_ID('dbo.UDF_AppDemo','TF') IS NOT NULL
DROP FUNCTION dbo.UDF_AppDemo
GO
 
-- Two tables for test
CREATE TABLE AppDemo1
(
   A VARCHAR(15),
   B VARCHAR(15)
)
 
CREATE TABLE AppDemo2
(
   C VARCHAR(15),
   D VARCHAR(15)
)
 
--Insert Testing Values
INSERT INTO AppDemo1 VALUES
('1','A'),
('3',NULL),
(NULL,'K')
 
INSERT INTO AppDemo2 VALUES
('1','A'),
('2','B'),
('1',NULL),
(NULL,'X')
 
-- Show records
SELECT * FROM AppDemo1

SELECT * FROM AppDemo2

-- Cross Join
SELECT * FROM AppDemo1
CROSS JOIN AppDemo2

-- Table-valued function
CREATE FUNCTION dbo.UDF_AppDemo(@ID AS VARCHAR(15))
RETURNS @VALUES TABLE
(
   E VARCHAR(15),
   F VARCHAR(15)
)
AS
BEGIN
   -- Extract records by ID
   INSERT INTO @VALUES
   SELECT *
   FROM AppDemo2 AS app
   WHERE app.C = @ID
  
   RETURN
END
GO
 
-- Simple cross join
SELECT *
FROM AppDemo1 AS app
CROSS JOIN UDF_AppDemo('1')

-- Cross apply
SELECT *
FROM AppDemo1 AS app
CROSS APPLY UDF_AppDemo(app.A)

-- Outer apply
SELECT *
FROM AppDemo1 AS app
OUTER APPLY UDF_AppDemo(app.A)