1. 除非有必要，否則不要用UNION代替UNION ALL
2. 查找兩個表中相同的行
2.1. 當執行連接查詢時，為了得到正確的結果，必須慎重考慮要把哪些列作為連接項
2.2. 當參與連接的行集里的某些列可能有共同值，而其他列有不同值的時候，這一點尤為重要
2.3. 集合運算INTERSECT會回傳兩個行集的相同部分
2.3.1. 必須保證兩個表里參與比較的專案數目是相同的，并且資料型別也是相同的
2.3.2. 默認不會回傳重復項
2.4. 示例
2.4.1. sql
create view V
as
select ename,job,sal
from emp
where job = 'CLERK'
select * from V
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ADAMS CLERK 1100
JAMES CLERK 950
MILLER CLERK 1300
2.4.2. sql
select e.empno,e.ename,e.job,e.sal,e.deptno
from emp e join V
on ( e.ename = v.ename
and e.job = v.job
and e.sal = v.sal )
2.4.3. sql
select empno,ename,job,sal,deptno
from emp
where (ename,job,sal) in (
select ename,job,sal from emp
intersect
select ename,job,sal from V
)
3. 查找只存在于一個表中的資料
3.1. MySQL
3.1.1. sql
select deptno
from dept
where deptno not in (select deptno from emp)
3.1.2. sql
select distinct deptno
from dept
where deptno not in (select deptno from emp)
3.1.2.1. 排除重復項
3.1.3. 在使用NOT IN時，要注意Null值
3.2. PostgreSQL
3.2.1. sql
select deptno from dept
except
select deptno from emp
3.3. Oracle
3.3.1. sql
select deptno from dept
minus
select deptno from emp
3.4. 要點
3.4.1. 參與運算的兩個SELECT串列要有相同的資料型別和值個數
3.4.2. 不回傳重復項
3.4.3. Null值不會產生問題
3.5. sql
select deptno
from dept
where deptno not in ( 10,50,null )
( no rows )
select deptno
from dept
where not (deptno=10 or deptno=50 or deptno=null)
( no rows )
3.5.1. 三值邏輯
3.6. 免受Null值影響的替代方案
3.6.1. sql
select d.deptno
from dept d
where not exists ( select null
from emp e
where d.deptno = e.deptno )
4. 從一個表檢索與另一個表不相關的行
4.1. 使用外連接并過濾掉Null值
4.2. sql
select d.*
from dept d left outer join emp e
on (d.deptno = e.deptno)
where e.deptno is null
4.2.1. 反連接（anti-join）
5. 新增連接查詢而不影響其他連接查詢
5.1. 外連接既能夠獲得額外資訊，又不會丟失原有的資訊
5.1.1. sql
select e.ename, d.loc, eb.received
from emp e join dept d
on (e.deptno=d.deptno)
left join emp_bonus eb
on (e.empno=eb.empno)
order by 2
5.2. 使用標量子查詢
5.2.1. 把子查詢放置在SELECT串列里
5.2.2. 在不破壞當前結果集的情況下，標量子查詢是為現有查詢陳述句添加額外資料的好辦法
5.2.3. sql
select e.ename, d.loc,
(select eb.received from emp_bonus eb
where eb.empno=e.empno) as received
from emp e, dept d
where e.deptno=d.deptno
order by 2
6. 識別并消除笛卡兒積
6.1. n-1法則
6.1.1. n代表FROM子句里表的個數
6.1.2. n-1則代表消除笛卡兒積所必需的連接查詢的最少次數
6.2. 笛卡兒積常用于變換或展開（以及合并）結果集，生成一系列的值，以及模擬loop回圈
7. 組合使用連接查詢與聚合函式
7.1. 如果連接查詢產生了重復行，兩種辦法來使用聚合函式可以避免得出錯誤的計算結果
7.1.1. 呼叫聚合函式時直接使用關鍵字DISTINCT，這樣每個值都會先去掉重復項再參與計算
7.1.2. 在進行連接查詢之前先執行聚合運算（以內嵌視圖的方式），這樣可以避免錯誤的結果，因為聚合運算發生在連接查詢之前
8. 從多個表中回傳缺失值
8.1. 使用全外連接（full outer join），基于一個共同值從兩個表中回傳缺失值
8.1.1. sql
select d.deptno,d.dname,e.ename
from dept d full outer join emp e
on (d.deptno=e.deptno)B
8.2. 合并兩個外連接的查詢結果
8.2.1. sql
select d.deptno,d.dname,e.ename
from dept d right outer join emp e
on (d.deptno=e.deptno)
union
select d.deptno,d.dname,e.ename
from dept d left outer join emp e
on (d.deptno=e.deptno)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/556814.html
標籤：MySQL
上一篇：社區星力量 | 平等、包容、耐性，這對貢獻小白來說很重要
下一篇：返回列表