SQL實用簡明教程-第7章-多表連接查詢課件

第7章多表連接查詢重點內(nèi)容：SQL連接語法連接的處理步驟連接的類型第7章多表連接查詢1SELECTselect_listFROMTable1name[CROSS|NATURAL]JOINTable2name[ON[Table1name.]columnoperator[Table2name.]column][WHEREconditions]一、連接的語法

SQL中為連接提供了關(guān)鍵字JOIN、CROSSJOIN及NATURALJOIN，用于連接操作。第一行中的select_list仍然是選擇的列的名稱，但是需要注意的是，如果使用兩個表中有相同名稱的列，則必須限定是哪個表中的列；第二行中關(guān)鍵字CROSS與NATURAL是可選項，可以使用也可以不使用。如果選擇了CROSS或NATURAL關(guān)鍵字，那么就不能使用ON關(guān)鍵字；單獨使用JOIN關(guān)鍵字時，需要使用ON關(guān)鍵字來設(shè)定連接的條件；使用CROSSJOIN時，不能使用關(guān)鍵字ON，因此必須使用WHERE子句設(shè)定連接的條件。因為如果不設(shè)定連接條件，將返回表之間的每一種可能組合(即笛卡爾積)，連接結(jié)果將非常龐大；NATURALJOIN只有在兩個表有相同名稱的列時才能使用，將在同名列上進行連接。因此，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當(dāng)然，使用WHERE子句限制查找的行仍然是可以的。SELECTselect_list一、連接的語法第一行2二、使用連接的原因

連接是關(guān)系模型的關(guān)鍵操作，因為表之間的數(shù)據(jù)并不是互不相關(guān)的，而是存在一定的聯(lián)系的，數(shù)據(jù)庫中僅僅知道各表單獨的數(shù)據(jù)是不足夠的。這時就需要通過連接來發(fā)現(xiàn)數(shù)據(jù)之間的關(guān)系，找出數(shù)據(jù)之間的聯(lián)系，而不僅僅局限于數(shù)據(jù)庫中已有的一些關(guān)系。使用連接還有一個好處：連接對于結(jié)果沒有特別的限制，具有很大的靈活性。通常可以通過連接創(chuàng)建包含不同表中數(shù)據(jù)的新表或者視圖。這就使得數(shù)據(jù)庫的擴展具有很大的靈活性。二、使用連接的原因3三、處理連接的方法1、笛卡兒積

有的時候，由于連接條件設(shè)置的不好，甚至沒有設(shè)置連接條件，結(jié)果將包含太多的行。這是由于笛卡爾積造成的。從概念上說，連接首先將形成表的笛卡爾積，即形成用于連接的表中所有的行的組合。用于連接的表越多，其笛卡爾積的結(jié)果將越大，越需要很好的指定連接條件。當(dāng)然，在一些特殊情況下，如一個表中只包含一個記錄時，可以不指定連接條件。因為這時的笛卡爾積是另外一個表中記錄的個數(shù)。進行連接操作時，都是先形成表之間的笛卡爾積，獲得笛卡爾積之后再使用指定的連接條件及WHERE子句中其它的限制條件對形成的笛卡爾積進行刪除，保留符合條件的記錄。正確的指定連接條件和限制條件將有助于產(chǎn)生良好的連接結(jié)果。當(dāng)返回的結(jié)果過多時，請注意檢查連接條件是否合適。此外，由于連接過程中使用了笛卡爾積，因此連接操作會導(dǎo)致性能的下降。因此，在使用時最好遵循以下一些原則：（1）用于連接的列已經(jīng)創(chuàng)建了索引。因為索引會單獨保存在磁盤上，且將數(shù)據(jù)按照一定順序進行了排列，索引的使用可以加快訪問的速度；（2）用于連接的列具有相同的數(shù)據(jù)類型，包括是否允許空值。如果需要系統(tǒng)自動進行類型轉(zhuǎn)換是需要花費較多時間的，特別是在表中記錄很多時，類型轉(zhuǎn)換所花費的時間將會很多.

