ITPub博客

首页 > 应用开发 > IT综合 > union & union all difrence

union & union all difrence

原创 IT综合 作者:playwawa 时间:2007-10-18 10:16:16 0 删除 编辑

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

[@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_IDE_Name
01Hansen, Ola
02Svendson, Tove
03Svendson, Stephen
04Pettersen, Kari

Employees_USA:

E_IDE_Name
01Turner, Sally
02Kent, Clark
03Svendson, Stephen
04Scott, 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Oracle Hint的用法
请登录后发表评论 登录
全部评论
  • 博文量
    105
  • 访问量
    1173326