How to differentiate union and join statements in MySQL _ MySQL

Source: Internet
Author: User
Tags mysql tutorial
This article mainly introduces the usage differences between union and join statements in MySQL, and illustrates the different functions of union and join in connection operations, for more information, see union and join, which are commonly used to join multiple tables. I don't want to talk about the specific concept. I just want to know how to query it online, because I cannot remember it accurately.
Difference: The union operation on two tables combines the number of data records, which is equal to vertical, the two table fields must be the same (Schema of both sides of union shocould match .). That is to say, if Table A has three pieces of data and Table B has two pieces of data, then A union B has five pieces of data. The difference between union and union all indicates that if union has the same data record, union all will not merge the same data record, there will be as many records as you need. For example, run the following statement in mysql:


select * from tmp_libingxue_a;name numberlibingxue 1001yuwen 1002select * from tmp_libingxue_b;name numberlibingxue 1001feiyao 1003select * from tmp_libingxue_a union select * from tmp_libingxue_b;libingxue 1001yuwen 1002feiyao 1003select * from tmp_libingxue_a union all select * from tmp_libingxue_b;libingxue 1001yuwen 1002libingxue 1001feiyao 1003


However, this cannot be executed in hive. execute select * from tmp_libingxue_a union all select * from tmp_libingxue_ B; and failed. in hive, union must be performed in the subquery. For example


select * from (select * from tmp_yuwen_a union all select * from tmp_yuwen_b) t1;


Note: it must be union all. if union is used separately, it will prompt you that ALL is missing, and t1 must be followed. you can write it as a or B, but it must be written. if it is not written, an error will occur.
The join operation is a horizontal join operation, which is only biased towards the join operation. Compared with union, join is more loose and does not require the fields of two tables. join without restrictions is equal to the Cartesian product of two tables. all join operations must be constrained by restrictions, the limited join operation is a horizontal expansion. Join statements that meet the conditions are extracted and directly filtered out if the conditions are not met. The usage can be flexible. There are two simple examples below:


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;


The usage of left outer join is similar to that of right outer join. The difference is that left outer join selects all the fields in the left table, and the fields in the right table select the fields that meet the conditions, if not, leave all the values blank. that is to say, the table on the left is used as a reference. Right outer join is also referenced in the right table. The difference between the three Joins has been said many times, and there are more detailed explanations on the Internet.
Similarities: in some specific cases, join can be used to implement the union all function, which is conditional, in this case, select union all or group by to check the situation or the consumption of the two. Although SQL is just a few keywords, it is changeable and powerful. you can use it as long as you can implement the desired functions. The SQL statement for requirements is simply reproduced as follows:


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;



sudo -u taobao hadoop dfs -rmr /group/tbads/warehouse/tmp_libingxue_result/pt=20041104sudo -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



hive> select * from tmp_libingxue_resource;



OK2001 0  11  101  200411042002 0  11  102  20041104



hive> select * from tmp_libingxue_result;



OK2001 0  12  103  200411042002 0  12  104  20041104



select user_id,shop_id,max(auction_id),max(search_time)from(select * from tmp_libingxue_resource union allselect * from tmp_libingxue_result )t1group by user_id,shop_id;



2001 0  12  1032002 0  12  104



select t1.user_id,t1.shop_id,t2.auction_id,t2.search_timefrom(select * from tmp_libingxue_resource) t1join(select * from tmp_libingxue_result) t2on t1.user_id=t2.user_id and t1.shop_id=t2.shop_id;



2001 0  12  1032002 0  12  104



Through the previous introduction, UNION is used to perform operations on the result set of a table, and JOIN is used to connect multiple tables. The two are essentially different.
The following is an example of using the UNION operator to connect two table records.
Typical UNION operations for two-table records


Assume that two tables, Table3 and Table4, contain the following columns and data.

Table1 database table

Table 2 database table

Table 1 and Table 2 have the same column structure. Therefore, you can use the UNION operator to join the record sets of the two tables. The following table shows the join result.

Use UNION to connect records in Table 3 and Table 4

The implementation code of the above connection process can be expressed as follows:


SELECT *FROM Table1UNIONSELECT *FROM Table2

The above is the difference between the use of the union and join statements in MySQL tutorial _ MySQL content. For more information, please follow the PHP Chinese network (www.php1.cn )!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.