三、處理連接的方法4三、處理連接的方法2、連接中運算符的使用

在進行連接時，可以使用前面介紹的運算符指定連接的條件。最常見的是使用等號進行連接，也可以使用大于或小于等其它運算符進行連接操作。此外，在進行連接時，還可以使用WHERE子句限制查找的行。3、多表連接在FROM子句中添加表名，在連接條件中設(shè)定新的連接條件即可實現(xiàn)多表連接。在多表連接時也可以使用JOIN關(guān)鍵字，不過用起來比較繁瑣，不如FROM/WHERE來的簡便。多表連接一個很重要的應(yīng)用就是結(jié)合連接表進行使用。

三、處理連接的方法5三、處理連接的方法4、連接結(jié)果的處理限制返回的列

當(dāng)使用連接來連接多個表時，一般來說都不會使用“*”來選擇所有的列，而是在SELECT列表中列出需要返回的列。如果使用“*”包含所有的列，那么顯示時列將按照創(chuàng)建表時指定的順序顯示。

刪除重復(fù)

返回連接結(jié)果時，不一定要顯示用于連接的列。需要顯示時，也應(yīng)該注意不要出現(xiàn)重復(fù)。

使用別名在進行連接時，也可以為表或列創(chuàng)建別名以方便使用或使顯示結(jié)果更加清晰。特別是表中有相同名稱的列時，別名的使用可以使代碼更易于輸入，且更易于閱讀。

空值的處理如果連接表的連接列中存在NULL時，NULL將不會參與連接，即使兩個表的連接列都存在NULL，連接結(jié)果也不會存在NULL。因為，空值代表的是未知、丟失或不可用的值，所以連接時不會對空值進行連接。

三、處理連接的方法限制返回的列當(dāng)使用連接來連接多個表時，一6三、處理連接的方法5、連接的處理步驟 處理、生成查詢結(jié)果有很多步驟。使用連接操作時的處理過程是比較復(fù)雜的，下面是包含連接操作時的處理步驟：(1)為連接表生成笛卡爾積。這是連接操作引起性能下降的主要原因。笛卡爾積是連接表包含的所有行之間的組合。當(dāng)用于連接的表中記錄較多時，形成的笛卡爾積將非常的大，形成笛卡爾積花費的時間也將更多。(2)使用WHERE子句從形成的笛卡爾積中去除所有不符合限制條件的行。這時SELECT子句還沒有進行執(zhí)行，因此，連接表形成的笛卡爾積中包含了用于連接的表的所有列。(3)使用WHERE子句進行刪除后，如果語句中包含了GROUPBY子句，將按照GROUPBY子句中指定的列對剩下的行進行分組。雖然，這時SELECT子句還沒有執(zhí)行，但是如果SELECT列表中包含了聚集函數(shù)將會對分好的組進行計算。(4)如果語句中使用了HAVING子句，將使用其設(shè)定的條件對分組結(jié)果進行刪除，不符合條件的組將會被刪除。(5)SELECT子句將被應(yīng)用到余下的數(shù)據(jù)中，表達式也被執(zhí)行，選出在SELECT列表中的列，不在其內(nèi)的列將會被刪除。如果有ORDERBY子句就將結(jié)果按照ORDERBY子句指定的列進行排序顯示出來。

三、處理連接的方法7四、連接的類型1、一般的連接

一般的連接通常通過兩個表中的某個特定的列進行連接。最通常使用的是相等連接，當(dāng)然也可以使用大于連接、小于連接等。SELECTname,city,pnumber,accepterFROMEmployeee,PaccepteraWHEREe.city=a.cityANDpno=pnumber

