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