详解MySQL中UNION的用法
如果想选择其他几个表中的行或从一个单一的表作为一个单独的结果集行的几个集会，那么可以使用的UNION。
UNION在MySQL4.0以上版本才能可以使用。本节说明如何使用它。
假设有两个表，潜在和实际的客户列表，供应商购买耗材合并所有三个表中的姓名和地址，来创建一个单一的邮件列表。UNION提供了一种方法做到这一点。假设三个表有以下内容：
mysql>SELECT*FROMprospect; +---------+-------+------------------------+ |fname|lname|addr| +---------+-------+------------------------+ |Peter|Jones|482RushSt.,Apt.402| |Bernice|Smith|916MapleDr.| +---------+-------+------------------------+ mysql>SELECT*FROMcustomer; +-----------+------------+---------------------+ |last_name|first_name|address| +-----------+------------+---------------------+ |Peterson|Grace|16055SeminoleAve.| |Smith|Bernice|916MapleDr.| |Brown|Walter|86021stSt.| +-----------+------------+---------------------+ mysql>SELECT*FROMvendor; +-------------------+---------------------+ |company|street| +-------------------+---------------------+ |ReddyParts,Inc.|38IndustrialBlvd.| |Parts-to-go,Ltd.|213BCommercePark.| +-------------------+---------------------+
这不要紧，如果所有的三个表具有不同的列名。下面的查询演示了如何选择一下子从三个表的名称和地址：
mysql>SELECTfname,lname,addrFROMprospect ->UNION ->SELECTfirst_name,last_name,addressFROMcustomer ->UNION ->SELECTcompany,'',streetFROMvendor; +-------------------+----------+------------------------+ |fname|lname|addr| +-------------------+----------+------------------------+ |Peter|Jones|482RushSt.,Apt.402| |Bernice|Smith|916MapleDr.| |Grace|Peterson|16055SeminoleAve.| |Walter|Brown|86021stSt.| |ReddyParts,Inc.||38IndustrialBlvd.| |Parts-to-go,Ltd.||213BCommercePark.| +-------------------+----------+------------------------+
如果想选择所有记录，包括重复的，请ALL的第一个UNION关键字：
mysql>SELECTfname,lname,addrFROMprospect ->UNIONALL ->SELECTfirst_name,last_name,addressFROMcustomer ->UNION ->SELECTcompany,'',streetFROMvendor; +-------------------+----------+------------------------+ |fname|lname|addr| +-------------------+----------+------------------------+ |Peter|Jones|482RushSt.,Apt.402| |Bernice|Smith|916MapleDr.| |Grace|Peterson|16055SeminoleAve.| |Bernice|Smith|916MapleDr.| |Walter|Brown|86021stSt.| |ReddyParts,Inc.||38IndustrialBlvd.| |Parts-to-go,Ltd.||213BCommercePark.| +-------------------+----------+------------------------+