Mysql学习MySQL中union和join语句使用区别的辨析教程

MYSQL入门
select * from tmp_libingxue_a;
name number
libingxue 1001
yuwen 1002
select * from tmp_libingxue_b;
name number
libingxue 1001
feiyao 1003
select * from tmp_libingxue_a union select * from tmp_libingxue_b;
libingxue 1001
yuwen 1002
feiyao 1003
select * from tmp_libingxue_a union all select * from tmp_libingxue_b;
libingxue 1001
yuwen 1002
libingxue 1001
feiyao 1003

MYSQL入门
select * from (select * from tmp_yuwen_a union all select * from tmp_yuwen_b) t1;

MYSQL入门
select * from (select * from tmp_yuwen_a)t1 join (select * from tmp_yuwen_b) t2;
select * from tmp_yuwen_a t1 join (select * from tmp_yuwen_b) t2; 

MYSQL入门
drop table tmp_libingxue_resource;
create external table if not exists tmp_libingxue_resource(
  user_id string,
  shop_id string,
  auction_id  string,
  search_time  string
)partitioned by (pt string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as sequencefile;
drop table tmp_libingxue_result;
create external table if not exists tmp_libingxue_result(
  user_id string,
  shop_id string,
  auction_id  string,
  search_time  string
)partitioned by (pt string)
row format delimited fields terminated by '\t'
lines terminated by '\n'
stored as sequencefile;
insert overwrite table tmp_libingxue_result where(pt=20041104) select * from tmp_libingxue_resource;

MYSQL入门
sudo -u taobao hadoop dfs -rmr /group/tbads/warehouse/tmp_libingxue_result/pt=20041104
sudo -u taobao hadoop jar /home/taobao/dataqa/framework/DailyReport.jar com.alimama.loganalyzer.tool.SeqFileLoader tmp_libingxue_resource.txt hdfs://v039182.sqa.cm4:54310/group/tbads/warehouse/tmp_libingxue_result/pt=20041104/part-00000 

MYSQL入门
hive> select * from tmp_libingxue_resource;

MYSQL入门
OK
2001 0  11  101  20041104
2002 0  11  102  20041104

MYSQL入门
hive> select * from tmp_libingxue_result;

MYSQL入门
OK
2001 0  12  103  20041104
2002 0  12  104  20041104

MYSQL入门
select user_id,shop_id,max(auction_id),max(search_time)
from
(select * from tmp_libingxue_resource 
union all
select * from tmp_libingxue_result )t1
group by user_id,shop_id;

MYSQL入门
2001 0  12  103
2002 0  12  104

MYSQL入门
select t1.user_id,t1.shop_id,t2.auction_id,t2.search_time
from
(select * from tmp_libingxue_resource) t1
join
(select * from tmp_libingxue_result) t2
on t1.user_id=t2.user_id and t1.shop_id=t2.shop_id;

MYSQL入门
2001 0  12  103
2002 0  12  104

通过前面的介绍,使用UNION对表的结果集进行并运算与使用JOIN对多表进行连接,二者有本质的不同.
下面给出一个使用UNION运算符连接二表记录的运算实例.
典型的二表记录的UNION运算

MYSQL入门
SELECT *
FROM Table1
UNION
SELECT *
FROM Table2