left join 和 left outer join 的區(qū)別
系統(tǒng)
2143 0
通俗的講:??
??A???
left
???
join
???B???的連接的記錄數(shù)與A表的記錄數(shù)同??
??A???
right
???
join
???B???的連接的記錄數(shù)與B表的記錄數(shù)同????
??A???
left
???
join
???B???等價(jià)B???
right
???
join
???A??
???
???
??
table
???A:??
Field_K,???Field_A??
??
1
???????????????????????a??
??
3
???????????????????????b??
??
4
???????????????????????c??
???
??
table
???B:??
??Field_K,???Field_B??
??
1
???????????????????????x??
??
2
???????????????????????y??
??
4
???????????????????????z??
???
??
select
???a.Field_K,???a.Field_A,???b.Field_K,???b.Field_B??
??
from
???a???
left
???
join
???b???
on
???a.Field_K
=
b.Field_K??
???
??Field_K?????????Field_A?????????Field_K?????????Field_B??????????
??
--
--------???----------???----------???----------????
??
1
?????????????????????a?????????????????????
1
?????????????????????x????????????????????
??
3
?????????????????????b?????????????????????
NULL
???????????????
NULL
??
??
4
?????????????????????c?????????????????????
4
?????????????????????z????????????????????
???
??
select
???a.Field_K,???a.Field_A,???b.Field_K,???b.Field_B??
??
from
???a???
right
???
join
???b???
on
???a.Field_K
=
b.Field_K??
???
??Field_K?????????Field_A?????????Field_K?????????Field_B??????????
??
--
--------???----------???----------???----------????
??
1
?????????????????????a?????????????????????
1
?????????????????????x????????????????????
??
NULL
???????????????
NULL
???????????????
2
?????????????????????y????????????????????
??
4
?????????????????????c?????????????????????
4
?????????????????????z?????
? --
舉個(gè)例子:??
??假設(shè)a表和b表的數(shù)據(jù)是這樣的。??
??a?????????????????????????b????
??id?????name id?????stock ??
??
1
a?????????????
1
?????????
15
??
??
2
?????????b?????????????
2
?????????
50
??
??
3
?????????c??????????????? ??
???
??
select
???
*
???
from
???a???
inner
???
join
???b???
on
???a.id
=
b.id??
??這個(gè)語(yǔ)法是連接查詢(xún)中的內(nèi)連接,它產(chǎn)生的結(jié)果是??
??兩個(gè)表相匹配的記錄出現(xiàn)在結(jié)果列表中。??
??根據(jù)上面的表,出現(xiàn)的結(jié)果是這樣的??
??a.id?????name?????b.id?????stock??
??
1
?????a?????????????
1
?????????
15
??
??
2
?????????????b?????????????
2
?????????
50
??
??
--
--------------------------??
??
select
???
*
???
from
???a,b???
where
???a.id
=
b.id??
??這個(gè)語(yǔ)法是內(nèi)連接的另外一種寫(xiě)法,其執(zhí)行結(jié)果與inner???
join
???一樣??
???
??
--
------------------------------????
???
??
select
???
*
???
from
???a???
left
/
right
???
join
???b???
on
???a.id
=
b.id??
??這個(gè)是外連接語(yǔ)法中的左外連接或右外連接??
??如果是左外連接的話,它將顯示a表的所有記錄,??
??
select
???a.
*
,b.
*
???
from
???a???
left
???
join
???b???
on
???a.id
=
b.id??
??查詢(xún)的結(jié)果是這樣的:??
??a.id?????name?????b.id?????stock??
??
1
???????a?????????
1
?????????????
15
??
??
2
???????????????b?????????
2
?????????????
50
??
??
3
???????????????c???????
null
?????????
null
??
??
--
------------------------------------------??
??如果是右外連接的話,它將顯示b表的所有記錄,??
??
select
???a.
*
,b.
*
???
from
???a???
right
???
join
???b???
on
???a.id
=
b.id??
??查詢(xún)的結(jié)果是這樣的:??
??a.id?????name?????b.id?????stock??
??
1
???????a?????????
1
?????????????
15
??
??
2
???????????????b?????????
2
?????????????
50
???
--
select
???a.
*
,b.
*
???
from
???a???
left
???
join
???b???
on
???a.k???
=
???b.k????
??
select
???a.
*
,b.
*
???
from
???a???
left
???
outer
???
join
???b???
on
???a.k???
=
b.k??
??
--
--------上面兩種一樣left???join是left???outer???join的簡(jiǎn)寫(xiě)??
??
select
???a.
*
,b.
*
???
from
???a???
left
???
inner
???
join
???b???
on
???a.k???
=
???b.k????
??沒(méi)有這種寫(xiě)法,錯(cuò)誤的語(yǔ)句.
--
在你要使用多個(gè)left???join的時(shí)候??
??比如說(shuō)10個(gè)??
??我們把10個(gè)全都寫(xiě)成left???join的形式??
??然后再SQL讓他自動(dòng)運(yùn)行一下,它會(huì)把最后一次出現(xiàn)的left???join變成left???
outer
???
join
??
??所以依此推理,最后一個(gè)left???join會(huì)以left???
outer
???join的形式存在??
??當(dāng)然,不管變不變對(duì)結(jié)果的顯示沒(méi)有任何影響??
??希望我的實(shí)驗(yàn)?zāi)軐?duì)你有所幫助???
--
使用關(guān)系代數(shù)合并數(shù)據(jù)
1
?關(guān)系代數(shù)
合并數(shù)據(jù)集合的理論基礎(chǔ)是關(guān)系代數(shù),它是由E.F.Codd于1970年提出的。
在關(guān)系代數(shù)的形式化語(yǔ)言中:
?????????用表、或者數(shù)據(jù)集合表示關(guān)系或者實(shí)體。
?????????用行表示元組。
?????????用列表示屬性。
關(guān)系代數(shù)包含以下8個(gè)關(guān)系運(yùn)算符
?????????選取――返回滿(mǎn)足指定條件的行。
?????????投影――從數(shù)據(jù)集合中返回指定的列。
?????????笛卡爾積――是關(guān)系的乘法,它將分別來(lái)自?xún)蓚€(gè)數(shù)據(jù)集合中的行以所有可能的方式進(jìn)行組合。
?????????并――關(guān)系的加法和減法,它可以在行的方向上合并兩個(gè)表中的數(shù)據(jù),就像把一個(gè)表壘在另一個(gè)表之上一樣。
?????????交――返回兩個(gè)數(shù)據(jù)集合所共有的行。
?????????差――返回只屬于一個(gè)數(shù)據(jù)集合的行。
?????????連接――在水平方向上合并兩個(gè)表,其方法是:將兩個(gè)表中在共同數(shù)據(jù)項(xiàng)上相互匹配的那些行合并起來(lái)。
?????????除――返回兩個(gè)數(shù)據(jù)集之間的精確匹配。
此外,作為一種實(shí)現(xiàn)現(xiàn)代關(guān)系代數(shù)運(yùn)算的方法,SQL還提供了:
?????????子查詢(xún)――類(lèi)似于連接,但更靈活;在外部查詢(xún)中,方式可以使用表達(dá)式、列表或者數(shù)據(jù)集合的地方都可以使用子查詢(xún)的結(jié)果。
本章將主要講述多種類(lèi)型的連接、簡(jiǎn)單的和相關(guān)的子查詢(xún)、幾種類(lèi)型的并、關(guān)系除以及其他的內(nèi)容。
2
?使用連接
2.1
?連接類(lèi)型
在關(guān)系代數(shù)中,連接運(yùn)算是由一個(gè)笛卡爾積運(yùn)算和一個(gè)選取運(yùn)算構(gòu)成的。首先用笛卡爾積完成對(duì)兩個(gè)數(shù)據(jù)集合的乘運(yùn)算,然后對(duì)生成的結(jié)果集合進(jìn)行選取運(yùn)算,確保只把分別來(lái)自?xún)蓚€(gè)數(shù)據(jù)集合并且具有重疊部分的行合并在一起。連接的全部意義在于在水平方向上合并兩個(gè)數(shù)據(jù)集合(通常是表),并產(chǎn)生一個(gè)新的結(jié)果集合,其方法是將一個(gè)數(shù)據(jù)源中的行于另一個(gè)數(shù)據(jù)源中和它匹配的行組合成一個(gè)新元組。
SQL提供了多種類(lèi)型的連接方式,它們之間的區(qū)別在于:從相互交疊的不同數(shù)據(jù)集合中選擇用于連接的行時(shí)所采用的方法不同。
連接類(lèi)型????????定義
內(nèi)連接????????只連接匹配的行
左外連接????????包含左邊表的全部行(不管右邊的表中是否存在與它們匹配的行),以及右邊表中全部匹配的行
右外連接????????包含右邊表的全部行(不管左邊的表中是否存在與它們匹配的行),以及左邊表中全部匹配的行
全外連接????????包含左、右兩個(gè)表的全部行,不管另外一邊的表中是否存在與它們匹配的行。
(H)(theta)連接????????使用等值以外的條件來(lái)匹配左、右兩個(gè)表中的行
交叉連接????????生成笛卡爾積-它不使用任何匹配或者選取條件,而是直接將一個(gè)數(shù)據(jù)源中的每個(gè)行與另一個(gè)數(shù)據(jù)源的每個(gè)行都一一匹配
在INFORMIX中連接表的查詢(xún)
如果FROM子句指定了多于一個(gè)表引用,則查詢(xún)會(huì)連接來(lái)自多個(gè)表的行。連接條件指定各列之間(每個(gè)表至少一列)進(jìn)行連接的關(guān)系。因?yàn)檎诒容^連接條件中的列,所以它們必須具有一致的數(shù)據(jù)類(lèi)型。
SELECT語(yǔ)句的FROM子句可以指定以下幾種類(lèi)型的連接
FROM子句關(guān)鍵字????????相應(yīng)的結(jié)果集
CROSS
?
JOIN
????????笛卡爾乘積(所有可能的行對(duì))
INNER
?
JOIN
????????僅對(duì)滿(mǎn)足連接條件的CROSS中的列
LEFT
?
OUTER
?
JOIN
????????一個(gè)表滿(mǎn)足條件的行,和另一個(gè)表的所有行
RIGHT
?
OUTER
?
JOIN
????????與LEFT相同,但兩個(gè)表的角色互換
FULL
?
OUTER
?
JOIN
????????
LEFT
?
OUTER
?和?
RIGHT
?OUTER中所有行的超集
2.2
?內(nèi)連接(
Inner
?
Join
)
內(nèi)連接是最常見(jiàn)的一種連接,它頁(yè)被稱(chēng)為普通連接,而E.FCodd最早稱(chēng)之為自然連接。
下面是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可以省略。
等價(jià)于早期的連接語(yǔ)法
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
)
全外連接返回參與連接的兩個(gè)數(shù)據(jù)集合中的全部數(shù)據(jù),無(wú)論它們是否具有與之相匹配的行。在功能上,它等價(jià)于對(duì)這兩個(gè)數(shù)據(jù)集合分別進(jìn)行左外連接和右外連接,然后再使用消去重復(fù)行的并操作將上述兩個(gè)結(jié)果集合并為一個(gè)結(jié)果集。
在現(xiàn)實(shí)生活中,參照完整性約束可以減少對(duì)于全外連接的使用,一般情況下左外連接就足夠了。在數(shù)據(jù)庫(kù)中沒(méi)有利用清晰、規(guī)范的約束來(lái)防范錯(cuò)誤數(shù)據(jù)情況下,全外連接就變得非常有用了,你可以使用它來(lái)清理數(shù)據(jù)庫(kù)中的數(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)連接查詢(xún)中加入條件是,無(wú)論是將它加入到j(luò)oin子句,還是加入到where子句,其效果是完全一樣的,但對(duì)于外連接情況就不同了。當(dāng)把條件加入到?join子句時(shí),SQL?Server、Informix會(huì)返回外連接表的全部行,然后使用指定的條件返回第二個(gè)表的行。如果將條件放到where子句中,SQL?Server將會(huì)首先進(jìn)行連接操作,然后使用where子句對(duì)連接后的行進(jìn)行篩選。下面的兩個(gè)查詢(xún)展示了條件放置位子對(duì)執(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
?自身連接
自身連接是指同一個(gè)表自己與自己進(jìn)行連接。這種一元連接通常用于從自反關(guān)系(也稱(chēng)作遞歸關(guān)系)中抽取數(shù)據(jù)。例如人力資源數(shù)據(jù)庫(kù)中雇員與老板的關(guān)系。
下面例子是在機(jī)構(gòu)表中查找本機(jī)構(gòu)和上級(jí)機(jī)構(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
?交叉(無(wú)限制)?連接
交叉連接用于對(duì)兩個(gè)源表進(jìn)行純關(guān)系代數(shù)的乘運(yùn)算。它不使用連接條件來(lái)限制結(jié)果集合,而是將分別來(lái)自?xún)蓚€(gè)數(shù)據(jù)源中的行以所有可能的方式進(jìn)行組合。數(shù)據(jù)集合中一的每個(gè)行都要與數(shù)據(jù)集合二中的每一個(gè)行分別組成一個(gè)新的行。例如,如果第一個(gè)數(shù)據(jù)源中有5個(gè)行,而第二個(gè)數(shù)據(jù)源中有4個(gè)行,那么在它們之間進(jìn)行交叉連接就會(huì)產(chǎn)生20個(gè)行。人們將這種類(lèi)型的結(jié)果集稱(chēng)為笛卡爾乘積。
大多數(shù)交叉連接都是由于錯(cuò)誤操作而造成的;但是它們卻非常適合向數(shù)據(jù)庫(kù)中填充例子數(shù)據(jù),或者預(yù)先創(chuàng)建一些空行以便為程序執(zhí)行期間所要填充的數(shù)據(jù)保留空間。
select
?
*
from
??t_institution?i
cross
?
join
?t_teller?t
在交叉連接中沒(méi)有on條件子句
3
?APPENDIX
3.1
?A?參考資料與資源
?????????《Microsoft?SQL?Server?
2000
?Bile》Paul?Nielsen
?????????Paul?Nielsen的Web站點(diǎn)
[
url
]
www.isnotnull.com
[
/url
]
3.2
?注文章所有SQL在IBM?Informix?Dynamic?Server?Version?
9.40
.TC2E1測(cè)試通過(guò)
--
表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
實(shí)驗(yàn)如下:
1
.
left
?
join
sql語(yǔ)句如下:
select
?
*
?
from
?A
left
?
join
?B
on
?A.aID?
=
?B.bID
結(jié)果如下:
aID????????aNum???????????????????bID???????????bName
1
????????????a20050111?????????
1
???????????????
2006032401
2
????????????a20050112?????????
2
??????????????
2006032402
3
????????????a20050113?????????
3
??????????????
2006032403
4
????????????a20050114?????????
4
??????????????
2006032404
5
????????????a20050115?????????
NULL
???????
NULL
(所影響的行數(shù)為?
5
?行)
結(jié)果說(shuō)明:
????????
left
?join是以A表的記錄為基礎(chǔ)的,A可以看成左表,B可以看成右表,
left
?join是以左表為準(zhǔn)的.
換句話說(shuō),左表(A)的記錄將會(huì)全部表示出來(lái),而右表(B)只會(huì)顯示符合搜索條件的記錄(例子中為:?A.aID?
=
?B.bID).
B表記錄不足的地方均為NULL.
2
.
right
?
join
sql語(yǔ)句如下:
select
?
*
?
from
?A
right
?
join
?B
on
?A.aID?
=
?B.bID
結(jié)果如下:
aID????????aNum???????????????????bID???????????bName
1
????????????a20050111?????????
1
???????????????
2006032401
2
????????????a20050112?????????
2
??????????????
2006032402
3
????????????a20050113?????????
3
??????????????
2006032403
4
????????????a20050114?????????
4
??????????????
2006032404
NULL
????
NULL
???????????????????
8
??????????????
2006032408
(所影響的行數(shù)為?
5
?行)
結(jié)果說(shuō)明:
????????仔細(xì)觀察一下,就會(huì)發(fā)現(xiàn),和left?join的結(jié)果剛好相反,這次是以右表(B)為基礎(chǔ)的,A表不足的地方用NULL填充.
3
.
inner
?
join
sql語(yǔ)句如下:
select
?
*
?
from
?A
innerjoin?B
on
?A.aID?
=
?B.bID
結(jié)果如下:
aID????????aNum???????????????????bID???????????bName
1
????????????a20050111?????????
1
???????????????
2006032401
2
????????????a20050112?????????
2
??????????????
2006032402
3
????????????a20050113?????????
3
??????????????
2006032403
4
????????????a20050114?????????
4
??????????????
2006032404
結(jié)果說(shuō)明:
????????很明顯,這里只顯示出了?A.aID?
=
?B.bID的記錄.這說(shuō)明inner?join并不以誰(shuí)為基礎(chǔ),它只顯示符合條件的記錄.
-----------------[以下為網(wǎng)上的一點(diǎn)資料]------------------
LEFT
?JOIN操作用于在任何的?
FROM
?子句中,組合來(lái)源表的記錄。使用?
LEFT
?
JOIN
?運(yùn)算來(lái)創(chuàng)建一個(gè)左邊外部聯(lián)接。左邊外部聯(lián)接將包含了從第一個(gè)(左邊)開(kāi)始的兩個(gè)表中的全部記錄,即使在第二個(gè)(右邊)表中并沒(méi)有相符值的記錄。
語(yǔ)法:
FROM
?table1?
LEFT
?
JOIN
?table2?
ON
?table1.field1?compopr?table2.field2
說(shuō)明:table1,?table2參數(shù)用于指定要將記錄組合的表的名稱(chēng)。
field1,?field2參數(shù)指定被聯(lián)接的字段的名稱(chēng)。且這些字段必須有相同的數(shù)據(jù)類(lèi)型及包含相同類(lèi)型的數(shù)據(jù),但它們不需要有相同的名稱(chēng)。
compopr參數(shù)指定關(guān)系比較運(yùn)算符:"
=
",?"
<
",?"
>
",?"
<=
",?"
>=
"?或?"
<>
"。
如果在INNER?JOIN操作中要聯(lián)接包含Memo?數(shù)據(jù)類(lèi)型或?OLE?Object?數(shù)據(jù)類(lèi)型數(shù)據(jù)的字段,將會(huì)發(fā)生錯(cuò)誤。
left join 和 left outer join 的區(qū)別
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元