Resultsnamecitypnumberaccepter——————————————————龐文凱杭州301科華貿(mào)易公司張峰杭州301科華貿(mào)易公司林雪飛廈門104神州發(fā)展有限公司梁朝陽杭州301科華貿(mào)易公司林國榮天津105四海貿(mào)易有限公司王力剛天津105四海貿(mào)易有限公司李大平北京204CESA.COMPANY魏華翔北京204東勝有限公司姜艷成北京204東勝有限公司[9rows]四、連接的類型SELECTname,city,pnum8四、連接的類型2、自然連接

自然連接是一種特殊的連接。自然連接只有在兩個表有相同名稱的列且列的涵義相似時才能使用，將在同名列上進行相等連接。這也就是SQL標(biāo)準中定義的NATURALJOIN關(guān)鍵字。使用NATURALJOIN關(guān)鍵字時，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當(dāng)然，使用WHERE子句限制查找的行仍然是可以的。使用傳統(tǒng)的FROM/WHERE子句，則需要在WHERE子句中指定連接列的相等條件。

SELECTpnumber,accepter,pmgridFROMProjectpNATURALJOINPacceptera

SELECTpnumber,accepter,pmgridFROMProjectp,PaccepteraWHEREp.pnumber=a.pnumberResultspnumberaccepterpmgrid———————————————101藍科通訊公司1002102華夏技術(shù)有限公司1001103漢升咨詢公司1001104神州發(fā)展有限公司1002105四海貿(mào)易有限公司1004201化天通訊公司4003202日勝公司4004203華田汽修公司4003204東勝有限公司4003301科華貿(mào)易公司4001302新安有限公司4001303華田汽修公司1001[12rows]四、連接的類型SELECTpnumber,accepte9四、連接的類型3、自連接

自連接用于單個表內(nèi)進行比較。使用自連接時，需要注意兩點：一是由于是將表與其自身相連接，因此必須對表設(shè)定別名；二是自連接時會將行與其自身相連，因此必須設(shè)定條件來防止。SELECTDISTINCTa1.accepter,a1.city,a2.accepter,a2.cityFROMPacceptera1,Pacceptera2WHEREa1.city=a2.cityANDa1.accepter<>a2.accepterANDa1.cityIN(’杭州’,’北京’)

Resultsacceptercityacceptercity———————————————————藍科通訊公司杭州漢升咨詢公司杭州藍科通訊公司杭州科華貿(mào)易公司杭州漢升咨詢公司杭州藍科通訊公司杭州漢升咨詢公司杭州科華貿(mào)易公司杭州科華貿(mào)易公司杭州藍科通訊公司杭州科華貿(mào)易公司杭州漢升咨詢公司杭州化天通訊公司北京華田汽修公司北京化天通訊公司北京東勝有限公司北京華田汽修公司北京東勝有限公司北京華田汽修公司北京化天通訊公司北京東勝有限公司北京化天通訊公司北京東勝有限公司北京華田汽修公司北京[12rows]四、連接的類型SELECTDISTINCTa1.acce10四、連接的類型4、外連接

有時候用戶可能希望顯示某個表中所有記錄，包括不符合連接條件的記錄，這正是外連接的作用。使用外連接可以方便的在連接結(jié)果中包含某個表中的其它記錄。SQL中提供了兩個關(guān)鍵字：LEFTOUTERJOIN與RIGHTOUTERJOIN。LEFTOUTERJOIN關(guān)鍵字使外連接顯示LEFTOUTERJOIN左側(cè)的表包含的所有記錄。而RIGHTOUTERJOIN關(guān)鍵字則使外連接顯示RIGHTOUTERJOIN右側(cè)的表所包含的所有記錄。四、連接的類型11五、對集合的支持1、UNON運算

UNOIN運算是集合運算中應(yīng)用的最多的，得到了廣泛的支持。UNION運算可以從多個表中組合數(shù)據(jù)進行顯示，但是與連接不同，UNION不是在FROM子句中添加多個表并指定連接條件實現(xiàn)，而是通過將多個查詢的結(jié)果結(jié)合到一起實現(xiàn)的。SELECTselect_listFROMtable_list/view_list[WHEREconditions]UNION[ALL]SELECTselect_listFROMtable_list/view_list[WHEREconditions]五、對集合的支持SELECTselect_list12五、對集合的支持2、INTERSECT運算

