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

上一篇:#YCB#待做题目与填坑资料


下一篇:精通CSS version2笔记之⒈选择器