SQL應用與開發：（五）多個資料表的連接配接

資料庫中的各個表中存儲着不同的資料，使用者往往需要用多個表中的資料來組合、提煉出所需要的資訊。如果一個查詢需要對多個表進行操作，就稱為連接配接查詢。連接配接查詢的結果集或結果表稱為表之間的連接配接。查詢實際上是通過各個表之間共同列的關聯性來查詢資料的，它是關系型資料庫查詢最主要的特征。

實作連接配接的結果是在向資料庫增添新類型的資料方面沒有限制，具有很大的靈活性。通常總是通過連接配接建立一個新表，以包含不同表中的資料。如果新表有合适的域，就可以将它連接配接到現有的表。

1.簡單連接配接操作

最簡單的連接配接方式是在select語句的from子句中用逗号将不同的基表隔開，使用where子句建立的同等連接配接是使第一個基表中一個或者多個列中的值與第二個基表中相應的一個或者多個列的值相等的連接配接。這樣在查詢結果中隻顯示兩個基表中列的值相比對的行。但是值得注意的是，無論不同表中的列是否具有相同的列名，都相應的通過增加表名來限制列名。

例如，在資料庫“銷售關系系統”中，從“業務員資訊”和“客戶資訊”連個表中，查詢“業務員編号”大于1004，并且在select清單中增加一個列“業務員編号”：

select 業務員資訊.業務員編号，業務員資訊.業務員編号，客戶資訊.客戶姓名，客戶資訊.客戶位址，客戶資訊.聯系電話

from 業務員資訊，客戶資訊

where 業務員資訊.業務員編号=客戶資訊.所屬業務員編号 and 業務員資訊.業務員編号>1004

執行後，便是我們條件的結果。

在該查詢中，我們也可以使用相關名稱，“業務員資訊”表用别名a代替，“客戶資訊”表用b代替，當引用這連個表時，除了from子句外，在select語句的任何地方都可以使用a和b,那麼我們上述的語句就變成了：

select a.業務員編号，a.業務員編号，b.客戶姓名，b.客戶位址，b.聯系電話

from 業務員資訊 a ，客戶資訊 b

where a.業務員編号=b.所屬業務員編号 and a.業務員編号>1004

其執行結果，和前面的完全相同。

這也為後面的多個表的連接配接的 學習奠定了基礎。例如，在資料庫“珠寶銷售系統”中，基于“珠寶資訊”、“珠寶商資訊”和“銷售明細資訊”三個表建立一個查詢。要求傳回“珠寶資訊”表中的列“珠寶名稱”，“珠寶商資訊”表中的列“珠寶商姓名”和“銷售明細資訊”表中的列“利潤”：

select a.珠寶名稱，b.珠寶商姓名，c.利潤

from 珠寶商資訊 as a ，珠寶商資訊 as b，銷售明細資訊 as c

where a.珠寶代号=c.珠寶代号 and a.珠寶商編号=b.珠寶商編号

執行後，便是所查詢結果集。

2.内連接配接

内連接配接是最常用的連接配接查詢，一般用inner jion關鍵字來指定内連接配接。但是，inner不是必需的。如果隻用join關鍵字，還必須定義一個on子句。内連接配接查詢操作列出與連接配接條件比對的資料行，它使用比較運算符比較被連接配接的列值。

2.1等值連接配接

所謂等值連接配接解釋在連接配接條件中使用等于号(=)運算符比較被連接配接列的值，其查詢結果中列出被連接配接表中的所有列，包括其中的重複列。換句話說，基表之間的連接配接通過相等的列值連接配接起來的查詢就是等值連接配接查詢。

等值連接配接查詢可以用兩種表示方式來指定連接配接條件。例如，在資料庫“銷售關聯系統”中，基于“商品資訊”和“供應商資訊”兩個表建立一個查詢。限定查詢條件為兩個表中的“供應商編号”相等時傳回，并要求傳回的列為“商品資訊”表中的“商品名稱”和“供應商資訊”表中的列“供應商名稱”、“聯系人姓名”。

select a.商品名稱，b.供應商名稱，b.聯系人姓名，a.單價

from 商品資訊 a，供應商名稱 b

where a.供應商編号=b.供應商編号

在上述語句的where子句中，用"="指定查詢為等值連接配接查詢。

還可以在查詢語句的from子句中使用inner join關鍵字連指定查詢是等值連接配接查詢。

from 商品資訊 a inner join 供應商資訊 b

                           on a.供應商編号=b.供應商編号 

