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

left outer join=left join ,?? right outer join=right join,???? inner join=join.
使用關(guān)系代數(shù)合并數(shù)據(jù)
1 關(guān)系代數(shù)
合并數(shù)據(jù)集合的理論基礎(chǔ)是關(guān)系代數(shù)，它是由E.F.Codd于1970年提出的。
在關(guān)系代數(shù)的形式化語言中：
?????????? 用表、或者數(shù)據(jù)集合表示關(guān)系或者實體。
?????????? 用行表示元組。
?????????? 用列表示屬性。
關(guān)系代數(shù)包含以下8個關(guān)系運算符
?????????? 選取――返回滿足指定條件的行。

?????????? 投影――從數(shù)據(jù)集合中返回指定的列。
????????? 笛卡爾積――是關(guān)系的乘法，它將分別來自兩個數(shù)據(jù)集合中的行以所有可能的方式進(jìn)行組合。
?????????? 并――關(guān)系的加法和減法，它可以在行的方向上合并兩個表中的數(shù)據(jù)，就像把一個表壘在另一個表之上一樣。

?????????? 交――返回兩個數(shù)據(jù)集合所共有的行。
????????? 差――返回只屬于一個數(shù)據(jù)集合的行。
????????? 連接――在水平方向上合并兩個表，其方法是：將兩個表中在共同數(shù)據(jù)項上相互匹配的那些行合并起來。
????????? 除――返回兩個數(shù)據(jù)集之間的精確匹配。
此外，作為一種實現(xiàn)現(xiàn)代關(guān)系代數(shù)運算的方法，SQL還提供了：
????????? 子查詢――類似于連接，但更靈活；在外部查詢中，方式可以使用表達(dá)式、列表或者數(shù)據(jù)集合的地方都可以使用子查詢的結(jié)果。
本章將主要講述多種類型的連接、簡單的和相關(guān)的子查詢、幾種類型的并、關(guān)系除以及其他的內(nèi)容。
2 使用連接
2.1 連接類型
在關(guān)系代數(shù)中，連接運算是由一個笛卡爾積運算和一個選取運算構(gòu)成的。首先用笛卡爾積完成對兩個數(shù)據(jù)集合的乘運算，然后對生成的結(jié)果集合進(jìn)行選取運算，確保只把分別來自兩個數(shù)據(jù)集合并且具有重疊部分的行合并在一起。連接的全部意義在于在水平方向上合并兩個數(shù)據(jù)集合（通常是表），并產(chǎn)生一個新的結(jié)果集合，其方法是將一個數(shù)據(jù)源中的行于另一個數(shù)據(jù)源中和它匹配的行組合成一個新元組。
SQL提供了多種類型的連接方式，它們之間的區(qū)別在于：從相互交疊的不同數(shù)據(jù)集合中選擇用于連接的行時所采用的方法不同。
連接類型?????????? 定義
內(nèi)連接?????????? 只連接匹配的行
左外連接?????????? 包含左邊表的全部行（不管右邊的表中是否存在與它們匹配的行），以及右邊表中全部匹配的行
右外連接?????????? 包含右邊表的全部行（不管左邊的表中是否存在與它們匹配的行），以及左邊表中全部匹配的行
全外連接?????????? 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們匹配的行。
(H)(theta)連接?????????? 使用等值以外的條件來匹配左、右兩個表中的行
交叉連接?????????? 生成笛卡爾積－它不使用任何匹配或者選取條件，而是直接將一個數(shù)據(jù)源中的每個行與另一個數(shù)據(jù)源的每個行都一一匹配
在INFORMIX中連接表的查詢
如果FROM子句指定了多于一個表引用，則查詢會連接來自多個表的行。連接條件指定各列之間（每個表至少一列）進(jìn)行連接的關(guān)系。因為正在比較連接條件中的列，所以它們必須具有一致的數(shù)據(jù)類型。
SELECT語句的FROM子句可以指定以下幾種類型的連接
FROM子句關(guān)鍵字?????????? 相應(yīng)的結(jié)果集
CROSS JOIN?????????? 笛卡爾乘積（所有可能的行對）
INNER JOIN?????????? 僅對滿足連接條件的CROSS中的列
LEFT OUTER JOIN?????????? 一個表滿足條件的行，和另一個表的所有行
RIGHT OUTER JOIN?????????? 與LEFT相同，但兩個表的角色互換
FULL OUTER JOIN???????? LEFT OUTER 和 RIGHT OUTER中所有行的超集

