T-SQL筆記11_cross apply (inner join) 跟 outer apply(left outer join)

- 11月 11, 2020

在一些關聯表的串接邏輯日常開發中

最常見的就是不同表之間join來join去

而 cross / outer apply 跟 join的功能有異曲同工之妙

差異在於不需要ON 表1.欄位A = 表2.欄位A 這種語法

cross apply 就相當於 inner join

outer apply 就相當於 left outer join

我們創建測試資料暫存表

SQL:

USE [tempdb] 
GO
 
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Employee] 
END 
GO 

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Department]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Department] 
END 

CREATE TABLE [Department]( 
   [DepartmentID] [int] NOT NULL PRIMARY KEY, 
   [Name] VARCHAR(250) NOT NULL, 
) ON [PRIMARY] 

INSERT [Department] ([DepartmentID], [Name])  
VALUES (1, N'Engineering') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (2, N'Administration') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (3, N'Sales') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (4, N'Marketing') 
INSERT [Department] ([DepartmentID], [Name])  
VALUES (5, N'Finance') 
GO 

CREATE TABLE [Employee]( 
   [EmployeeID] [int] NOT NULL PRIMARY KEY, 
   [FirstName] VARCHAR(250) NOT NULL, 
   [LastName] VARCHAR(250) NOT NULL, 
   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID), 
) ON [PRIMARY] 
GO
 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (1, N'Orlando', N'Gee', 1 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (2, N'Keith', N'Harris', 2 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (3, N'Donna', N'Carreras', 3 ) 
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) 
VALUES (4, N'Janet', N'Gates', 3 ) 


select * from Department
select * from Employee

這是兩表的資料(部門及員工)

觀察一下測試語法比較

cross apply 就相當於 inner join

SQL

select * from Department D
inner join Employee E
ON D.DepartmentID = E.DepartmentID

select * from Department D
cross apply (
	select * from Employee E
	where E.DepartmentID = D.DepartmentID
) A

outer apply 就相當於 left outer join

SQL

select * from Department D 
left outer join Employee E 
ON D.DepartmentID = E.DepartmentID

select * from Department D
outer apply (
	select * from Employee E
	where E.DepartmentID = D.DepartmentID
) A