執行後，與上述語句所得結果相同。

也可以對連接配接查詢所得的結果利用order by子句進行排序。例如，将上述的的等值連接配接查詢的查詢按列“單價”的降序進行排列。

order by a.單價 desc

運作後，唯一與上述結果不同的是，該查詢的結果根據“單價”對查詢的結果進行了降序排序。

2.2非等值連接配接

在等值連接配接查詢的連接配接條件中不适用等号，而使用其他比較符就構成了非等值連接配接查詢。也就是說，非等值連接配接查詢是在連接配接條件中使用了等于運算符以外的其他比較運算符比較被連接配接列的列值。在非等值連接配接查詢中，可以使用的比較傲運算符有：>、<、>=、<=、!=，還可以使用between...and之類的關鍵字。

例如，從資料庫“銷售管理系統”中，基于“供應商資訊”和“入庫單資訊”兩個表建立一個查詢。限定查詢條件為兩個表中的“供應商編号”不相等，并傳回列“供應商名稱”和“供應商所在城市”。

select 供應商名稱，供應商所在城市

from 供應商資訊 a inner join 入庫單資訊 b

on a.供應商編号!=b.供應商編号

執行上述語句後，其查詢結果傳回80行，單大部分都是重複的。我們利用下述語句來消除重複行。

select  distinct 供應商名稱，供應商所在城市

該查詢結果中資訊與“供應商資訊”表中列“供應商名稱”和“供應商所在城市”中的資訊完全相同，是以上述查詢并沒有實際應用價值，這就說明非等值連接配接查詢往往需要同其他連接配接查詢結合使用，尤其是與等值連接配接查詢結合。

2.3自然連接配接

自然連接配接是在連接配接條件中使用等于号(=)運算符比較被連接配接列的列值，但它使用選擇清單指出查詢結果集中所包含的列，并删除連接配接表中的重複列。簡單的說，在等值連接配接中去掉重複的屬性列，即為自然連接配接。

自然連接配接為具有相同名稱的列自動進行記錄比對。自然連接配接不必指定任何同等連接配接條件。sql實作方式判斷出具有相同名稱的列然後形成比對。然而，自然連接配接雖然可以指定查詢結果包括的列，但是不能指定被比對的列。

例如，在資料庫“珠寶營銷系統”中，基于“顧客資訊”和“珠寶商資訊”兩個表建立了一個自然連接配接查詢。這個連接配接查詢的限定條件是兩個表中“消費者所在城市”和“珠寶商所在城市”相同，并按照列“消費者姓名”、“消費者位址”、“珠寶商姓名”和“珠寶商位址”傳回查詢結果。

select 消費者姓名，消費者位址，消費者所在城市 as 城市，珠寶商姓名。珠寶商位址

from 顧客資訊 a inner join b

on a.消費者所在城市=b.珠寶商所在城市

執行上述語句後，對其結果進行分析。盡管利用自然查詢能夠消除查詢結果中重複的行，但是從上述語句的查詢結果中能夠發現，該查詢結果也是由笛卡爾積形成的。

3.外連接配接

當至少有一個同屬于兩個表的行符合連接配接條件時，内連接配接才傳回行。而外連接配接傳回所有的比對航和一定的不比對行，這主要取決于所建立的連接配接的類型。sql支援的3種類型的外連接配接：

左外連接配接：傳回所有的比對行并從關鍵字join左邊的表中傳回所有不比對的行。

右外連接配接：傳回所有的比對行并從關鍵字join右邊的表中傳回所有不比對的行。

完全連接配接：傳回所有的比對行和不比對的行。

3.1左外連接配接

左外連接配接的查詢結果集中包括指定左表中的所有行，而不僅僅是連接配接列所比對的行。如果左表的某行在右表中沒有找到相比對的行，則結果集中的右表的相對應位置為null。

在左外連接配接查詢的select語句中，用left outer join關鍵字對兩個基表進行連接配接。

例如，在資料庫“珠寶營銷系統”中，以在同一個城市的消費者和珠寶商為條件，對“消費者資訊”和“珠寶商資訊”表進行左外連接配接查詢。

select  消費者姓名，消費者位址，消費者所在城市 as 城市，珠寶商姓名，珠寶商位址

from 顧客資訊 a left outer join 珠寶商資訊 b

執行後，查詢其結果中包含了三行含有null值得資料，盡管這三行沒有比對列，但在查詢結果中仍然被包括進去，原因就是這三行中列“消費者姓名”中的資訊包含在左表中。這就是說，在進行左外連接配接查詢時，無論左表中的行是否能夠在右表中找到比對的行，查詢結果中都将被顯示在該行，與其他行唯一不同的是，右表中與該行對應的位置用null值代替。

