KingbaseES数据库改写SQL Server数据库CROSS APPLY和OUTER APPLY
一、功能介绍：
CROSS APPLY和OUTER APPLY是SQL Server中的一种连接操作，类似于JOIN语句可以将一张表与一个表函数或一个子查询进行关联。表函数是一种返回一个表类型的数据的函数，子查询是一个嵌套在外部查询中的查询。它们可以与表值函数或子查询配合使用，返回左表和右表的匹配结果。CROSS APPLY只返回有匹配结果的左表行，而OUTER APPLY返回所有的左表行，没有匹配结果的用NULL填充。
1.CROSS APPLY和OUTER APPLY的区别在于处理不匹配的行的方式：
- CROSS APPLY只返回左表中与右表或函数匹配的行，类似于INNER JOIN。
- OUTER APPLY返回左表中所有的行，如果没有与右表或函数匹配的行，则用NULL填充，类似于LEFT OUTER JOIN。
CROSS APPLY和OUTER APPLY的语法如下：
SELECT column_list
FROM table1
CROSS APPLY table_valued_function(table1.column)
-- or
SELECT column_list
FROM table1
CROSS APPLY (subquery) AS alias
SELECT column_list
FROM table1
OUTER APPLY table_valued_function(table1.column)
-- or
SELECT column_list
FROM table1
OUTER APPLY (subquery) AS alias
2.CROSS APPLY和OUTER APPLY的用途有：
- 与表函数关联，如使用系统函数或者自定义函数将一张表与一个返回表类型数据的函数进行关联，从而实现一对多的关系。
- 与子查询关联，如使用聚合函数或窗口函数来计算每行的统计信息。
- 重用列别名，如使用计算一个列的表达式，并在后续引用该表达式。
二、SQL Server数据库：
在SQL Server数据库创建tb1、tb2表及插入测试数据
create table tb1(id decimal(6,0),info varchar(10),age decimal(4,0));
insert into tb1 values(1,'A',22);
insert into tb1 values(2,'B',16);
insert into tb1 values(3,'C',28);
create table tb2(id decimal(6,0),info varchar(10),age decimal(4,0));
insert into tb2 values(1,'A',22);
insert into tb2 values(2,'B',16);
insert into tb2 values(6,'D',35);
2.1：与子查询关联：
使用CROSS APPLY查询：
--将两个表直接连接，不需要任何的关联条件，产生的结果就是这两张表的笛卡儿集
SELECT d.* FROM tb1 d CROSS APPLY tb2;
--将表tb1与表tb2使用子查询连接，使用左表的字段作为子查询的条件
SELECT * FROM tb1 d
CROSS APPLY (select * from tb2 where id=d.id) b;
返回结果：
- CROSS APPLY 操作仅返回左表表达式（在其最终输出中）中与右表表达式匹配的那些行。 CROSS APPLY 类似于 INNER JOIN，更准确地说类似于具有相关子查询的 CROSS JOIN，其隐式联接条件为 1=1。
SELECT * FROM tb1 a
cross join tb2 b
where a.id=b.id ;
使用OUTER APPLY查询：
SELECT * FROM tb1 d
OUTER APPLY (select * from tb2 where id=d.id) b;
返回结果：
- OUTER APPLY 操作返回左表表达式中的所有行，不管其与右表表达式的匹配情况。 对于右表表达式中没有相应匹配项的那些行，在右表表达式的列中返回 NULL 值。 如果没有与右表或函数匹配的行，则用NULL填充。OUTER APPLY 等效于 LEFT OUTER JOIN。
SELECT * FROM tb1 a
LEFT OUTER JOIN tb2 b
on a.id=b.id ;
2.2：与表函数关联：
CREATE TABLE EPD (
EmpId int PRIMARY KEY,
EmpFirstName VARCHAR(50),
EmpLastName VARCHAR(50),
Department VARCHAR(50),
DepartID INT
);
CREATE TABLE EPS (
EmpID INT,
EmpFullName VARCHAR(80),
EmpSalary INT,
EmpWorkingYears INT,
DepartID INT
);
insert into EPD values(1001,'Kate','Thomas','IT',2);
insert into EPD values(1002,'John','Wills','IT',2);
insert into EPD values(1003,'Branda','Pat','Accounts',3);
insert into EPD values(1004,'Sofia','Kaul','HR',1);
insert into EPD values(1005,'Tim','Stout','IT',2);
insert into EPD values(1006,'Mick','Presto','Accounts',3);
insert into EPD values(1007,'Nwwhile','Nwwhile','Nwwhile',NULL);
insert into EPS values(1001,'Kate Thimas',35000,3,2);
insert into EPS values(1002,'John Wills',25000,2,2);
insert into EPS values(1003,'Branda Pat',20000,2,3);
insert into EPS values(1004,'Sofia Kaul',18000,1,1);
insert into EPS values(1005,'Tim Stout',25000,2,2);
insert into EPS values(1006,'Mick Presto',28000,3,3);
insert into EPS values(null,'Nwwhile Nwwhile',8000,1,NULL);
insert into EPS values(null,'Hello World',5000,1,NULL);
CREATE FUNCTION fn_Salar (@DepartmentID int)
RETURNS TABLE
AS RETURN
(
SELECT
EmpID, EmpFullName,
EmpSalary+5000 AS Salaryinc
FROM EPS
WHERE DepartID = @DepartmentID
);
使用CROSS APPLY查询：
--执行此查询看返回是否符合预期
SELECT EmpID, Salaryinc FROM fn_Salar(2)
--使用CROSS APPLY关联表值函数fn_Salar
SELECT e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM EPD AS e
CROSS APPLY fn_Salar (e.DepartID) AS f
返回结果：
使用OUTER APPLY查询：
--执行此查询看返回是否符合预期
SELECT EmpID, Salaryinc FROM fn_Salar(2)
--使用CROSS APPLY关联表值函数fn_Salar
SELECT e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM EPD AS e
OUTER APPLY fn_Salar (e.DepartID) AS f
返回结果：
2.3：引用列别名：
使用CROSS APPLY查询：
--直接在CROSS APPLY查询引用查询出的列进行计算
select p.*,calc_salay
FROM EPS AS p
CROSS APPLY (select (p.EmpSalary/1000)) s(calc_salay)
CROSS APPLY (select * from EPD where EmpID=p.EmpID) f
返回结果：
使用OUTER APPLY查询：
select p.*,calc_salay
FROM EPS AS p
OUTER APPLY (select (p.EmpSalary/1000)) s(calc_salay)
OUTER APPLY (select * from EPD where EmpID=p.EmpID) f
返回结果：
2.4：SQL Server数据库CROSS APPLY、OUTER APPLY总结：
- CROSS APPLY仅返回左表表达式（在其最终输出中）中与右表表达式匹配的那些行。 CROSS APPLY 类似于 INNER JOIN，更准确地说，类似于具有相关子查询的 CROSS JOIN，其隐式联接条件为 1=1。
- OUTER APPLY返回左表表达式中的所有行，而不管其与右表表达式的匹配情况。 对于右表表达式中没有相应匹配项的那些行，它在右表表达式的列中返回 NULL 值。 因此OUTER APPLY 等效于 LEFT OUTER JOIN。
- 当右侧有一个表值函数或子查询并且你希望为左侧表表达式中的每一行计算此表值函数或子查询时，就需要使用 APPLY。 在某些情况下使用 APPLY 运算可以提高查询性能。
三、KingbaseES数据库实现CROSS APPLY、OUTER APPLY功能：
KingbaseES数据库使用lateral表达式可以在FROM子句中引用之前的表或子查询的列。lateral表表达式可以用来实现一些复杂的查询逻辑，如对每一行执行一个带参数的子查询，或者对多个函数返回的结果集进行联合。使用表连接+lateral可以实现CROSS APPLY、OUTER APPLY功能。
KingbaseES数据库创建tb1、tb2测试表：
create table tb1(id number(6,0),info varchar(10),age number(4,0));
insert into tb1 values(1,'A',22);
insert into tb1 values(2,'B',16);
insert into tb1 values(3,'C',28);
create table tb2(id number(6,0),info varchar(10),age number(4,0));
insert into tb2 values(1,'A',22);
insert into tb2 values(2,'B',16);
insert into tb2 values(6,'D',35);
3.1：lateral结合子查询：
SELECT d.* FROM tb1 d CROSS join tb2;
ID | INFO | AGE
----+------+-----
1 | A | 22
1 | A | 22
1 | A | 22
2 | B | 16
2 | B | 16
2 | B | 16
3 | C | 28
3 | C | 28
3 | C | 28
(9 rows)
--使用cross join结合lateral查询
SELECT * FROM tb1 d
CROSS JOIN lateral (select * from tb2 where id=d.id) b;
ID | INFO | AGE | ID | INFO | AGE
----+------+-----+----+------+-----
1 | A | 22 | 1 | A | 22
2 | B | 16 | 2 | B | 16
(2 rows)
--或者把lateral放from子句中
SELECT * FROM tb1 d,lateral(select * from tb2 where id=d.id);
ID | INFO | AGE | ID | INFO | AGE
----+------+-----+----+------+-----
1 | A | 22 | 1 | A | 22
2 | B | 16 | 2 | B | 16
(2 rows)
--使用left outer join结合lateral查询
SELECT * FROM tb1 d
LEFT OUTER JOIN lateral(select * from tb2 where id=d.id) b on true;
ID | INFO | AGE | ID | INFO | AGE
----+------+-----+----+------+-----
1 | A | 22 | 1 | A | 22
2 | B | 16 | 2 | B | 16
3 | C | 28 | | |
(3 rows)
--使用left join结合lateral查询
SELECT * FROM tb1 d
LEFT JOIN lateral(select * from tb2 where id=d.id) b on true;
ID | INFO | AGE | ID | INFO | AGE
----+------+-----+----+------+-----
1 | A | 22 | 1 | A | 22
2 | B | 16 | 2 | B | 16
3 | C | 28 | | |
(3 rows)
3.2：lateral结合函数查询：
准备环境：
CREATE TABLE EPD (
EmpId int PRIMARY KEY,
EmpFirstName VARCHAR(50),
EmpLastName VARCHAR(50),
Department VARCHAR(50),
DepartID INT
);
CREATE TABLE EPS (
EmpID INT,
EmpFullName VARCHAR(80),
EmpSalary INT,
EmpWorkingYears INT,
DepartID INT
);
insert into EPD values(1001,'Kate','Thomas','IT',2);
insert into EPD values(1002,'John','Wills','IT',2);
insert into EPD values(1003,'Branda','Pat','Accounts',3);
insert into EPD values(1004,'Sofia','Kaul','HR',1);
insert into EPD values(1005,'Tim','Stout','IT',2);
insert into EPD values(1006,'Mick','Presto','Accounts',3);
insert into EPD values(1007,'Nwwhile','Nwwhile','Nwwhile',NULL);
insert into EPS values(1001,'Kate Thimas',35000,3,2);
insert into EPS values(1002,'John Wills',25000,2,2);
insert into EPS values(1003,'Branda Pat',20000,2,3);
insert into EPS values(1004,'Sofia Kaul',18000,1,1);
insert into EPS values(1005,'Tim Stout',25000,2,2);
insert into EPS values(1006,'Mick Presto',28000,3,3);
insert into EPS values(null,'Nwwhile Nwwhile',8000,1,NULL);
insert into EPS values(null,'Hello World',5000,1,NULL);
CREATE or replace FUNCTION fn_Salar(DepartmentID int)
RETURNS TABLE (EmpID int, EmpFullName varchar2(80), Salaryinc int) AS
BEGIN
RETURN QUERY SELECT EmpID,EmpFullName,EmpSalary+5000 AS Salaryinc FROM EPS WHERE DepartID=DepartmentID;
END;
使用CROSS APPLY查询：
--执行此查询看返回是否符合预期
SELECT EmpID, Salaryinc FROM fn_Salar(2);
EMPID | EMPFULLNAME | SALARYINC
-------+-------------+-----------
1001 | Kate Thimas | 40000
1002 | John Wills | 30000
1005 | Tim Stout | 30000
(3 rows)
--使用CROSS APPLY关联表值函数fn_Salar
SELECT e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM EPD AS e
CROSS JOIN lateral fn_Salar (e.DepartID) AS f;
--或者
SELECT e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM EPD AS e
CROSS JOIN fn_Salar (e.DepartID) AS f;
--返回结果
EMPFIRSTNAME | EMPLASTNAME | SALARYINC
--------------+-------------+-----------
Kate | Thomas | 40000
Kate | Thomas | 30000
Kate | Thomas | 30000
John | Wills | 40000
John | Wills | 30000
John | Wills | 30000
Branda | Pat | 25000
Branda | Pat | 33000
Sofia | Kaul | 23000
Tim | Stout | 40000
Tim | Stout | 30000
Tim | Stout | 30000
Mick | Presto | 25000
Mick | Presto | 33000
(14 rows)
使用OUTER APPLY查询：
--使用CROSS APPLY关联表值函数fn_Salar
SELECT e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM EPD AS e
LEFT JOIN fn_Salar (e.DepartID) AS f on true;
--或者
SELECT e.EmpFirstName,
e.EmpLastName,
f.Salaryinc
FROM EPD AS e
LEFT JOIN lateral fn_Salar (e.DepartID) AS f on true;
--返回结果
EMPFIRSTNAME | EMPLASTNAME | SALARYINC
--------------+-------------+-----------
Kate | Thomas | 40000
Kate | Thomas | 30000
Kate | Thomas | 30000
John | Wills | 40000
John | Wills | 30000
John | Wills | 30000
Branda | Pat | 25000
Branda | Pat | 33000
Sofia | Kaul | 23000
Tim | Stout | 40000
Tim | Stout | 30000
Tim | Stout | 30000
Mick | Presto | 25000
Mick | Presto | 33000
Nwwhile | Nwwhile |
(15 rows)
3.3：引用列别名：
使用CROSS APPLY查询：
select p.*,calc_salay
FROM EPS AS p
CROSS JOIN lateral(select (p.EmpSalary/1000)) s(calc_salay)
CROSS JOIN lateral(select * from EPD where EmpID=p.EmpID) f;
--返回结果
EMPID | EMPFULLNAME | EMPSALARY | EMPWORKINGYEARS | DEPARTID | CALC_SALAY
-------+-------------+-----------+-----------------+----------+------------
1001 | Kate Thimas | 35000 | 3 | 2 | 35
1002 | John Wills | 25000 | 2 | 2 | 25
1003 | Branda Pat | 20000 | 2 | 3 | 20
1004 | Sofia Kaul | 18000 | 1 | 1 | 18
1005 | Tim Stout | 25000 | 2 | 2 | 25
1006 | Mick Presto | 28000 | 3 | 3 | 28
(6 rows)
使用OUTER APPLY查询：
select p.*,calc_salay
FROM EPS AS p
LEFT OUTER JOIN lateral(select (p.EmpSalary/1000)) s(calc_salay) on true
LEFT OUTER JOIN lateral(select * from EPD where EmpID=p.EmpID) f on true;
--或者
select p.*,calc_salay
FROM EPS AS p
LEFT JOIN lateral(select (p.EmpSalary/1000)) s(calc_salay) on true
LEFT JOIN lateral(select * from EPD where EmpID=p.EmpID) f on true;
--返回结果
EMPID | EMPFULLNAME | EMPSALARY | EMPWORKINGYEARS | DEPARTID | CALC_SALAY
-------+-----------------+-----------+-----------------+----------+------------
1001 | Kate Thimas | 35000 | 3 | 2 | 35
1002 | John Wills | 25000 | 2 | 2 | 25
1003 | Branda Pat | 20000 | 2 | 3 | 20
1004 | Sofia Kaul | 18000 | 1 | 1 | 18
1005 | Tim Stout | 25000 | 2 | 2 | 25
1006 | Mick Presto | 28000 | 3 | 3 | 28
| Nwwhile Nwwhile | 8000 | 1 | | 8
| Hello World | 5000 | 1 | | 5
(8 rows)
3.4：KingbaseES数据库lateral子查询使用场景
- 在from子句中使用一个带参数的函数，而参数来自于前面的表或子查询。
- 在from子句中使用一个聚合函数，而分组列来自于前面的表或子查询。
- 在from子句中使用一个窗口函数，而窗口分区列来自于前面的表或子查询。
KINGBASE研究院
