SQL各種串連學習

  使用關係代數合并資料
1 關係代數
合并資料集合的理論基礎是關係代數，它是由E.F.Codd於1970年提出的。
在關係代數的形式化語言中：
        用表、或者資料集合表示關係或者實體。
        用行表示元組。
        用列表示屬性。
關係代數包含以下8個關係運算子
        選取――返回滿足指定條件的行。
        投影――從資料集合中返回指定的列。
        笛卡爾積――是關係的乘法，它將分別來自兩個資料集合中的行以所有可能的方式進行組合。
        並――關係的加法和減法，它可以在行的方向上合并兩個表中的資料，就像把一個表壘在另一個表之上一樣。
        交――返回兩個資料集合所共有的行。
        差――返回只屬於一個資料集合的行。
        串連――在水平方向上合并兩個表，其方法是：將兩個表中在共同資料項目上相互匹配的那些行合并起來。
        除――返回兩個資料集之間的精確匹配。
此外，作為一種實現現代關係代數運算的方法，SQL還提供了：
        子查詢――類似於串連，但更靈活；在外部查詢中，方式可以使用運算式、列表或者資料集合的地方都可以使用子查詢的結果。
本章將主要講述多種類型的串連、簡單的和相關的子查詢、幾種類型的並、關係除以及其他的內容。
2 使用串連
2.1 連線類型
在關係代數中，串連運算是由一個笛卡爾積運算和一個選取運算構成的。首先用笛卡爾積完成對兩個資料集合的乘運算，然後對產生的結果集合進行選取運算，確保只把分別來自兩個資料集合并且具有重疊部分的行合并在一起。串連的全部意義在於在水平方向上合并兩個資料集合（通常是表），併產生一個新的結果集合，其方法是將一個資料來源中的行於另一個資料來源中和它匹配的行組合成一個新元組。
SQL提供了多種類型的串連方式，它們之間的區別在於：從相互交疊的不同資料集合中選擇用於串連的行時所採用的方法不同。
連線類型        定義
內串連        只串連匹配的行
左外串連        包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行
右外串連        包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行
全外串連        包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。
(H)(theta)串連        使用等值以外的條件來匹配左、右兩個表中的行
交叉串連        產生笛卡爾積－它不使用任何匹配或者選取條件，而是直接將一個資料來源中的每個行與另一個資料來源的每個行都一一匹配
在INFORMIX中串連表的查詢
如果FROM子句指定了多於一個表引用，則查詢會串連來自多個表的行。串連條件指定各列之間（每個表至少一列）進行串連的關係。因為正在比較串連條件中的列，所以它們必須具有一致的資料類型。
SELECT語句的FROM子句可以指定以下幾種類型的串連
FROM子句關鍵字        相應的結果集
CROSS JOIN        笛卡爾乘積（所有可能的行對）
INNER JOIN        僅對滿足串連條件的CROSS中的列
LEFT OUTER JOIN        一個表滿足條件的行，和另一個表的所有行
RIGHT OUTER JOIN        與LEFT相同，但兩個表的角色互換
FULL OUTER JOIN        LEFT OUTER 和 RIGHT OUTER中所有行的超集

2.2 內串連（Inner Join）
內串連是最常見的一種串連，它頁被稱為普通串連，而E.FCodd最早稱之為自然串連。
下面是ANSI SQL－92標準
select *
from  t_institution i
inner join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = "5801"
其中inner可以省略。
等價於早期的串連文法
select *
from t_institution i, t_teller t
where i.inst_no = t.inst_no
and i.inst_no = "5801"

2.3 外串連
2.3.1        左外串連(Left Outer Jion)
select *
from  t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
其中outer可以省略。
2.3.2        右外串連(Rigt Outer Jion)
select *
from  t_institution i
right outer join t_teller t
on i.inst_no = t.inst_no
2.3.3        全外串連(Full Outer)
全外串連返回參與串連的兩個資料集合中的全部資料，無論它們是否具有與之相匹配的行。在功能上，它等價於對這兩個資料集合分別進行左外串連和右外串連，然後再使用消去重複行的並操作將上述兩個結果集合并為一個結果集。
在現實生活中，參照完整性條件約束可以減少對於全外串連的使用，一般情況下左外串連就足夠了。在資料庫中沒有利用清晰、規範的約束來防範錯誤資料情況下，全外串連就變得非常有用了，你可以使用它來清理資料庫中的資料。
select *
from  t_institution i
full outer join t_teller t
on i.inst_no = t.inst_no
2.3.4        外串連與條件配合使用
當在內串連查詢中加入條件是，無論是將它加入到join子句，還是加入到where子句，其效果是完全一樣的，但對於外串連情況就不同了。當把條件加入到join子句時，SQL Server、Informix會返回外串連表的全部行，然後使用指定的條件返回第二個表的行。如果將條件放到where子句中，SQL Server將會首先進行串連操作，然後使用where子句對串連後的行進行篩選。下面的兩個查詢展示了條件放置位子對執行結果的影響：
條件在join子句
select *
from  t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
and i.inst_no = “5801”
結果是：
inst_no    inst_name            inst_no    teller_no  teller_name
5801       天河區               5801       0001       tom
5801       天河區               5801       0002       david
5802       越秀區
5803       白雲區
條件在where子句
select *
from  t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = “5801”
結果是：
inst_no    inst_name            inst_no    teller_no  teller_name
5801       天河區               5801       0001       tom
5801       天河區               5801       0002       david

2.4 自身串連
自身串連是指同一個表自己與自己進行串連。這種一元串連通常用於從自反關係（也稱作遞迴關係）中抽取資料。例如人力資源資料庫中僱員與老闆的關係。
下面例子是在機構表中尋找本機構和上級機構的資訊。
select s.inst_no superior_inst, s.inst_name sup_inst_name, i.inst_no, i.inst_name
from t_institution i
join t_institution s
on i.superior_inst = s.inst_no

結果是：
superior_inst sup_inst_name        inst_no    inst_name
800           廣州市               5801       天河區
800           廣州市               5802       越秀區
800           廣州市               5803       白雲區

2.5 交叉(無限制) 串連
交叉串連用於對兩個源表進行純關係代數的乘運算。它不使用串連條件來限制結果集合，而是將分別來自兩個資料來源中的行以所有可能的方式進行組合。資料集合中一的每個行都要與資料集合二中的每一個行分別組成一個新的行。例如，如果第一個資料來源中有5個行，而第二個資料來源中有4個行，那麼在它們之間進行交叉串連就會產生20個行。人們將這種類型的結果集稱為笛卡爾乘積。
大多數交叉串連都是由於錯誤操作而造成的；但是它們卻非常適合向資料庫中填充例子資料，或者預先建立一些空行以便為程式執行期間所要填充的資料保留空間。
select *
from  t_institution i
cross join t_teller t
在交叉串連中沒有on條件子句