sql server 本机编译存储过程（内存优化表） 绕过不支持FULL OUTER JOIN 的限制的方法...

将FULL OUTER JOIN 转成left join，right join 和 union

select * from A

ID NAME
4 Spaghetti
1 Pirate
2 Monkey
3 Ninja

select * from B

ID NAME
3 Darth Vade
1 Rutabaga
2 Pirate
4 Ninja

SELECT dbo.A.id, dbo.A.name, dbo.B.id, dbo.B.name FROM dbo.A FULL OUTER JOIN dbo.B ON dbo.A.name = dbo.B.name

select dbo.A.id, dbo.A.name, dbo.B.id, dbo.B.name from dbo.A LEFT OUTER JOIN dbo.B ON dbo.A.name = dbo.B.name
union
select dbo.A.id, dbo.A.name, dbo.B.id, dbo.B.name from dbo.A right OUTER JOIN dbo.B ON dbo.A.name = dbo.B.name

select dbo.A.id, dbo.A.name, dbo.B.id, dbo.B.name from dbo.A LEFT OUTER JOIN dbo.B ON dbo.A.name = dbo.B.name

union all

select dbo.A.id, dbo.A.name, dbo.B.id, dbo.B.name from dbo.A right OUTER JOIN dbo.B ON dbo.A.name = dbo.B.name WHERE dbo.A.id IS null

 

这三个查询结果都是一样的

id1	name1	id2	name2
1	Pirate	2	Pirate
2	Monkey	NULL	NULL
3	Ninja	4	Ninja
4	Spaghetti	NULL	NULL
NULL	NULL	1	Rutabaga
NULL	NULL	3	Darth Vade

 

 

 

 

 

 

sql join的区别参考 https://www.cnblogs.com/logon/p/3748020.html

转载于:https://www.cnblogs.com/Martianhh/p/11248973.html