SQL中的join串連

inner join on,full outer join,left join on,right jion on
1.inner join on  內部串連 兩表都滿足的組合
2.full outer 全連 兩表相同的組合在一起，A表有，B表沒有的資料（顯示為null）,同樣B表有，A表沒有的顯示為(null)
3.A表 left join  B表 左連,以A表為基礎，A表的全部資料，B表有的組合。沒有的為null
4.A表 right join B表 右連,以B表為基礎，B表的全部資料，A表的有的組合。沒有的為null

 

一.內串連：inner join on 或 join
   只要兩個表的公用欄位有匹配值，就將這兩個表中的記錄組合起來。
   以一個共同的欄位求兩個表中符合要求的交集，並將每個表符合要求的記錄以共同的欄位為準合并起來。其實就是取他們的交集，，內聯結是用比較子比較要聯結列的值的聯結

文法
FROM table1 INNER JOIN table2 ON table1 . field1 compopr table2 . field2

INNER JOIN 操作包含以下部分：
部分 說明
 
table1, table2 要組合其中的記錄的表的名稱。
field1，field2 要聯結的欄位的名稱。如果它們不是數字，則這些欄位的資料類型必須相同，並且包含同質資料，但是，它們不必具有相同的名稱。
compopr
 任何關係比較子：“=”、“<”、“>”、“<=”、“>=”或者“<>”。

sql語句
select * from table1 join table2 on table1.id=table2.id
等價（與下列執行效果相同）
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * from table1 cross join table2 where table1.id=table2.id  (註：cross join後加條件只能用where,不能用on)

說明
1.可以在任何 FROM 子句中使用 INNER JOIN 操作。這是最常用的聯結類型。只要兩個表的公用欄位上存在相匹配的值，Inner 聯結就會組合這些表中的記錄。
2.可以將 INNER JOIN 用於 Departments 及 Employees 表，以選擇出每個部門的所有僱員。而要選擇所有部分（即使某些部門中並沒有被分配僱員）或者所有僱員（即使某些僱員沒有分配到

任何部門），則可以通過 LEFT JOIN 或者 RIGHT JOIN 操作來建立外部聯結。

3.如果試圖聯結包含備忘或 OLE 對象資料的欄位，將發生錯誤。

4.可以聯結任何兩個相似類型的數字欄位。例如，可以聯結自動編號和長整型欄位，因為它們均是相似類型。然而，不能聯結單精確度型和雙精確度型類型欄位。

下例展示了如何通過 CategoryID 欄位聯結 Categories 和 Products 表：

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID;

在前面的樣本中，CategoryID 是被聯結欄位，但是它不包含在查詢輸出中，因為它不包含在 SELECT 語句中。若要包含被聯結欄位，請在 SELECT 語句中包含該欄位名，在本例中是指

Categories.CategoryID。

也可以在 JOIN 語句中連結多個 ON 子句，請使用如下文法：

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];

也可以通過如下文法嵌套 JOIN 語句：

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

注意：LEFT JOIN 或 RIGHT JOIN 可以嵌套在 INNER JOIN 之中，但是 INNER JOIN 不能嵌套於 LEFT JOIN 或 RIGHT JOIN 之中。

二、外串連
1.概念：包括左向外聯結、右向外聯結或完整外部聯結

2.左串連：left join 或 left outer join
(1)左向外聯結的結果集包括 LEFT OUTER 子句中指定的左表的所有行，而不僅僅是聯結列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯的結果集行中右表的所有挑選清單列

均為空白值(null)。
(2)sql語句
select * from table1 left join table2 on table1.id=table2.id

注釋：包含table1的所有子句，根據指定條件返回table2相應的欄位，不符合的以null顯示

3.右串連：right join 或 right outer join
(1)右向外聯結是左向外聯結的反向聯結。將返回右表的所有行。如果右表的某行在左表中沒有匹配行，則將為左表返回空值。
(2)sql語句
select * from table1 right join table2 on table1.id=table2.id

注釋：包含table2的所有子句，根據指定條件返回table1相應的欄位，不符合的以null顯示

4.完整外部聯結:full join 或 full outer join
(1)完整外部聯結返回左表和右表中的所有行。當某行在另一個表中沒有匹配行時，則另一個表的挑選清單列包含空值。如果表之間有匹配行，則整個結果集行包含基表的資料值。
(2)sql語句
select * from table1 full join table2 on table1.id=table2.id

注釋：返回左右串連的和（見上左、右串連）

 

三、交叉串連(完全)

1.概念：沒有 WHERE 子句的交叉聯結將產生聯結所涉及的表的笛卡爾積。第一個表的行數乘以第二個表的行數等於笛卡爾積結果集的大小。（table1和table2交叉串連產生3*3=9條記錄）
2.交叉串連：cross join (不帶條件where...)
3.等價（與下列執行效果相同）
   A:select * from table1,table2

 

四.操作執行個體

表A記錄如下：
aID               aNum
1                  a20050111
2                  a20050112
3                  a20050113
4                  a20050114
5                  a20050115

表B記錄如下:
bID               bName
1                   2006032401
2                  2006032402
3                  2006032403
4                  2006032404
8                  2006032408

 

實驗如下:
1.left join

sql語句如下:
select * from A
left join B
on A.aID = B.bID

結果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404
5                   a20050115                NULL              NULL
（所影響的行數為 5 行）

結果說明:
left join是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left join是以左表為準的.
換句話說,左表(A)的記錄將會全部表示出來,而右表(B)只會顯示符合搜尋條件的記錄(例子中為: A.aID = B.bID).
B表記錄不足的地方均為NULL.

 

 

2.right join
sql語句如下:
select * from A
right join B
on A.aID = B.bID
結果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404
NULL           NULL                          8                     2006032408
（所影響的行數為 5 行）
結果說明:
        仔細觀察一下,就會發現,和left join的結果剛好相反,這次是以右表(B)為基礎的,A表不足的地方用NULL填充.

3.inner join
sql語句如下:
select * from A
innerjoin B
on A.aID = B.bID

結果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404

結果說明:
        很明顯,這裡只顯示出了 A.aID = B.bID的記錄.這說明inner join並不以誰為基礎,它只顯示合格記錄.