SQL Union和Union All的使用方法

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. UNION命令可以用来选择两个有关联的信息，和JOIN命令非常相似。然而当使用UNION命令时得保证所选择的栏目数据类型相同。 Note: With UNION, only distinct values are selected. 注意：使用UNION，只有不同的值会被选择出来。 SQL Statement 1UNIONSQL Statement 2 -------------------------------------------------------------------------------- Employees_Norway表: E_ID E_Name 01 Hansen, Ola 02 Svendson, Tove 03 Svendson, Stephen 04 Pettersen, Kari Employees_USA表: E_ID E_Name 01 Turner, Sally 02 Kent, Clark 03 Svendson, Stephen 04 Scott, Stephen -------------------------------------------------------------------------------- Using the UNION Command 使用UNION 命令 Example 举例 List all different employee names in Norway and USA: 列举在USA和Norway中不同的人员名字： SELECT E_Name FROM Employees_NorwayUNIONSELECT E_Name FROM Employees_USA Result 结果 E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Scott, Stephen Note: This command cannot be used to list all employees in Norway and USA. In the example above we have two employees with equal names, and only one of them is listed. The UNION command only selects distinct values. 注意：这个命令不能将Norway和USA中所有的人员列举出来。在上面的举例中两个表有相同人员名字的数据，最后列出来的只会是其中的一个。 -------------------------------------------------------------------------------- UNION ALL The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. UNION ALL命令等同于UNION命令，但UNION ALL会选择全部的值 SQL Statement 1UNION ALLSQL Statement 2 -------------------------------------------------------------------------------- Using the UNION ALL Command 使用 UNION ALL命令 Example举例 List all employees in Norway and USA: 列举出在Norway和USA中所有的员工： SELECT E_Name FROM Employees_NorwayUNION ALLSELECT E_Name FROM Employees_USA Result 结果 E_Name Hansen, Ola Svendson, Tove Svendson, Stephen Pettersen, Kari Turner, Sally Kent, Clark Svendson, Stephen Scott, Stephen