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

在開始今天的摸魚大業(yè)之前，讓我們繼續(xù)延用之前用的表(為了演示方便，我們?cè)贓MP表中多插入了一條數(shù)據(jù)，這個(gè)數(shù)據(jù)的部門號(hào)是空值)，用于演示下面的例子(′`)

BONUS.png

DEPT.png

EMP.png

SALGRADE.png

FROM 中的子查詢

由于FROM中的子查詢涉及到多表的操作，所以準(zhǔn)備放在后面講，先在此處做個(gè)小提示，等學(xué)完多表操作后，會(huì)回來提這個(gè)，到時(shí)候我會(huì)在這邊貼一個(gè)鏈接

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

• 如果在進(jìn)行多表查詢操作時(shí)沒有連接條件 ，則會(huì)進(jìn)行全匹配，結(jié)果集相當(dāng)于兩個(gè)表進(jìn)行笛卡爾積

  • 舉個(gè)栗子

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

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

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

  • 舉個(gè)栗子：

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

  得到如下結(jié)果：
  
  
  2.png
  • 但是仔細(xì)觀察會(huì)發(fā)現(xiàn)，上面的數(shù)據(jù)只有13條，而員工表里面的數(shù)據(jù)有14條，其中Sunny所在部門號(hào)為空值，在部門表中匹配不到結(jié)果，故沒有出現(xiàn)在結(jié)果集當(dāng)中。（這是因?yàn)椴捎脙?nèi)連接的緣故，下面將會(huì)對(duì)內(nèi)外連接分別做分析）

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

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

• 等值內(nèi)連

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

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

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

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

    得到如下結(jié)果：
    
    
    2.png
    • 我們發(fā)現(xiàn)員工名為Sunny的員工數(shù)據(jù)和部門號(hào)為40的部門數(shù)據(jù)都沒有出現(xiàn)在結(jié)果集當(dāng)中。因?yàn)镾unny的部門號(hào)為空值，不等于部門表中任意一行數(shù)據(jù)的部門號(hào)，故找不到匹配；因?yàn)樵趩T工表中沒有員工的部門號(hào)為40，所以部門號(hào)為40的部門也沒有出現(xiàn)在結(jié)果集當(dāng)中。（只有滿足連接條件，并且成功找到匹配的數(shù)據(jù)才會(huì)出現(xiàn)雜結(jié)果集當(dāng)中）
  • 這種用where書寫的多表連接語句等價(jià)于SQL標(biāo)準(zhǔn)中的內(nèi)連接（inner join）
    • 上面的語句也可以寫成下面這種形式

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

  • 對(duì)于兩表中的同名字段，在使用的時(shí)候必須用表名或者表別名加以限定，不然SQL語句會(huì)有歧義，導(dǎo)致無法正確被解析
    • 舉個(gè)栗子

    -- 我們想在上面例子的基礎(chǔ)上，多顯示一個(gè)部門號(hào)
    -- 如果寫成下面這樣，就會(huì)報(bào)錯(cuò)，因?yàn)镾QL解析器不知道DEPTNO指的是EMP表的還是DEPT表的
    SELECT ENAME, DNAME, DEPNOT
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    -- 正確的寫法應(yīng)該是這樣的
    SELECT ENAME, DNAME, EMP.DEPTNO
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    --當(dāng)然也可以用DEPT來限定
    SELECT ENAME, DNAME, DEPT.DEPTNO
    FROM EMP, DEPT
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    
    -- 不過最好用表別名來限定，比較簡(jiǎn)潔一點(diǎn)
    SELECT ENAME, DNAME, e.DEPTNO
    FROM EMP e, DEPT d
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    

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

    -- 下面這個(gè)語句執(zhí)行的話是要報(bào)大錯(cuò)滴，因?yàn)橐呀?jīng)給EMP表起了別名e，就不能再用原表名EMP了
    SELECT ENAME, DNAME, EMP.DEPTNO
    FROM EMP e, DEPT d
    WHERE EMP.DEPTNO = DEPT.DEPTNO;
    

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

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

• 不等值連接

  • 舉個(gè)栗子

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

  得到如下結(jié)果：
  
  

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

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

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

SQL內(nèi)連接的標(biāo)準(zhǔn)寫法

• JOIN ... ON ...
• 舉個(gè)栗子

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

外連接

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

• 左外連接(LEFT OUTER JOIN)

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

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

  得到如下結(jié)果：
  
  

  4.png

• 右外連接(RIGHT OUTER JOIN)

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

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

  得到如下結(jié)果：
  
  

  5.png

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

• 全外連接

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

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

  得到如下結(jié)果：
  
  

  6.png

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

  • 其一，在進(jìn)行外連接的時(shí)候，必須用ON
  • 舉個(gè)栗子

  -- 下面的語句做了簡(jiǎn)單的外連接操作
  SELECT *
  FROM EMP 
    LEFT OUTER JOIN DEPT  ON EMP.DEPTNO = DEPT.DEPTNO;
  
  -- 下面的語句執(zhí)行是會(huì)報(bào)錯(cuò)的，因?yàn)闆]有加on
  SELECT *
  FROM EMP
    LEFT OUTER JOIN DEPT 
  WHERE EMP.DEPTNO = DEPT.DEPTNO;
  

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