CROSS APPLY & OUTER APPLY
CROSS APPLY & OUTER APPLY
CREATE TABLE [dbo].[Account](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Age] [int] NULL,
[Gender] [int] NULL,
[BirthDate] [datetime] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Account]([UserId], [Name], [Age], [Gender], [BirthDate]) VALUES (1, N'User01', 21, 1, '2000-11-02 15:12:30.000');
INSERT INTO [dbo].[Account]([UserId], [Name], [Age], [Gender], [BirthDate]) VALUES (2, N'User02', 19, 0, '2002-02-02 15:12:30.000');
INSERT INTO [dbo].[Account]([UserId], [Name], [Age], [Gender], [BirthDate]) VALUES (3, N'User03', 20, 1, '2001-03-02 15:12:30.000');
GO
CREATE TABLE [dbo].[Message](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NULL,
[Message] [nvarchar](200) NULL,
[IsRead] [bit] NULL,
[CreateTime] [datetime] NULL,
CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Message]([Id], [UserId], [Message], [IsRead], [CreateTime]) VALUES (1, 1, N'Hello!', '0', '2021-11-28 15:16:14.000');
INSERT INTO [dbo].[Message]([Id], [UserId], [Message], [IsRead], [CreateTime]) VALUES (2, 1, N'First Message', '0', '2021-11-28 15:17:07.000');
INSERT INTO [dbo].[Message]([Id], [UserId], [Message], [IsRead], [CreateTime]) VALUES (3, 1, N'Second Message', '0', '2021-11-28 15:17:32.000');
INSERT INTO [dbo].[Message]([Id], [UserId], [Message], [IsRead], [CreateTime]) VALUES (4, 3, N'Hello!', '0', '2021-11-27 15:17:51.000');
GO
INNER JOIN
内连结,左右两表中交叉存在的交集记录.不会出现NULL
SELECT msg.Id,ac.Name,msg.Message,msg.IsRead,msg.CreateTime FROM dbo.Message AS msg INNER JOIN dbo.Account AS ac ON ac.UserId =msg.UserId
| Id | Name | Message | IsRead | CreateTime |
|---|---|---|---|---|
| 1 | User01 | Hello! | 0 | 2021-11-28 15:16 |
| 2 | User01 | First Message | 0 | 2021-11-28 15:17 |
| 3 | User01 | Second Message | 0 | 2021-11-28 15:17 |
| 4 | User03 | Hello! | 0 | 2021-11-27 15:17 |
LEFT JOIN
左侧为主表,进行匹配.如果右侧没有,则显示NULL
SELECT msg.Id,ac.Name,msg.Message,msg.IsRead,msg.CreateTime FROM dbo.Message AS msg LEFT JOIN dbo.Account AS ac ON ac.UserId =msg.UserId
| Id | Name | Message | IsRead | CreateTime |
|---|---|---|---|---|
| 1 | User01 | Hello! | 0 | 2021-11-28 15:16 |
| 2 | User01 | First Message | 0 | 2021-11-28 15:17 |
| 3 | User01 | Second Message | 0 | 2021-11-28 15:17 |
| 4 | User03 | Hello! | 0 | 2021-11-27 15:17 |
OUTER JOIN
RIGHT OUTER JOIN:
右侧表为主表,进行匹配.如果左侧没有,则显示NULL.
查询结果为右表的并集.
LEFT OUTER JOIN:
左侧表为主表,查询结果为左表的并集.
SELECT msg.Id,ac.Name,msg.Message,msg.IsRead,msg.CreateTime FROM dbo.Message AS msg RIGHT OUTER JOIN dbo.Account AS ac ON ac.UserId =msg.UserId
| Id | Name | Message | IsRead | CreateTime |
|---|---|---|---|---|
| 1 | User01 | Hello! | 0 | 2021-11-28 15:16 |
| 2 | User01 | First Message | 0 | 2021-11-28 15:17 |
| 3 | User01 | Second Message | 0 | 2021-11-28 15:17 |
| NULL | User02 | NULL | NULL | NULL |
| 4 | User03 | Hello! | 0 | 2021-11-27 15:17 |
CROSS APPLY
如果不考虑外部表中的聚合函数.整体实现效果与INNER JOIN差不多.
通过连结查询,优先进行left外部表计算,然后在保持left外部表所有数据行的基础上,对right表进行查询匹配.
CROSS APPLY 可以根据当前左表的当前记录去查询右表;
INNER JOIN 是根据左表的当前记录匹配右表整个结果集;
执行查询/运算的顺序上,比INNER JOIN更严格.
查询结果与INNER JOIN一致,为左右两表的交集.
SELECT msg.Id,ac.Name,msg.Message,msg.IsRead,msg.CreateTime FROM dbo.Account AS ac CROSS APPLY (SELECT TOP (2) * FROM dbo.Message WHERE ac.UserId=UserId ORDER BY CreateTime DESC) msg
| Id | Name | Message | IsRead | CreateTime |
|---|---|---|---|---|
| 3 | User01 | Second Message | 0 | 2021-11-28 15:17 |
| 2 | User01 | First Message | 0 | 2021-11-28 15:17 |
| 4 | User03 | Hello! | 0 | 2021-11-27 15:17 |
OUTER APPLY
外联结,如果不考虑外部表中的聚合函数,与OUTER JOIN差不过.
与OUTER JOIN的区别在于,OUTER APPLY的左右表执行/运算顺序.
OUTER APPLY 可以根据当前左表的当前记录去查询右表;
INNER JOIN 是根据左表的当前记录匹配右表整个结果集;
查询结果与OUTER JOIN一致,为左/右表的并集.
SELECT msg.Id,ac.Name,msg.Message,msg.IsRead,msg.CreateTime FROM dbo.Account AS ac OUTER APPLY (SELECT TOP (2) * FROM dbo.Message WHERE ac.UserId=UserId ORDER BY CreateTime DESC) msg
| Id | Name | Message | IsRead | CreateTime |
|---|---|---|---|---|
| 3 | User01 | Second Message | 0 | 2021-11-28 15:17 |
| 2 | User01 | First Message | 0 | 2021-11-28 15:17 |
| NULL | User02 | NULL | NULL | NULL |
| 4 | User03 | Hello! | 0 | 2021-11-27 15:17 |
OUTER APPLY
运算测试1
测试案例
WITH m AS (
SELECT
'数字' TypeGroup,
'1,2,3,4,5,6,7,8,9' info UNION ALL
SELECT
'字母' TypeGroup,
'a,b,c,d,e,f,g,h,i' info
)
SELECT * FROM m;
| TypeGroup | info |
|---|---|
| 数字 | 1,2,3,4,5,6,7,8,9 |
| 字母 | a,b,c,d,e,f,g,h,i |
WITH m AS (
SELECT
'数字' TypeGroup,
'1,2,3,4,5,6,7,8,9' info UNION ALL
SELECT
'字母' TypeGroup,
'a,b,c,d,e,f,g,h,i' info
)
-- SELECT * FROM m;
SELECT A.TypeGroup,B.info
FROM(
select TypeGroup, CONVERT(xml,'<root><v>' + REPLACE(info, ',', '</v><v>') + '</v></root>') as info
from m
) A
OUTER APPLY(
SELECT info = N.c.value('.', 'varchar(8000)')
FROM A.info.nodes('/root/v') N(c)
) B
| TypeGroup | info |
|---|---|
| 数字 | 1 |
| 数字 | 2 |
| 数字 | 3 |
| 数字 | 4 |
| 数字 | 5 |
| 数字 | 6 |
| 数字 | 7 |
| 数字 | 8 |
| 数字 | 9 |
| 字母 | a |
| 字母 | b |
| 字母 | c |
| 字母 | d |
| 字母 | e |
| 字母 | f |
| 字母 | g |
| 字母 | h |
| 字母 | i |
测试运算2
WITH Z AS (
SELECT '奇数' [Type],'1' [Value] UNION ALL
SELECT '奇数' [Type],'3' [Value] UNION ALL
SELECT '奇数' [Type],'5' [Value] UNION ALL
SELECT '奇数' [Type],'7' [Value] UNION ALL
SELECT '偶数' [Type],'2' [Value] UNION ALL
SELECT '偶数' [Type],'4' [Value] UNION ALL
SELECT '偶数' [Type],'6' [Value] UNION ALL
SELECT '偶数' [Type],'8' [Value]
)
SELECT * FROM
(SELECT DISTINCT [Z].[Type] FROM Z) A
OUTER APPLY (
SELECT [VALUES]=CAST((SELECT [Z].[Value] FROM Z WHERE Z.[Type]=A.[Type] FOR XML AUTO) AS NVARCHAR(MAX))
) B
| Type | VALUES |
|---|---|
| 偶数 | |
| 奇数 |
