union & union all difrence

最新推荐文章于 2024-09-21 01:15:39 发布

转载最新推荐文章于 2024-09-21 01:15:39 发布 · 165 阅读

本文详细介绍了SQL中UNION和UNION ALL命令的区别及使用方法。UNION用于从两个表中选择不同数据行，而UNION ALL则返回所有记录，包括重复项。通过示例展示了如何使用这两个命令来获取挪威和美国员工的不同名称列表。

一直比较少注意到的一个问题

[@more@]

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.

Note: With UNION, only distinct values are selected.

SQL Statement 1
UNION
SQL 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

Example

List all different employee names in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION
SELECT 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.

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

SQL Statement 1
UNION ALL
SQL Statement 2

Using the UNION ALL Command

Example

List all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/202861/viewspace-977390/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/202861/viewspace-977390/