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)