2.2 內(nèi)連接（Inner Join）
內(nèi)連接是最常見的一種連接，它頁被稱為普通連接，而E.FCodd最早稱之為自然連接。
下面是ANSI SQL－92標(biāo)準(zhǔn)
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)
全外連接返回參與連接的兩個數(shù)據(jù)集合中的全部數(shù)據(jù)，無論它們是否具有與之相匹配的行。在功能上，它等價于對這兩個數(shù)據(jù)集合分別進(jìn)行左外連接和右外連接，然后再使用消去重復(fù)行的并操作將上述兩個結(jié)果集合并為一個結(jié)果集。
在現(xiàn)實生活中，參照完整性約束可以減少對于全外連接的使用，一般情況下左外連接就足夠了。在數(shù)據(jù)庫中沒有利用清晰、規(guī)范的約束來防范錯誤數(shù)據(jù)情況下，全外連接就變得非常有用了，你可以使用它來清理數(shù)據(jù)庫中的數(shù)據(jù)。
select *
from???? t_institution i
full outer join t_teller t
on i.inst_no = t.inst_no
2.3.4?????????? 外連接與條件配合使用
當(dāng)在內(nèi)連接查詢中加入條件是，無論是將它加入到j(luò)oin子句，還是加入到where子句，其效果是完全一樣的，但對于外連接情況就不同了。當(dāng)把條件加入到j(luò)oin子句時，SQL Server、Informix會返回外連接表的全部行，然后使用指定的條件返回第二個表的行。如果將條件放到where子句中，SQL Server將會首先進(jìn)行連接操作，然后使用where子句對連接后的行進(jìn)行篩選。下面的兩個查詢展示了條件放置位子對執(zhí)行結(jié)果的影響：
條件在join子句
select *
from???? t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
and i.inst_no = “5801”
結(jié)果是：
inst_no?????? inst_name?????????????? inst_no?????? teller_no???? teller_name
5801???????? 天河區(qū)???????????????? 5801???????? 0001???????? tom
5801???????? 天河區(qū)???????????????? 5801???????? 0002???????? david
5802???????? 越秀區(qū)
5803???????? 白云區(qū)
條件在where子句
select *
from???? t_institution i
left outer join t_teller t
on i.inst_no = t.inst_no
where i.inst_no = “5801”
結(jié)果是：
inst_no?????? inst_name?????????????? inst_no?????? teller_no???? teller_name
5801???????? 天河區(qū)???????????????? 5801???????? 0001???????? tom
5801???????? 天河區(qū)???????????????? 5801???????? 0002???????? david

2.4 自身連接
自身連接是指同一個表自己與自己進(jìn)行連接。這種一元連接通常用于從自反關(guān)系（也稱作遞歸關(guān)系）中抽取數(shù)據(jù)。例如人力資源數(shù)據(jù)庫中雇員與老板的關(guān)系。
下面例子是在機構(gòu)表中查找本機構(gòu)和上級機構(gòu)的信息。
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

結(jié)果是：
superior_inst sup_inst_name?????????? inst_no?????? inst_name
800???????????? 廣州市???????????????? 5801???????? 天河區(qū)
800???????????? 廣州市???????????????? 5802???????? 越秀區(qū)
800???????????? 廣州市???????????????? 5803???????? 白云區(qū)

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


Inner Join
Inner Join 應(yīng)該是最常用的 Join 方式, 它只會傳回符合 Join 規(guī)則的紀(jì)錄, 還是先來看看語法
Select <要選擇的字段> From <主要資料表>
<Join 方式> <次要資料表> [On <Join 規(guī)則>]
Select ProductId, ProductName, Suppliers.SupplierId
From Products
Inner Join Suppliers
Products.Suppliers = Suppliers.SupplierId
Inner Join 的主要精神就是 exclusive , 叫它做排他性吧! 就是講 Join 規(guī)則不相符的資料就會被排除掉,
譬如講在 Product 中有一項產(chǎn)品的供貨商代碼 (SupplierId), 沒有出現(xiàn)在 Suppliers 資料表中, 那么這筆記錄便會被排除掉
Outer Join
這款的 Join 方式是一般人比較少用到的, 甚至有些 SQL 的管理者也從未用過, 這真是一件悲哀的代志, 因為善用 Outer Join 是可以簡化一些查詢的工作的, 先來看看 Outer Join 的SQL語句和語法
Select <要查詢的字段> From <Left 資料表>
<Left | Right> [Outer] Join <Right 資料表> On <Join 規(guī)則>
語法中的 Outer 是可以省略的, 例如你可以用 Left Join 或是 Right Join, 在本質(zhì)上, Outer Join 是 inclusive, 叫它做包容性吧! 不同于 Inner Join 的排他性, 因此在 Left Outer Join 的查詢結(jié)果會包含所有 Left 資料表的資料, 顛倒過來講, Right Outer Join 的查詢就會包含所有 Right 資料表的資料
總結(jié)：inner join是內(nèi)部連接,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

轉(zhuǎn)載于:https://www.cnblogs.com/elock/archive/2009/12/05/1617706.html

總結(jié)

以上是生活随笔為你收集整理的SQL中的left outer join,inner join,right outer join用法 （左右内连接）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。