容易遗忘的一些小代码之 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)