准备两张测试表
09:41:52 kiwi@storedb1> select * from t1;
ID1 I
------------- -
1 A
2 B
3 C
Elapsed: 00:00:00.00
09:41:56 kiwi@storedb1> select * from t2;
I ID
- --
A A2
B B2
D D2
我们先来看看full outer join
09:42:14 kiwi@storedb1> select t1.id1,t1.id2,t2.id3 from t1 full outer join t2 on(t1.id2=t2.id2);
ID1 I ID
------------- - --
1 A A2
2 B B2
D2
3 C
再来看看union
09:44:54 kiwi@storedb1> select t1.id1,t1.id2,t2.id3 from t1 left outer join t2 on(t1.id2=t2.id2)
09:45:09 2 union
09:45:18 3 select t1.id1,t1.id2,t2.id3 from t1 right outer join t2 on(t1.id2=t2.id2);
ID1 I ID
------------- - --
1 A A2
2 B B2
3 C
D2
看看 union all
1 select t1.id1,t1.id2,t2.id3 from t1 left outer join t2 on(t1.id2=t2.id2)
2 union all
3* select t1.id1,t1.id2,t2.id3 from t1 right outer join t2 on(t1.id2=t2.id2)
09:46:20 kiwi@storedb1> /
ID1 I ID
------------- - --
1 A A2
2 B B2
3 C
1 A A2
2 B B2
D2
从中我们就可以看出区别full outer join 相当于分别做left outer join,right outer join 然后做一个union
union 是两个的交集不包括重复的行,默认进行排序
而union all的效果是left outer join和right outer join的交集,包括了重复的集合,不进行排序
理解full outer jion,union,union all
最新推荐文章于 2021-01-21 21:57:19 发布