3.2右外連接配接

右外連接配接就是左外連接配接的反向連接配接，隻不過在插叙結果集中包括的是指定右表的所有行。如果右表的某行在左表中沒有找到相比對的行，則結果集中的左表的相對應位置為null。

在右外連接配接查詢的selecet語句中，用right outer join關鍵字對兩個基表進行連接配接。

例如，在資料庫“珠寶商營銷系統”中，以在同一個城市的消費者和珠寶商為條件，對“消費者資訊”和“珠寶商資訊”表進行右外連接配接。

from 顧客資訊 a right outer join 珠寶商資訊 b

執行後，查詢其結果中包含了兩行含有null值得資料，盡管這兩行沒有比對列，但在查詢結果中仍然被包括進去，原因就是這兩行中列“珠寶商姓名”中的資訊包含在右表中。這就是說，在進行右外連接配接查詢時，無論右表中的行是否能夠在左表中找到比對的行，查詢結果中都将被顯示在該行，與其他行唯一不同的是，左表中與該行對應的位置用null值代替。

3.3完全連接配接

完全連接配接傳回左表和右表中的所有行。當某行在另一個表中沒有比對行時，則另一個表與之相對應列值為null。如果表之間右比對行，則整個結果集包含基表的資料值。

在完全連接配接查詢的select語句中，用full outer join關鍵字對兩個基表進行連接配接。

例如，在資料庫“珠寶商營銷系統”中，以在同一個城市的消費者和珠寶商為條件，對“消費者資訊”和“珠寶商資訊”表進行完全連接配接。

from 顧客資訊 a full outer join 珠寶商資訊 b

執行後，查詢其結果中包含了六行含有null值得資料，盡管這六行沒有比對列，但在查詢結果中仍然被包括進去，原因就是這兩行中列“珠寶商姓名”中的資訊包含在右表中。這就是說，在進行完全連接配接查詢時，無論左表中還是右表是否能夠找到比對的行，它都在查詢結果中顯示該行，而隻是在找不到比對的位置上用null值代替。

4.聯合查詢

如果有多個不同的查詢結果，但又不希望将它們連接配接在一起，組成資料。在這種情況下，可以使用union子句。使用union子句的查詢稱為聯合查詢，它可以将兩個或者更多查詢的結果組合為一個單個結果集，該結果集包含聯合查詢中所有查詢結果集中的全部行資料。聯合查詢不同于對兩個表中的列進行連接配接查詢，前者是組合兩個表中的行，後者是比對兩個表中的列資料。聯合查詢的文法格式為：

select <select_list>

from <table_reference>

[where <search_condition>]

{union [all]

[where <search_condition>]}

[order by <order_condition>]

在進行聯合查詢時，union子句會自動删除重複的行，查詢結果的列标題為第一個查詢語句的列标題。是以，必須在第一個查詢語句中定義列标題。

例如，從資料庫”珠寶營銷系統“的"顧客資訊"表中，查詢”消費者所在城市“為”北京市“的消費者的姓名、家庭住址和聯系電話，并為其增加類型列為”類别“，列的内容為”消費者“；從”珠寶商資訊“表中，查詢”珠寶商所在城市“同樣是”北京市“的珠寶商的相關資訊，并增加一個列，列的内容為“珠寶商”；最後，将兩個查詢的結果聯合在一起。

select 消費者姓名 as 姓名，消費者位址 as 家庭住址，聯系電話，'消費者' as 類别

from 顧客資訊

where 消費者所在城市='北京市'

union

select 珠寶商姓名，珠寶商位址，電話，'珠寶商'

from 珠寶商資訊

where 珠寶商所在城市='北京市'

在使用union的select語句中，如果要對聯合查詢結果進行排序，則必須使用第一個查詢語句中的列名、列标題或者列序号。并且排序子句order by中最好用數字來指定排序次序，如果不用數字，則聯合查詢的子查詢中的列名就需要相同，也可以使用别名來統一列名。

另外，在對聯合查詢的結果進行排序的結果進行排序時，必須把order by子句放在select子句的後面。

