CROSS APPLY 和outer apply
/*
CREATE TABLE A(id INT, NAME VARCHAR(30))
CREATE TABLE B(id INT, marks VARCHAR(30))
INSERT INTO A
SELECT 1,'周杰伦' union
SELECT 2,'周星驰' union
SELECT 3,'成龙' union
SELECT 4,'李连杰' union
SELECT 5,'洪金宝' union
SELECT 6,null union
SELECT 7,null
INSERT INTO b
SELECT 1,12 union
SELECT 3,55 union
SELECT 5,56 union
SELECT 6,null union
SELECT 11,28 union
SELECT 16,47 union
SELECT 25,null
*/
select * from A CROSS APPLY (select * from B where a.id=b.id) c
/*
id NAME id marks
1 周杰伦 1 12
3 成龙 3 55
5 洪金宝 5 56
6 NULL 6 NULL
*/
select * from A outer APPLY (select * from B where a.id=b.id) c
/*
id NAME id marks
1 周杰伦 1 12
2 周星驰 NULL NULL
3 成龙 3 55
4 李连杰 NULL NULL
5 洪金宝 5 56
6 NULL 6 NULL
7 NULL NULL NULL
*/
/*
DROP TABLE A
DROP TABLE B
*/