寫在前面：本篇博客大部分內(nèi)容參考數(shù)據(jù)庫系統(tǒng)概念（本科教學版）第四章(第三章的多表部分會挪到這一部分講)
筆者接下來的代碼示例會主要在SQL Server數(shù)據(jù)庫中測試

在開始今天的摸魚大業(yè)之前惯雳，讓我們繼續(xù)延用之前用的表(為了演示方便敲长，我們在EMP表中多插入了一條數(shù)據(jù)窟蓝，這個數(shù)據(jù)的部門號是空值)，用于演示下面的例子(′`)

BONUS.png

DEPT.png

EMP.png

SALGRADE.png

FROM 中的子查詢

由于FROM中的子查詢涉及到多表的操作饶深，所以準備放在后面講，先在此處做個小提示，等學完多表操作后丰榴，會回來提這個溅蛉，到時候我會在這邊貼一個鏈接

多表查詢中的笛卡爾積（全匹配問題）

• 如果在進行多表查詢操作時沒有連接條件 公浪，則會進行全匹配，結果集相當于兩個表進行笛卡爾積

  • 舉個栗子

  -- 下面的操作本來是想輸出所有的員工的姓名以及其所在的部門名
  -- 但是由于沒有指定連接條件船侧，所以結果將會是每一個員工與每一個部門組合的結果
  SELECT ENAME, DNAME
  FROM EMP, DEPT;
  

  結果如下(結果太多了欠气，總共有52條，下面的圖片中只截取了前面的一部分)：
  
  
  1.png

• 在實際開發(fā)過程中笛卡爾積得到的結果一般是沒有太大意義的镜撩，所以應當盡量避免==>添加連接條件(WHERE emp.deptno = dept.deptno)

  • 舉個栗子：

  -- 這個栗子就基本完成了上面的需求
  SELECT ENAME, DNAME
  FROM EMP, DEPT
  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  

  得到如下結果：
  
  
  2.png
  • 但是仔細觀察會發(fā)現(xiàn)预柒，上面的數(shù)據(jù)只有13條，而員工表里面的數(shù)據(jù)有14條，其中Sunny所在部門號為空值宜鸯，在部門表中匹配不到結果憔古，故沒有出現(xiàn)在結果集當中。（這是因為采用內(nèi)連接的緣故顾翼，下面將會對內(nèi)外連接分別做分析）

在WHERE子句中書寫連接條件的內(nèi)連接

這種在where子句中寫連接條件的實現(xiàn)方式投放，不是SQL標準中的標準用法，但是大多數(shù)數(shù)據(jù)庫都支持這種用法适贸，所以這種用法已經(jīng)成了一種事實標準灸芳。在內(nèi)連接的范疇中，這種用法與SQL標準的內(nèi)連接用法是等價的

• 等值內(nèi)連

  • 上述那個例子就是最常見的等值內(nèi)連

  SELECT ENAME, DNAME
  FROM EMP, DEPT
  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  

  • 等值內(nèi)連就是在where中書寫多表的連接條件的時候比較兩個表中某一個或多個字段的值拜姿，值相等的則匹配（常見的就是用一個表的外鍵與另一個表中對應的外鍵的參照鍵進行比較）
  • 必須是兩張表中能夠滿足連接條件的數(shù)據(jù)才會出現(xiàn)在結果集當中（不單是等值連接烙样，下面講的非等值連接也是，所有內(nèi)連接都應該瞞住這個）
    • 再來舉個上面舉過的栗子

    -- 下面的操作得到了所有員工的名字以及其所在部門的名字
    SELECT ENAME, DNAME
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    

    得到如下結果：
    
    
    2.png
    • 我們發(fā)現(xiàn)員工名為Sunny的員工數(shù)據(jù)和部門號為40的部門數(shù)據(jù)都沒有出現(xiàn)在結果集當中蕊肥。因為Sunny的部門號為空值谒获，不等于部門表中任意一行數(shù)據(jù)的部門號，故找不到匹配壁却；因為在員工表中沒有員工的部門號為40批狱，所以部門號為40的部門也沒有出現(xiàn)在結果集當中。（只有滿足連接條件展东，并且成功找到匹配的數(shù)據(jù)才會出現(xiàn)雜結果集當中）
  • 這種用where書寫的多表連接語句等價于SQL標準中的內(nèi)連接（inner join）
    • 上面的語句也可以寫成下面這種形式

    -- 下面的語句和上面例子中的語句是等價的
    SELECT ENAME, DNAME
    FROM EMP INNER JOIN DEPT
        ON EMP.DEPTNO = DEPT.DEPTNO
    
    -- SQL標準中還有下面這種用法（課本上有提到赔硫，但是顯然SQL Server不支持這種用法）
    -- 表示的是利用DEPNOT這個字段來進行兩表的連接（前提是這兩個表中要有同名字段）
    -- 如果支持這種用法的數(shù)據(jù)庫執(zhí)行下面操作的話得到的結果和上面的是一樣的（經(jīng)驗證，MySQL數(shù)據(jù)庫是支持這種用法的）
    SELECT ENAME, DNAME
    FROM EMP INNER JOIN DEPT
        USING(DEPNOT)
    

  • 對于兩表中的同名字段盐肃，在使用的時候必須用表名或者表別名加以限定爪膊，不然SQL語句會有歧義，導致無法正確被解析
    • 舉個栗子

    -- 我們想在上面例子的基礎上砸王，多顯示一個部門號
    -- 如果寫成下面這樣推盛，就會報錯，因為SQL解析器不知道DEPTNO指的是EMP表的還是DEPT表的
    SELECT ENAME, DNAME, DEPNOT
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    -- 正確的寫法應該是這樣的
    SELECT ENAME, DNAME, EMP.DEPTNO
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    --當然也可以用DEPT來限定
    SELECT ENAME, DNAME, DEPT.DEPTNO
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    -- 不過最好用表別名來限定谦铃，比較簡潔一點
    SELECT ENAME, DNAME, e.DEPTNO
    FROM EMP e, DEPT d
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    

  • 如果對一個表起了別名之后就不能再使用原表名了耘成，而要換成其別名
    • 舉個栗子

    -- 下面這個語句執(zhí)行的話是要報大錯滴，因為已經(jīng)給EMP表起了別名e荷辕，就不能再用原表名EMP了
    SELECT ENAME, DNAME, EMP.DEPTNO
    FROM EMP e, DEPT d
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    

  • 如果再考慮效率問題凿跳，在進行多表操作時，最好所有字段都用表名或者表別名限定疮方，這樣可以免去SQL解析器幫你分析某個字段屬于哪個表的開銷控嗜，可以在一定程度上提高執(zhí)行效率
    • 舉個栗子

    SELECT e.ENAME, d.DNAME, e.DEPTNO
    FROM EMP e, DEPT d
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    

• 不等值連接

  • 舉個栗子

  -- 下面的語句就查處了所有員工的姓名、工資骡显、以及其工資等級
  SELECT e.ENAME, e.SAL, g.GRADE
  FROM EMP e, SALGRADE g
  WHERE e.SAL >= g.LOSAL AND  e.SAL <= g.HISAL;
  

  得到如下結果：
  
  

  3.png
  • 不等值連接就是內(nèi)連接中除了通過比較值相同來進行連接以外的其他內(nèi)連接操作

• n個表相連疆栏，至少需要n-1個連接條件曾掂，要不然就會在連接過程中出現(xiàn)笛卡爾積

• 多表的連接條件一般都是建立在外鍵和外鍵的參照鍵之間，采用等值連接

SQL內(nèi)連接的標準寫法

• JOIN ... ON ...
• 舉個栗子

  -- 下面的語句就是SQL標準中多表內(nèi)連接的寫法
  SELECT *
  FROM TABLE1 t1
    JOIN TABLE2 t2 ON ...
    JOIN TABLE3 t3 ON ...
    JOIN TABLE4 t4 ON ...
  

外連接

內(nèi)連接的結果是外連接結果的一個子集壁顶，外連接的結果中還可以包括只在一張表中出現(xiàn)珠洗，并且在另一張表種找不到匹配的結果

• 左外連接(LEFT OUTER JOIN)

  • 包含JOIN關鍵字左表中的所有數(shù)據(jù)（即便某個數(shù)據(jù)在右表中找不到匹配）
  • 舉個栗子

  -- 下面的語句與上面的例子類似
  -- 同樣是得到所有員工的名字以及其所在部門名
  -- 不同的是采用左外連接以后Suuny的數(shù)據(jù)會出現(xiàn)在結果集中了
  SELECT ENAME, DNAME
  FROM EMP LEFT OUTER JOIN DEPT 
      ON EMP.DEPTNO = DEPT.DEPTNO;
  

  得到如下結果：
  
  

  4.png

• 右外連接(RIGHT OUTER JOIN)

  • 包含JOIN關鍵字右表中的所有數(shù)據(jù)(即便某個數(shù)據(jù)在左表中找不到匹配
  • 舉個栗子

  -- 還是這個栗子，但不同的是我們把LEFT改成了RIGHT
  -- 會發(fā)現(xiàn)若专，部門號為40的部門信息顯示出來了
  SELECT ENAME, DNAME
  FROM EMP RIGHT OUTER JOIN DEPT
      ON EMP.DEPTNO = DEPT.DEPTNO;
  

  得到如下結果：
  
  

  5.png

• 對比可以發(fā)現(xiàn)左外連接和右外連接的效用其實是一樣的许蓖，只要吧JOIN兩邊表的位置對調(diào)一下，兩者就可相互轉(zhuǎn)換调衰。（使用時隨意膊爪，習慣怎么用就怎么用就好）

• 全外連接

  • JOIN關鍵字兩邊的表的所有數(shù)據(jù)都會出現(xiàn)在結果集當中，得到的結果其實就是左外連接和右外連接結果集的并集
  • 舉個栗子：

  SELECT ENAME, DNAME
  FROM EMP FULL OUTER JOIN DEPT
      ON EMP.DEPTNO = DEPT.DEPTNO;
  

  得到如下結果：
  
  

  6.png

• 在進行多表連接的時候采用WHERE和ON的區(qū)別

  • 其一嚎莉，在進行外連接的時候米酬，必須用ON
  • 舉個栗子

  -- 下面的語句做了簡單的外連接操作
  SELECT *
  FROM EMP 
    LEFT OUTER JOIN DEPT  ON EMP.DEPTNO = DEPT.DEPTNO;
  
  -- 下面的語句執(zhí)行是會報錯的，因為沒有加on
  SELECT *
  FROM EMP
    LEFT OUTER JOIN DEPT 
  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  

  • 其二趋箩，如果在on子句中指定連接條件赃额，并在where子句中出現(xiàn)其余條件，這樣的SQL插敘通常更容易讓人讀懂
  • 所以在執(zhí)行內(nèi)連接的時候叫确，on和where的使用是沒有多大區(qū)別的跳芳，但是在執(zhí)行外連接的時候就必須用on了。所以建議就是在on子句中指定連接條件竹勉，并在where子句中出現(xiàn)其余條件