SQL中的left outer join,inner join,right outer join用法 （左右內連接）

SQL語句中的left outer join,inner join,right outer join用法

left outer join=left join ,?? right outer join=right join,???? inner join=join.
使用關系代數合并數據
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條件子句


Inner Join
Inner Join 應該是最常用的 Join 方式, 它只會傳回符合 Join 規則的紀錄, 還是先來看看語法
Select <要選擇的字段> From <主要資料表>
<Join 方式> <次要資料表> [On <Join 規則>]
Select ProductId, ProductName, Suppliers.SupplierId
From Products
Inner Join Suppliers
Products.Suppliers = Suppliers.SupplierId
Inner Join 的主要精神就是 exclusive , 叫它做排他性吧! 就是講 Join 規則不相符的資料就會被排除掉,
譬如講在 Product 中有一項產品的供貨商代碼 (SupplierId), 沒有出現在 Suppliers 資料表中, 那么這筆記錄便會被排除掉
Outer Join
這款的 Join 方式是一般人比較少用到的, 甚至有些 SQL 的管理者也從未用過, 這真是一件悲哀的代志, 因為善用 Outer Join 是可以簡化一些查詢的工作的, 先來看看 Outer Join 的SQL語句和語法
Select <要查詢的字段> From <Left 資料表>
<Left | Right> [Outer] Join <Right 資料表> On <Join 規則>
語法中的 Outer 是可以省略的, 例如你可以用 Left Join 或是 Right Join, 在本質上, Outer Join 是 inclusive, 叫它做包容性吧! 不同于 Inner Join 的排他性, 因此在 Left Outer Join 的查詢結果會包含所有 Left 資料表的資料, 顛倒過來講, Right Outer Join 的查詢就會包含所有 Right 資料表的資料
總結：inner join是內部連接,outer join是外部接。inner求出的是交集,outer表示某個表的所有行,不管另一個表有沒有這一行
INNER Join code as the following:
Select * from A a, B b where a.categoryID = b.categoryID;
equals:
Select * from A a inner join B b on a.categoryID = b.categoryID;
OUTER Join code as the following
select * from A a full(left/right) outer?? join B b?? on a on a.categoryID = b.categoryID;
left/right outer join claus specific?? for MSSQL:
Select * from A a, B b where a.categoryID?? *=???? bcategoryID;
elect * from A a, B b where a.categoryID?????? =*?? b.categoryID;
left/right outer join claus specific for Oracle:
Select * from A a, B b where a.categoryID?????? =?? b.categoryID(+);
Select * from A a, B b where a.categoryID (+) =?? b.categoryID;

?

A final thing worth discussing here is the use of aliases. Aliases aren’t necessarily related to table joins, but they become especially useful (and sometimes necessary) when joining tables, and they assign different (and usually shorter) names for the tables involved. Aliases are necessary when joining a table with itself, in which case you need to assign different aliases for its different instances to differentiate them. The following query returns the same products as the query before, but it uses aliases:

SELECT p.ProductID, p.Name
FROM ProductCategory pc INNER JOIN Product p
ON p.ProductID = pc.ProductID
WHERE pc.CategoryID = 5