SQL基礎(chǔ)之使用集合運算符進行多表查詢（十二）

集合操作：類型和注意事項

集合操作注意事項

• 在 SELECT 列表中的列名和表達式在數(shù)量上必須匹配

• 第二個查詢中的每一列的數(shù)據(jù)類型必須與第一個查詢其對應(yīng)的列的數(shù)據(jù)類型相匹配

• 可以用括號來改變的執(zhí)行順序。

• ORDER BY  子句:

–  只能在語句的最后出現(xiàn)

–  可以使用第一個查詢中的列名，別名或相對位置

Oracle 服務(wù)器和集合操作符

• 除 UNION ALL之外，系統(tǒng)會自動刪除重復的記錄

• 列名為第一個查詢返回的結(jié)果

• 除 UNION ALL之外，系統(tǒng)自動按照第一個查詢中的第一個列的升序排列

使用 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，當前的職稱相同(也就是說,他們換工作但是現(xiàn)在已經(jīng)回到以前同樣的工作)。

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

• 當字段在一個或另一個表中不存在，必須匹配上數(shù)據(jù)類型（使用TO_CHAR函數(shù)或其他轉(zhuǎn)換函數(shù)）

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 子句只能在結(jié)束時出現(xiàn)一次

• 集合操作中每個查詢不能有單獨的 ORDER BY 子句

• ORDER BY 子句中 只能識別第一個 SELECT 查詢的列。

• 默認情況下，第一列的第一個 SELECT 查詢使用升序?qū)敵鲞M行排序。

請查詢出所有的部門下沒有 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、請查出所有工作發(fā)生過變動，但是多次變動后現(xiàn)在的工作是做的以前做過的工作的員工的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