SQL基础之使用集合运算符进行多表查询（十二）-白红宇

集合操作：类型和注意事项

集合操作注意事项

在 SELECT 列表中的列名和表达式在数量上必须匹配

第二个查询中的每一列的数据类型必须与第一个查询其对应的列的数据类型相匹配

可以用括号来改变的执行顺序。

ORDER BY 子句:

– 只能在语句的最后出现

– 可以使用第一个查询中的列名，别名或相对位置

Oracle 服务器和集合操作符

除 UNION ALL之外，系统会自动删除重复的记录

列名为第一个查询返回的结果

除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

使用 UNION 操作符

1、显示当前和以前的工作的所有员工的详细信息。每个雇员只显示一次。

select employee_id, job_id

from employees

union

select employee_id, job_id

from job_history;

使用 UNION ALL 操作符

1、显示当前和以前的员工的所有部门。

select employee_id, job_id, department_id

from employees

union all

select employee_id, job_id, department_id

from job_history

order by employee_id;

使用 INTERSECT 操作符

显示员工ID和工作ID，当前的职称相同(也就是说,他们换工作但是现在已经回到以前同样的工作)。

select employee_id, job_id

from employees

intersect

select employee_id, job_id

from job_history;

使用 MINUS 操作符

1、显示员工表中一次都没有改变过工作的的员工ID

select employee_id

from employees

minus

select employee_id

from job_history;

相匹配的 SELECT 语句

使用 UNION 操作符显示location_id,department_name,state_province

当字段在一个或另一个表中不存在，必须匹配上数据类型（使用TO_CHAR函数或其他转换函数）

select location_id, department_name "Department",

to_char(null) "warehouse location"

from departments

union

select location_id, to_char(null) "Department",

state_province

from locations;

使用UNION操作符，显示雇员的ID，工作ID，和所有员工的工资

select employee_id, job_id,salary

from employees

union

select employee_id, job_id,0

from job_history;

集合操作中使用 ORDER BY 子句的注意事项

复合查询中 ORDER BY 子句只能在结束时出现一次

集合操作中每个查询不能有单独的 ORDER BY 子句

ORDER BY 子句中只能识别第一个 SELECT 查询的列。

默认情况下，第一列的第一个 SELECT 查询使用升序对输出进行排序。

请查询出所有的部门下没有 ST_CLERK 工种的 department_id，要求使用集合操作符

select department_id

from departments

minus

select department_id

from employees

where job_id not like 'ST_CLERK';

2、请使用集合操作符写一条 SQL，查出所有的没有部门坐落的国家的 country_id,country_name

select country_id,country_name

from countries

minus

select l.country_id,c.country_name

from locations l join countries c

on (l.country_id=c.country_id)

join departments d

on d.location_id=l.location_id;

3、请使用集合操作符写一条 SQL，查出部门号在 10，50，20 中的所有的 job_id,department_id，

并以 10,50,20 的排列顺序显示。

select distinct job_id,department_id

from employees

where department_id = 10

union all

select distinct job_id,department_id

from employees

where department_id= 50

union all

select distinct job_id,department_id

from employees

where department_id= 20;

4、请查出所有工作发生过变动，但是多次变动后现在的工作是做的以前做过的工作的员工的employee_id 和 job_id

select employee_id,job_id

from employees

intersect

select employee_id,job_id

from job_history;

5、HR 部门的同事希望你能够帮助他们生成一个报告，要求满足以下 2 点：

a)从 EMPLOYEES 表中获得所有员工的 last_name 和 department_id（不管是否属于同一个部门）

b)从 DEPARTMENTS 表中获得所有的 department_id 和 department_name（不管是否该部门有员工）

select last_name,department_id,to_char(null) dept_name

from employees

union

select to_char(null),department_id,department_name

from departments;

LAST_NAME DEPARTMENT_ID DEPT_NAME

------------------------- ------------- ------------------------------

Abel 80

Ande 80

Atkinson 50

Austin 60

Baer 70

Baida 30

Banda 80

Bates 80

Bell 50

Bernstein 80

Bissot 50

Bloom 80

Bull 50

Cabrio 50

Cambrault 80

Chen 100

Chung 50

Colmenares 30

Davies 50

De Haan 90

Dellinger 50

Dilly 50

Doran 80

Ernst 60

Errazuriz 80

Everett 50

Faviet 100

Fay 20

Feeney 50

Fleaur 50

Fox 80

Fripp 50

Gates 50

Gee 50

Geoni 50

Gietz 110

Grant 50

Grant

Greenberg 100

Greene 80

Hall 80

Hartstein 20

Higgins 110

Himuro 30

Hunold 60

Hutton 80

Johnson 80

Jones 50

Kaufling 50

Khoo 30

King 80

King 90

Kochhar 90

Kumar 80

Ladwig 50

Landry 50

Lee 80

Livingston 80

Lorentz 60

Mallin 50

Markle 50

Marlow 50

Marvins 80

Matos 50

Mavris 40

McCain 50

McEwen 80

Mikkilineni 50

Mourgos 50

Nayer 50

OConnell 50

Olsen 80

Olson 50

Ozer 80

Partners 80

Pataballa 60

Patel 50

Perkins 50

Philtanker 50

Popp 100

Rajs 50

Raphaely 30

Rogers 50

Russell 80

Sarchand 50

Sciarra 100

Seo 50

Sewall 80

Smith 80

Stiles 50

Sullivan 50

Sully 80

Taylor 50

Taylor 80

Tobias 30

Tucker 80

Tuvault 80

LAST_NAME DEPARTMENT_ID DEPT_NAME

------------------------- ------------- ------------------------------

Urman 100

Vargas 50

Vishney 80

Vollman 50

Walsh 50

Weiss 50

Whalen 10

Zlotkey 80

10 Administration

20 Marketing

30 Purchasing

40 Human Resources

50 Shipping

60 IT

70 Public Relations

80 Sales

90 Executive

100 Finance

110 Accounting

120 Treasury

130 Corporate Tax

140 Control And Credit

150 Shareholder Services

160 Benefits

170 Manufacturing

180 Construction

190 Contracting

200 Operations

210 IT Support

220 NOC

230 IT Helpdesk

240 Government Sales

250 Retail Sales

260 Recruiting

270 Payroll

本文转自 yuri_cto 51CTO博客，原文链接：http://blog.51cto.com/laobaiv1/1906572，如需转载请自行联系原作者