例如，從資料庫“銷售管理系統”的“客戶資訊”表中，查詢“客戶編号”不大于1005的客戶的姓名、家庭住址和聯系電話，并為其增加類型列“職能”，列的内容為“客戶”；從“業務員資訊”表中，查詢“業務員編号”不大于1005的業務員的姓名、家庭住址和聯系電話，并增加一個列，列的内容為“業務員”；最後，将兩個查詢結果聯合在一起，并按類型“職能”的升序排列。

select 客戶姓名 as 姓名，客戶位址 as 家庭住址，聯系電話，'客戶' as 職能

from 客戶資訊

where 客戶編号<= 1005

select 業務員姓名，家庭住址，電話，'業務員'

from 業務員資訊

where 業務員編号<=1005

order by 職能

union all是另外一種對表進行聯合的方法。它與union唯一不同的差別是它不删除重複的行，也不對行進行自動排序。在對表進行聯合查詢時，如果以希望在查詢結果中顯示重複的行，就可以使用union all。在此，不再一一舉例。

5.交叉連接配接和自連接配接

在連接配接查詢中還有兩個比較特殊的連接配接查詢方式：交叉連接配接和自連接配接。在交叉連接配接的查詢結果中，兩個表中每兩個可能成對的行占一行。自連接配接就是一個表與自身進行連接配接查詢。

5.1交叉連接配接

兩個表進行交叉連接配接将生成來自這兩個表的各行的所有可能組合。交叉連接配接在不帶where子句時，傳回的是被連接配接的兩個表所有行的笛卡爾積，即傳回到結果集中的行數等于第一個表中符合查詢條件的行數乘以第二個表中符合查詢條件的行數。當交叉連接配接帶有where子句時，傳回的是連接配接兩個表的所有行的笛卡爾積減去where子句所現在而省略的所有行數。

交叉連接配接與前面介紹的基本連接配接操作非常相似。唯一不同的是，在from子句中，多個表名之間不是用逗号，而是用cross join關鍵字隔開；并且在進行交叉連接配接時，不能像内連接配接和外連接配接一樣使用on 關鍵字來限定連接配接條件，但是可以将連接配接條件限定在where子句中。

例如，在資料庫“銷售管理系統”中，對“商品資訊”表和“供應商資訊”表進行交叉連接配接。要求傳回商品的産地和供應商所在城市都是“上海市”或者構思“南京市”的相關資訊。

select 商品資訊.商品名稱，商品資訊.産地，供應商資訊.供應商名稱

from 商品資訊 cross join 供應商資訊

where 商品資訊.産地=供應商資訊.供應商所在城市

and (商品資訊.産地='上海市' or 商品資訊.産地='南京市')

order by 商品資訊.産地

5.2自連接配接

連接配接不僅可以再不同表之間進行，也可以使一個表同其自身進行連接配接，這種連接配接成為自連接配接，相應的查詢稱為子連接配接查詢。子連接配接是與表本身進行的内連接配接或者外連接配接。

子連接配接的連接配接操作可以利用别名的方法實作一個表自身的連接配接。實質上，這種子身連接配接方法與兩個表的連接配接操作完全相似。隻是在每次列出這個表時便為它命名一個别名。

例如，對資料庫“銷售管理系統”中的“客戶資訊”表進行自連接配接，查詢“客戶位址”相同的客戶資訊，并在查詢結果中隻傳回“客戶位址”為“北京市海澱區”或者“北京市朝陽區”的相關資訊。

select a.客戶姓名，a.聯系電話，a.客戶位址，b.客戶姓名，b.聯系電話

from 客戶資訊 a inner join 客戶資訊 b

on a.客戶位址=b.客戶位址

where a.客戶位址='北京市海澱區'

or a.客戶位址='北京市朝陽區'

由于此查詢涉及“客戶資訊”表與自身的連接配接，是以“客戶資訊”表以兩種角色顯示。要區分這連個角色，必須在from子句中為“客戶資訊”表中提供兩個不同的别名（a 和b），這兩個别名用來限定查詢中的列名。如果希望消除客戶與自身相比對的行，則隻需在上述語句中添加一個限定條件，即将上述語句改為：

where (a.客戶位址='北京市海澱區'

or a.客戶位址='北京市朝陽區')

and a.客戶姓名<>b.客戶姓名

該查詢結果和上述查詢結果相比較，發現在該查詢結果中消除了客戶與其自身相比對的行。

6.學習小結

在學習本章節的内容時，很是感到頭疼，這内容也的确是多了點兒。但是，其聯系卻是十分的緊密，學起來不是很費力，總算把學的知識總結起來了。

在對于多個資料表的連接配接學習中，資料表的聯系很是實際化，在不斷練習中學到更多的知識。