集合运算符

Oracle SQL 入门

Sara Billen

Curriculum Manager

什么是集合运算符？

$$

集合运算符将两条或多条 SELECT 查询的输出合并为一个结果。

$$

  • 连接子句合并表
    • 面向列
  • 集合运算符合并查询
    • 面向行
Oracle SQL 入门

集合运算符的类型

Union

Union operator

所有行，不含重复

Union All

Union all operator

所有行，包含重复

Intersect

Intersect operator

两条查询共同输出的行

Minus

Minus operator

第一条查询中不在第二条中的去重行

Oracle SQL 入门

Union

所有行，不含重复

我们的客户关联了哪些城市？


SELECT City FROM Customer
UNION
SELECT BillingCity FROM Invoice

| City           |
|----------------|
| Lyon           |
| Fort Worth     |
| Vienne         |
| Brussels       |
| Orlando        |
| Copenhagen     |
| Oslo           |
| Rio de Janeiro |
| Boston         |
| ...            |
Oracle SQL 入门

Union all

包含重复的所有行

我们的客户关联了哪些城市？各出现多少次？


SELECT City from Customer
UNION ALL
SELECT BillingCity from Invoice

| City          |
|---------------|
| Oslo          |
| Prague        |
| Prague        |
| Vienee        |
| Brussels      |
| Copenhagen    |
| Mountain View |
| Mountain View |
| Mountain View |
| ...           |
Oracle SQL 入门

Intersect

两条查询共同输出的行

哪些 Miles Davis 的曲目在播放列表中？

(SELECT TrackId from PlaylistTrack)
INTERSECT
(SELECT TrackId from Track
WHERE Composer = 'Miles Davis')

| TrackId |
|---------|
| 612     |
| 600     |
| 614     |
| 604     |
| 605     |
| 598     |
| 617     |
Oracle SQL 入门

Minus

第一条查询中不在第二条查询中的去重行

哪些艺人不作曲？

(SELECT Name FROM Artist)
MINUS
(SELECT Composer FROM Track)
ORDER BY 1 DESC

| Name                     |
|--------------------------|
| Zeca Pagodinho           |
| Youssou N'Dour           |
| Yo-Yo Ma                 |
| Yehudi Menuhin           |
| Xis                      |
| Wilhelm Kempff           |
| Whitesnake               |
| Vinícius E Qurteto Em Cy |
| Vinícius E Odette Lara   |
| ...                      |                                                                       |
Oracle SQL 入门

让我们练习！

Oracle SQL 入门

Preparing Video For Download...