INTERSECT運算與UNION運算相似，UNION運算中應(yīng)該注意的問題、提高結(jié)果可讀性的方法都可以在INTERSECT運算中使用。INTERSECT運算將返回多個查詢結(jié)果中相同的行。SELECTcityFROMEmployeeWHEREdno=2INTERSECTSELECTDISTINCTcityFROMPaccepter

Resultscity————北京上海廈門[3rows]五、對集合的支持SELECTcityResults13五、對集合的支持3、MINUS運算

MINUS返回在一個查詢結(jié)果中存在，且不在另一個查詢結(jié)果中存在的所有行，即兩個查詢的差集。如果在第二個查詢結(jié)果中也存在相同的行，MINUS返回的結(jié)果中將不包含這些行。SELECTcityFROMPaccepterMINUSSELECTcityFROMEmployeeWHEREdno=2

Resultscity————北京杭州天津[3rows]五、對集合的支持SELECTcityResults14本章小結(jié)：掌握SQL中連接的語法和處理連接的方法掌握連接的各種類型掌握SQL所支持的集合運算本章小結(jié)：15第7章多表連接查詢重點內(nèi)容：SQL連接語法連接的處理步驟連接的類型第7章多表連接查詢16SELECTselect_listFROMTable1name[CROSS|NATURAL]JOINTable2name[ON[Table1name.]columnoperator[Table2name.]column][WHEREconditions]一、連接的語法

SQL中為連接提供了關(guān)鍵字JOIN、CROSSJOIN及NATURALJOIN，用于連接操作。第一行中的select_list仍然是選擇的列的名稱，但是需要注意的是，如果使用兩個表中有相同名稱的列，則必須限定是哪個表中的列；第二行中關(guān)鍵字CROSS與NATURAL是可選項，可以使用也可以不使用。如果選擇了CROSS或NATURAL關(guān)鍵字，那么就不能使用ON關(guān)鍵字；單獨使用JOIN關(guān)鍵字時，需要使用ON關(guān)鍵字來設(shè)定連接的條件；使用CROSSJOIN時，不能使用關(guān)鍵字ON，因此必須使用WHERE子句設(shè)定連接的條件。因為如果不設(shè)定連接條件，將返回表之間的每一種可能組合(即笛卡爾積)，連接結(jié)果將非常龐大；NATURALJOIN只有在兩個表有相同名稱的列時才能使用，將在同名列上進行連接。因此，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當(dāng)然，使用WHERE子句限制查找的行仍然是可以的。SELECTselect_list一、連接的語法第一行17二、使用連接的原因

連接是關(guān)系模型的關(guān)鍵操作，因為表之間的數(shù)據(jù)并不是互不相關(guān)的，而是存在一定的聯(lián)系的，數(shù)據(jù)庫中僅僅知道各表單獨的數(shù)據(jù)是不足夠的。這時就需要通過連接來發(fā)現(xiàn)數(shù)據(jù)之間的關(guān)系，找出數(shù)據(jù)之間的聯(lián)系，而不僅僅局限于數(shù)據(jù)庫中已有的一些關(guān)系。使用連接還有一個好處：連接對于結(jié)果沒有特別的限制，具有很大的靈活性。通?？梢酝ㄟ^連接創(chuàng)建包含不同表中數(shù)據(jù)的新表或者視圖。這就使得數(shù)據(jù)庫的擴展具有很大的靈活性。二、使用連接的原因18三、處理連接的方法1、笛卡兒積

