SQL 聯結(內串連/外串連/交叉串連)

SQL  聯結

有表 titles(書資訊表)  如

有表 publishers(出版社)  如

執行左外聯結代碼：

select titles.titles_id,titles.title,publishers.pub_name from titles left outer join publishers on titles.pub_id=publishers.pub_id     結果如：

大家看明白了嗎？ 現在要講關左外聯結的 理論了 要頂住呀！不帶睡覺啊!

 

一、聯結（ join）

通過聯結，可以根據各個表之間的邏輯關係從兩個或多個表中檢索資料。聯結表示應如何使用一個表中的資料來選擇另一個表中的行。

聯結條件通過以下方法定義兩個表在查詢中的關聯方式：

指定每個表中要用於聯結的列。典型的聯結條件在一個表中指定外鍵，在另一個表中指定與其關聯的鍵。指定比較各列的值時要使用的邏輯運算子（=、<> 等）。

 

二、聯結類型

當聯結表時，建立的聯結類型影響出現在結果集內的行。聯結類型：

三、內聯結

內串連也叫自然串連，它是組合兩個表的常用方法。自然串連將兩個表中的列進行比較，將兩個表中滿足串連條件的行組合起來，作為結果。自然串連有兩種形式的文法。

文法一：

       SELECT  列   FROM  表1 [insert] JION 表2  ON 表1.列=表2.列

文法二：

        SELECT  列  FROM  表1,表2  WHERE表1.列=表2.列

【例】從titles、authors和titleauthor表中查詢書的書號、書名、作者號和作者名。

 select titles.title_id, title, authors.au_id, au_lname from titles join titleauthor on titles.title_id=titleauthor.title_id  join authors  on authors.au_id=titleauthor.au_id

 

四、外串連（Outer join）

在自然串連中，只有在兩個表中匹配的行才能在結果集中出現。而在外串連中可以只限制一個表，而對另外一個表不加限制（即所有的行都出現在結果集中）。

外串連分為左外串連、右外串連和全外串連。左外串連是對串連條件中左邊的表不加限制；右外串連是對右邊的表不加限制；全外串連對兩個表都不加限制，所有兩個表中的行都會包括在結果集中。

 

五、外串連（Outer join）文法

左外串連的文法為： SELECT 列 FROM 表1   LEFT   [OUTER]JOIN 表2 ON 表1.列1=表2.列2

右外串連的文法為： SELECT select_list FROM  表1 RIGHT[OUTER]JOIN 表2 ON 表1.列1=表2.列2

全外串連(完整外部聯結)的文法為： SELECT select_list FROM  表1 FULL[OUTER]  JOIN  表2 ON 表1.列1=表2.列2

 

六、左向外聯結

包括第一個命名表（“左”表，出現在 JOIN 子句的最左邊）中的所有行。不包括右表中的不匹配行。

【例35】下面的 SQL 陳述式說明 titles 表和 publishers 表之間的左向外聯結包括所有的書名，甚至包括那些沒有出版商資訊的書名： Use pubs    SELECT titles.title_id,    titles.title, publishers.pub_name    FROM titles LEFT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id

 

 

 

七、右向外聯結

包括第二個命名表（“右”表，出現在 JOIN 子句的最右邊）中的所有行。不包括左表中的不匹配行。

【例36】在 titles 和 publishers 表之間的右向外聯結將包括所有的出版商，甚至包括那些在 titles 表中沒有書名的出版商。

   SELECT titles.title_id, titles.title, publishers.pub_name     FROM titles RIGHT OUTER JOIN publishers ON titles.pub_id = publishers.pub_id

 

 

 

 

八、完整外部聯結

包括所有聯結表中的所有行，不論它們是否匹配。

【例37】 titles 表和 publishers 表之間的完整外部聯結顯示所有書名和所有出版商，甚至包括那些在另一個表中沒有匹配值的書名和出版商。

    SELECT titles.title_id, titles.title, publishers.pub_name  FROM titles FULL OUTER JOIN publishers    ON titles.pub_id = publishers.pub_id

 

 

 

九、交叉聯結

沒有 WHERE 子句的交叉聯結將產生聯結所涉及的表的笛卡爾積。第一個表的行數乘以第二個表的行數等於笛卡爾積結果集的大小。

【例39】 閱讀以下程式：

   USE pubs    

 SELECT au_fname, au_lname, pub_name  FROM authors CROSS JOIN publishers  ORDER BY au_lname DESC       

說明：結果集包含 184 行（authors 有 23 行，publishers 有 8 行；23 乘以 8 等於 184）。           不過，如果添加一個 WHERE 子句，則交叉聯結的作用將同內聯結一樣。

 

 

使用例子：（Oracle）

SELECT
    A.KAISEN_NO ,       --回線番號 
    decode(B.KAISEN_KBN ,1, '固定', 2, '移動', ''),   --回線區分
    decode(B.CHAKU_SEN_KBN , 0, '通常', 1, '著専', ''),   --著専區分
    A.KEIHI_BUSHO_CD,       --負擔部署Code
    D.BUSHO_MEI,       --負擔部署名稱
    A.RIYO_BUSHO_CD,       --利用部署Code
    E.BUSHO_MEI,       --利用部署名稱
    C.RIYOSHA_CD,                                     --利用者cd
    C.RIYOSHA_MEI,       --利用者名
    B.MIKAIYAKU_RIYU                                         --未解約理由

FROM
   T_UCHIWAKE_MEISAI A                               --內訳明細データ
   LEFT OUTER JOIN M_TEL_NUMBER B ON A.KAISEN_NO = B.KAISEN_NO                          --回線マスタ
   LEFT OUTER JOIN M_RIYOSHA C ON A.RIYOSHA_NAIBU_NO = C.RIYOSHA_NAIBU_CD               --利用者マスタ
   LEFT OUTER JOIN M_BUSHO D ON A.KEIHI_BUSHO_CD = D.BUSHO_CD                           --部署マスタ
   LEFT OUTER JOIN M_BUSHO E ON A.RIYO_BUSHO_CD  = E.BUSHO_CD                           --部署マスタ

WHERE
  A.SEIKYU_KINGAKU != 0 AND
  A.WARIBIKI_MAE_RYOKIN = 0 AND
  A.HOSEI_GO_SEIKYU_KINGAKU = 0 AND
  SUBSTR(TO_CHAR(A.RIYO_NEN_TSUKI), 1, 4) = '請求年'   AND
  SUBSTR(TO_CHAR(A.RIYO_NEN_TSUKI), 5, 2) = '請求月'   AND
  B.KAISEN_KBN ='回線種類'   AND
  B.CHAKU_SEN_KBN = '回線區分'
  
ORDER BY
   A.KAISEN_NO