【Oracle學(xué)習(xí)11】 子查詢和集合運算符
子查詢
11.1 定義子查詢
子查詢:
- 子查詢是嵌套在另一個SQL語句中的查詢.
- 子查詢可以嵌套在SELECT,INSERT,UPDATE,DELETE或其它查詢內(nèi)的查詢摇锋。
- 子查詢不能在GROUP By及Order By中。
- 子查詢可以返回標量酝枢，也可以返回一組記錄。
- 除關(guān)聯(lián)子查詢外硼被，子查詢都在嵌套查詢的外查詢之前就先執(zhí)行书蚪。
subquery
subquery
#部門及員工數(shù)量
select sysdate Today, (select count(*) from departments ) dept_cnt,
(select count(*) from employees) emp_cnt from dual;
#找出經(jīng)理員工
select last_name from employees where (employee_id in (select manager_id from employees));
#每個國家最高薪水
SQL>select max(salary),country_id from (select e.salary,department_id,l.country_id from employees e join departments d using (department_id)
join locations l using (location_id))
group by country_id;
MAX(SALARY) COUN
----------- ----
24000 US
13000 CA
10000 DE
14000 UK
##子查詢位置
11.2 子查詢可以解決的問題
11.2.1 將子查詢的結(jié)果集用于比較
子查詢可以返回標量猛蔽，也可以返回一組記錄黄刚。 標量子查詢只返回一個值。
#找出低于平均薪水的員工
select avg(salary) from employees; --$6461
select last_name,salary from employees where salary < (select avg(salary) from employees) order by salary desc;
#返回一組記錄,找出有員工的部門
select department_name from departments where department_id in (select distinct(department_id ) from employees);
select d.department_id,count(*) as cnt from departments d join employees on d.department_id = employees.department_id group by d.department_id;
11.2.2 星型轉(zhuǎn)換
oracle可允許START_TRANSFORMATION_ENABLED
#多表關(guān)聯(lián)的sql可改寫為星型SQL
select count(quantity_sold) from sales , products, cust_id where sales.xx = products.xx and ... ;
#星型SQL
select count(quantity_sold) from sales where prod_id in
(select prod_id from products where prod_name='Comic Book Heroes')
and cust_id in (select cust_id from customers where cust_city='Oxford');
11.2.3 生成對其執(zhí)行SELECT 語句的表
內(nèi)聯(lián)視圖：
FROM子句中的子查詢诫尽，叫稱為內(nèi)聯(lián)視圖(inline views)禀酱。
#查詢各國家的平均薪水
select avg(salary) ,country_id from
(select salary,country_id from employees natural join departments natural join locations) group by country_id;
AVG(SALARY) COUN
----------- ----
5640 US
6000 CA
8500 UK
11.2.4 生成投影值
select (select max(salary) from employees) * (select max(commission_pct) from employees) / 100 from dual;
11.2.5 生成傳遞給DML語句的行
insert into sales_hist select * from sales where date>sysdate-1;
update employees set salary = (select avg(salary) from employees) ;
delete from departments where department_id not in (select department_id from employees where department_id is not null);
11.3 列舉子查詢的類型
子查詢可以分成a)單行子查詢 b)多行子查詢 c)關(guān)聯(lián)子查詢
subquery
單行子查詢
i多行子查詢
子查詢與Having
11.3.1 單行和多行子查詢
- 在父查詢之前就需要執(zhí)行子查詢。
- 適用于單行子查詢的運算符是=,>,>=,<,<=和<>牧嫉。 多行子查詢的比較運算符是IN,NOT IN,ANY和ALL剂跟。
錯誤示例
多行子查詢
多行子查詢
SQL>SELECT salary FROM employees WHERE job_id = 'IT_PROG';
SALARY
----------
9000
6000
4800
4800
4200
#salary < all 沒有大于4200元工資
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < all
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG' order by salary;
#salary < ANY ??是指??
SQL>SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
多列名子查詢
Null Values in a Subquery
# 多列名子查詢
#找出各部門工資最少的人
SQL> SELECT first_name, min(salary), department_id FROM employees GROUP BY department_id order by department_id;
ERROR at line 1:
ORA-00979: not a GROUP BY expression
#正確寫法如下:
SELECT min(salary), department_id FROM employees
GROUP BY department_id order by department_id;
#
SQL> SELECT first_name, department_id, salary FROM employees
WHERE (salary, department_id) IN
(SELECT min(salary), department_id
FROM employees
GROUP BY department_id)
ORDER BY department_id;
FIRST_NAME DEPARTMENT_ID SALARY
---------------------------------------- ------------- ----------
Jennifer 10 4400
Pat 20 6000
Karen 30 2500
Susan 40 6500
TJ 50 2100
Diana 60 4200
Hermann 70 10000
Sundita 80 6100
Neena 90 17000
Lex 90 17000
Luis 100 6900
William 110 8300
#Null Values in a Subquery。找出不是manager的員工酣藻，如下是錯誤的曹洽。
SELECT distinct manager_id FROM employees; -- rownum=19 有一個null
SQL> SELECT emp.last_name FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id FROM employees mgr);
no rows selected
#可以改寫為
SQL>SELECT emp.last_name,manager_id FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT nvl(mgr.manager_id,0) FROM employees mgr) order by manager_id ;
11.3.2 關(guān)聯(lián)子查詢
關(guān)聯(lián)子查詢效率較差
#找出薪水少于部門平均薪水的所有員工.
select p.last_name ,p.department_id from employees p
where p.salary < (select avg(s.salary) from employees s where s.department_id = p.department_id );
LAST_NAME DEPARTMENT_ID
-------------------------------------------------- -------------
OConnell 50
Grant 50
Fay 20
Gietz 110
...
#找出薪水大于'Taylor'的人
SQL> select last_name from employees where salary > (select salary from employees where last_name = 'Taylor') order by last_name;
ORA-01427: single-row subquery returns more than one row
改正方法1：
select last_name from employees where salary > all (select salary from employees where last_name = 'Taylor') order by last_name;
方法2:
select last_name from employees where salary > (select max(salary) from employees where last_name = 'Taylor') order by last_name;
11.4 寫單行和多行子查詢
# 找平均薪水最高的工作
SQL>select job_title from jobs natural join employees group by job_title
having avg(salary) = (select max(avg(salary)) from employees group by job_id);
JOB_TITLE
----------------------------------------------------------------------
President
11.4.1 使用Exists 條件
#EXISTS 存在一個或多個就返回True。
#有員工的部門.從外表中一條條取記錄辽剧，在子表中比較.
select department_name from departments d where exists (select * from employees e where d.department_id= e.department_id);
#沒有員工的部門
select department_name from departments d where not exists (select * from employees e where d.department_id= e.department_id);
11.4.2 子查詢的空結(jié)果
若子查詢有可能為空送淆，則需要避免使用not in ，因為這相當(dāng)于<>all怕轿。
#結(jié)果為空偷崩，因為子查詢會返回null
SQL> select last_name,employee_id,manager_id from employees where employee_id not in (select manager_id from employees);
no rows selected
改為。找出沒有經(jīng)理的員工
select last_name,employee_id,manager_id from employees where employee_id not in (select manager_id from employees where manager_id is not null); --rownum=50
#有經(jīng)理的員工
select last_name,employee_id,manager_id from employees where employee_id in (select manager_id from employees where manager_id is not null);
11.5 描述集合運算符
三種集合運算: UNION,INTERSECT 撞羽，MINUS阐斜。
- UNION ： 返回查詢合并，排序并刪除重復(fù)行诀紊。 多個結(jié)果集UNION谒出，結(jié)果會自動轉(zhuǎn)換為最高的精度。
- UNION ALL: 返回查詢合并邻奠，不排序笤喳，不去重。
- INTERSECT: 只返回同時出現(xiàn)在兩個查詢結(jié)果集中的行碌宴，排序這些行并刪除重復(fù)行杀狡。 求交集。
- MINUS: 只返回第一個結(jié)果集中的行唧喉，并且這些行不能出現(xiàn)在第二個結(jié)果集中。 減集忍抽。
集合運算符
11.5.1 集合和維恩圖
集合運算要求：
- 查詢結(jié)果集的列數(shù)必須相同八孝。 數(shù)據(jù)類型大致相同(即相同或者可隱式轉(zhuǎn)換)。
- 集合運算可以有不同的名稱鸠项，再輸出結(jié)果用第一個查詢的名稱干跛。
- UNION，MINUS,INTERSECT都會刪除重復(fù)的行記錄祟绊。
- 會默認返回接所有列排序(從左到右)行楼入。 除非使用 NUNION ALL哥捕，則不會排序。
- 集合運算符的優(yōu)先級相同嘉熊，以指定它們的順序應(yīng)用遥赚。
- 集合中Order by 只能出現(xiàn)在結(jié)果集的最忍氣吞聲，不能出現(xiàn)在一個查詢中間阐肤。
集合運算原則
UNION
INTERSECT
MINUS
image.png
#UNION
SQL> select region_name from regions UNION select region_name from regions;
REGION_NAME
--------------------------------------------------
Americas
Asia
Europe
Middle East and Africa
# UNION ALL如下將不排序
select region_name from regions UNION ALL select region_name from regions;
#INTERSECT
SQL> select region_name from regions INTERSECT (select region_name from regions where region_name like 'A%' );
REGION_NAME
--------------------------------------------------
Americas
Asia
#MINUS
SQL> select region_name from regions MINUS (select region_name from regions where region_name like 'A%' );
REGION_NAME
--------------------------------------------------
Europe
Middle East and Africa
SQL> select region_name from regions MINUS (select region_name from regions );
no rows selected
#兩表列數(shù)對齊凫佛，可以使用TO_CHAR
SQL>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;
11.6 使用集合運算符將多個查詢合并為一個查詢
#多個結(jié)果集UNION，結(jié)果會自動轉(zhuǎn)換為最高的精度孕惜。
#不同的結(jié)果集可以增加null列來運用
select name,tail_length,null from cats union all select name,null ,wingspan from birds;
select sysdate , null from dual union select null ,'zhang3' from dual;
11.7 返回行的順序
返回行的順序:
- 不能在組成復(fù)合查詢的單個查詢中使用ORDER BY 子句
- 可以在復(fù)合查詢的結(jié)尾添加ORDER BY 子愧薛，并指定列號或別名
- UNION ALL 返回的行按它們在兩個源查詢中出現(xiàn)的順序排列
- 非UNION ALL，依從左到右列的順序排序返回的行衫画。
order by