有的時候，由于連接條件設(shè)置的不好，甚至沒有設(shè)置連接條件，結(jié)果將包含太多的行。這是由于笛卡爾積造成的。從概念上說，連接首先將形成表的笛卡爾積，即形成用于連接的表中所有的行的組合。用于連接的表越多，其笛卡爾積的結(jié)果將越大，越需要很好的指定連接條件。當(dāng)然，在一些特殊情況下，如一個表中只包含一個記錄時，可以不指定連接條件。因為這時的笛卡爾積是另外一個表中記錄的個數(shù)。進行連接操作時，都是先形成表之間的笛卡爾積，獲得笛卡爾積之后再使用指定的連接條件及WHERE子句中其它的限制條件對形成的笛卡爾積進行刪除，保留符合條件的記錄。正確的指定連接條件和限制條件將有助于產(chǎn)生良好的連接結(jié)果。當(dāng)返回的結(jié)果過多時，請注意檢查連接條件是否合適。此外，由于連接過程中使用了笛卡爾積，因此連接操作會導(dǎo)致性能的下降。因此，在使用時最好遵循以下一些原則：（1）用于連接的列已經(jīng)創(chuàng)建了索引。因為索引會單獨保存在磁盤上，且將數(shù)據(jù)按照一定順序進行了排列，索引的使用可以加快訪問的速度；（2）用于連接的列具有相同的數(shù)據(jù)類型，包括是否允許空值。如果需要系統(tǒng)自動進行類型轉(zhuǎn)換是需要花費較多時間的，特別是在表中記錄很多時，類型轉(zhuǎn)換所花費的時間將會很多.

三、處理連接的方法19三、處理連接的方法2、連接中運算符的使用

在進行連接時，可以使用前面介紹的運算符指定連接的條件。最常見的是使用等號進行連接，也可以使用大于或小于等其它運算符進行連接操作。此外，在進行連接時，還可以使用WHERE子句限制查找的行。3、多表連接在FROM子句中添加表名，在連接條件中設(shè)定新的連接條件即可實現(xiàn)多表連接。在多表連接時也可以使用JOIN關(guān)鍵字，不過用起來比較繁瑣，不如FROM/WHERE來的簡便。多表連接一個很重要的應(yīng)用就是結(jié)合連接表進行使用。

三、處理連接的方法20三、處理連接的方法4、連接結(jié)果的處理限制返回的列

當(dāng)使用連接來連接多個表時，一般來說都不會使用“*”來選擇所有的列，而是在SELECT列表中列出需要返回的列。如果使用“*”包含所有的列，那么顯示時列將按照創(chuàng)建表時指定的順序顯示。

刪除重復(fù)

返回連接結(jié)果時，不一定要顯示用于連接的列。需要顯示時，也應(yīng)該注意不要出現(xiàn)重復(fù)。

使用別名在進行連接時，也可以為表或列創(chuàng)建別名以方便使用或使顯示結(jié)果更加清晰。特別是表中有相同名稱的列時，別名的使用可以使代碼更易于輸入，且更易于閱讀。

空值的處理如果連接表的連接列中存在NULL時，NULL將不會參與連接，即使兩個表的連接列都存在NULL，連接結(jié)果也不會存在NULL。因為，空值代表的是未知、丟失或不可用的值，所以連接時不會對空值進行連接。

三、處理連接的方法限制返回的列當(dāng)使用連接來連接多個表時，一21三、處理連接的方法5、連接的處理步驟 處理、生成查詢結(jié)果有很多步驟。使用連接操作時的處理過程是比較復(fù)雜的，下面是包含連接操作時的處理步驟：(1)為連接表生成笛卡爾積。這是連接操作引起性能下降的主要原因。笛卡爾積是連接表包含的所有行之間的組合。當(dāng)用于連接的表中記錄較多時，形成的笛卡爾積將非常的大，形成笛卡爾積花費的時間也將更多。(2)使用WHERE子句從形成的笛卡爾積中去除所有不符合限制條件的行。這時SELECT子句還沒有進行執(zhí)行，因此，連接表形成的笛卡爾積中包含了用于連接的表的所有列。(3)使用WHERE子句進行刪除后，如果語句中包含了GROUPBY子句，將按照GROUPBY子句中指定的列對剩下的行進行分組。雖然，這時SELECT子句還沒有執(zhí)行，但是如果SELECT列表中包含了聚集函數(shù)將會對分好的組進行計算。(4)如果語句中使用了HAVING子句，將使用其設(shè)定的條件對分組結(jié)果進行刪除，不符合條件的組將會被刪除。(5)SELECT子句將被應(yīng)用到余下的數(shù)據(jù)中，表達式也被執(zhí)行，選出在SELECT列表中的列，不在其內(nèi)的列將會被刪除。如果有ORDERBY子句就將結(jié)果按照ORDERBY子句指定的列進行排序顯示出來。

