SQL UNION ALL

SQL UNION ALL查询与SQL UNION查询类似,也用于合并两个或以上SQL SELECT查询结果。不同的是SQL UNION返回唯一不同的值,而SQL UNION ALL返回所有的值。

SQL UNION ALL查询语法

SELECT Column(s) FROM 表1
UNION ALL
SELECT Column(s) FROM 表2

SQL UNION ALL查询范例

数据表: FinanceEmployees

EmployeeId FirstName LastName
203 Kilooa Joooo
204 Hioiaaa Hifa
205 Yuiofa Iioooa
206 Miofa Poqqq

数据表: DevelopmentEmployees

EmployeeId FirstName LastName
206 Miofa Poqqq
207 Qifaww Siafoa
208 Ziooo Rrroo
209 UUifad Bbiiw

选择在FinanceEmployees和DevelopmentEmployees中所有的记录:

SELECT EmployeeId, FirstName, LastName FROM FinanceEmployees 
UNION ALL
SELECT EmployeeId, FirstName, LastName FROM DevelopmentEmployees

结果会类似于:

EmployeeId FirstName LastName
203 Kilooa Joooo
204 Hioiaaa Hifa
205 Yuiofa Iioooa
206 Miofa Poqqq
206 Miofa Poqqq
207 Qifaww Siafoa
208 Ziooo Rrroo
209 UUifad Bbiiw

我们可以看到在结果集中有一些相同的行。