首页 新闻 搜索 专区 学院

关于union查询一个纠结的问题

0
[待解决问题]

select 'A', '', '' from dual
union
select 'B', '', '' from dual;

 结果是这样:

A  NULL NULL

B  NULL NULL

select p1.t1, p2.t2, p2.t3 from
(select 'A' as t1,'' as t2,'' as t3 from dual ) p1 join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1
union all
select p1.t1, p2.t2, p2.t3 from
(select 'B' as t1,'' as t2,'' as t3 from dual ) p1 join
(select '' as t1, '' as t2, '' as t3 from dual where 1=2) p2 on p1.t1 = p2.t1

希望的结果和上面的sql的结果是一样的,但是,查询出来确实空。

测试数据库是oracle。

如果把union all 换成union, 结果变成期望的效果。

WHY?

java_win的主页 java_win | 菜鸟二级 | 园豆:202
提问于:2014-04-16 21:06
< >
分享
所有回答(1)
0

UNION表示“并”,当用的时候,系统会自动将重复的元组去掉,如果要保留重复元组则就用UNION ALL。

FortuneGril | 园豆:300 (菜鸟二级) | 2014-04-22 16:44
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册