三、處理連接的方法22四、連接的類型1、一般的連接

一般的連接通常通過兩個表中的某個特定的列進行連接。最通常使用的是相等連接，當(dāng)然也可以使用大于連接、小于連接等。SELECTname,city,pnumber,accepterFROMEmployeee,PaccepteraWHEREe.city=a.cityANDpno=pnumber

Resultsnamecitypnumberaccepter——————————————————龐文凱杭州301科華貿(mào)易公司張峰杭州301科華貿(mào)易公司林雪飛廈門104神州發(fā)展有限公司梁朝陽杭州301科華貿(mào)易公司林國榮天津105四海貿(mào)易有限公司王力剛天津105四海貿(mào)易有限公司李大平北京204CESA.COMPANY魏華翔北京204東勝有限公司姜艷成北京204東勝有限公司[9rows]四、連接的類型SELECTname,city,pnum23四、連接的類型2、自然連接

自然連接是一種特殊的連接。自然連接只有在兩個表有相同名稱的列且列的涵義相似時才能使用，將在同名列上進行相等連接。這也就是SQL標(biāo)準中定義的NATURALJOIN關(guān)鍵字。使用NATURALJOIN關(guān)鍵字時，不必使用關(guān)鍵字ON或WHERE子句設(shè)定連接條件。當(dāng)然，使用WHERE子句限制查找的行仍然是可以的。使用傳統(tǒng)的FROM/WHERE子句，則需要在WHERE子句中指定連接列的相等條件。

SELECTpnumber,accepter,pmgridFROMProjectpNATURALJOINPacceptera

SELECTpnumber,accepter,pmgridFROMProjectp,PaccepteraWHEREp.pnumber=a.pnumberResultspnumberaccepterpmgrid———————————————101藍科通訊公司1002102華夏技術(shù)有限公司1001103漢升咨詢公司1001104神州發(fā)展有限公司1002105四海貿(mào)易有限公司1004201化天通訊公司4003202日勝公司4004203華田汽修公司4003204東勝有限公司4003301科華貿(mào)易公司4001302新安有限公司4001303華田汽修公司1001[12rows]四、連接的類型SELECTpnumber,accepte24四、連接的類型3、自連接

自連接用于單個表內(nèi)進行比較。使用自連接時，需要注意兩點：一是由于是將表與其自身相連接，因此必須對表設(shè)定別名；二是自連接時會將行與其自身相連，因此必須設(shè)定條件來防止。SELECTDISTINCTa1.accepter,a1.city,a2.accepter,a2.cityFROMPacceptera1,Pacceptera2WHEREa1.city=a2.cityANDa1.accepter<>a2.accepterANDa1.cityIN(’杭州’,’北京’)

Resultsacceptercityacceptercity———————————————————藍科通訊公司杭州漢升咨詢公司杭州藍科通訊公司杭州科華貿(mào)易公司杭州漢升咨詢公司杭州藍科通訊公司杭州漢升咨詢公司杭州科華貿(mào)易公司杭州科華貿(mào)易公司杭州藍科通訊公司杭州科華貿(mào)易公司杭州漢升咨詢公司杭州化天通訊公司北京華田汽修公司北京化天通訊公司北京東勝有限公司北京華田汽修公司北京東勝有限公司北京華田汽修公司北京化天通訊公司北京東勝有限公司北京化天通訊公司北京東勝有限公司北京