第8章連接查詢與集合查詢

1、第第8 8章章 連接查詢與集合查詢連接查詢與集合查詢在對數(shù)據(jù)庫的查詢過程中，有些時候檢索一張表中的在對數(shù)據(jù)庫的查詢過程中，有些時候檢索一張表中的數(shù)據(jù)記錄往往不能滿足開發(fā)人員或者客戶的需要。例如，查數(shù)據(jù)記錄往往不能滿足開發(fā)人員或者客戶的需要。例如，查詢學(xué)生的選課成績信息。而學(xué)生選課信息和課程成績信息分詢學(xué)生的選課成績信息。而學(xué)生選課信息和課程成績信息分別在兩個不同的數(shù)據(jù)表中。其中，在課程信息表（別在兩個不同的數(shù)據(jù)表中。其中，在課程信息表（T_curriculum）中包括課程的編號、課程的名字、課程的學(xué)）中包括課程的編號、課程的名字、課程的學(xué)分、課時以及授課的教師等學(xué)生選課信息，而學(xué)生的編號、分、

2、課時以及授課的教師等學(xué)生選課信息，而學(xué)生的編號、選課的課程編號以及課程成績等信息在成績信息表（選課的課程編號以及課程成績等信息在成績信息表（T_result）中，此時為了在查詢的結(jié)果中顯示學(xué)生選課信息）中，此時為了在查詢的結(jié)果中顯示學(xué)生選課信息和所選課程的相關(guān)信息，就需要同時檢索課程信息表（和所選課程的相關(guān)信息，就需要同時檢索課程信息表（ T_curriculum）和成績信息表（）和成績信息表（ T_result）。這就需要進(jìn)行）。這就需要進(jìn)行連接查詢的操作。連接查詢的操作。第第8 8章章 連接查詢與集合查詢連接查詢與集合查詢連接查詢主要包括內(nèi)連接、交叉連接、自連接、以及連接查詢主要包括內(nèi)連接

3、、交叉連接、自連接、以及外連接查詢四種類型。本章主要介紹這四種連接查詢的使用外連接查詢四種類型。本章主要介紹這四種連接查詢的使用方法，在本章的最后還將介紹三種主要的集合查詢。方法，在本章的最后還將介紹三種主要的集合查詢。等值連接和非等值連接查詢的使用方法等值連接和非等值連接查詢的使用方法使用使用ON子句建立相等連接子句建立相等連接使用使用USING子句建立相等連接子句建立相等連接交叉連接的使用方法交叉連接的使用方法自連接查詢的使用方法自連接查詢的使用方法外連接查詢的使用方法外連接查詢的使用方法集合查詢的使用方法集合查詢的使用方法8.1 8.1 內(nèi)連接查詢內(nèi)連接查詢很多時候，需要將幾個表連接起來

4、并查詢滿足條件的很多時候，需要將幾個表連接起來并查詢滿足條件的記錄，這個時候就需要使用內(nèi)連接。內(nèi)連接包括等值連接和記錄，這個時候就需要使用內(nèi)連接。內(nèi)連接包括等值連接和非等值連接。除了使用非等值連接。除了使用WHERE子句中的等號運(yùn)算符（子句中的等號運(yùn)算符（=）建立等值連接外，還可以使用建立等值連接外，還可以使用ON子句和子句和USING子句建立建子句建立建立等值連接。這一節(jié)將介紹幾種內(nèi)連接的查詢方法。立等值連接。這一節(jié)將介紹幾種內(nèi)連接的查詢方法。8.1.1 8.1.1 等值連接等值連接等值連接是指將指定的連接條件通過使用等號運(yùn)算符等值連接是指將指定的連接條件通過使用等號運(yùn)算符（=）連接起來，并

5、返回符合連接條件的數(shù)據(jù)行。其語法格）連接起來，并返回符合連接條件的數(shù)據(jù)行。其語法格式如下：式如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1,表名表名2WHERE 表名表名1.字段字段1=表名表名2.字段字段2其中，其中，SELECT 語句中表名語句中表名1.字段和表名字段和表名2.字段表示指字段表示指定數(shù)據(jù)表定數(shù)據(jù)表1和數(shù)據(jù)表和數(shù)據(jù)表2中要查詢的列；中要查詢的列；FROM 語句中表名語句中表名1和表名和表名2表示指定連接的數(shù)據(jù)表的名字；表示指定連接的數(shù)據(jù)表的名字；WHERE子句中表子句中表名名1.字段字段1=表名表名2.字段字段2表示用于指定連接條件的

6、列。這里的表示用于指定連接條件的列。這里的字段字段1中的列和字段中的列和字段2中的列必須是兩個表之間相互關(guān)聯(lián)的列中的列必須是兩個表之間相互關(guān)聯(lián)的列。8.1.2 8.1.2 非等值連接非等值連接非等值連接是指使用除等號運(yùn)算符（非等值連接是指使用除等號運(yùn)算符（=）以外的其他運(yùn)）以外的其他運(yùn)算符將指定條件連接起來而執(zhí)行的查詢操作。其他運(yùn)算符包算符將指定條件連接起來而執(zhí)行的查詢操作。其他運(yùn)算符包括、括、=（大于等于）、（大于等于）、（大于）、（大于）、（小于）、小于）、!=（不等于）等，還可以使用（不等于）等，還可以使用BETWEENAND運(yùn)運(yùn)算符。算符。8.1.3 8.1.3 使用使用ONON子句建

7、立相等連接子句建立相等連接在在SQL語句中除了語句中除了WHERE子句中使用等號運(yùn)算符（子句中使用等號運(yùn)算符（=）實現(xiàn)等值連接的操作之外，還可以使用）實現(xiàn)等值連接的操作之外，還可以使用ON子句建立相等子句建立相等連接條件。其語法規(guī)則如下：連接條件。其語法規(guī)則如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 JOIN 表名表名2ON表名表名1.字段字段1=表名表名2.字段字段其中關(guān)鍵字其中關(guān)鍵字JOIN表示將表表示將表1和表和表2連接起來，連接起來，ON子句用子句用來指定連接條件的列。這里的字段來指定連接條件的列。這里的字段1中的列和字段中的列和字段2中的

8、列必中的列必須是兩個表之間相互關(guān)聯(lián)的列。須是兩個表之間相互關(guān)聯(lián)的列。8.1.4 8.1.4 使用使用USINGUSING子句建立相等連接子句建立相等連接在進(jìn)行連接操作時，有時只希望將兩張表中相互關(guān)聯(lián)在進(jìn)行連接操作時，有時只希望將兩張表中相互關(guān)聯(lián)的列建立一個等值連接。此時，可以使用的列建立一個等值連接。此時，可以使用USING子句建立相子句建立相等連接來簡化使用等號運(yùn)算符（等連接來簡化使用等號運(yùn)算符（=）建立的等值連接操作。）建立的等值連接操作。其語法規(guī)范如下：其語法規(guī)范如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 JOIN 表名表名2USING (字

9、段字段1)其中關(guān)鍵字其中關(guān)鍵字JOIN表示將表表示將表1和表和表2連接起來，連接起來，USING子子句中使用括號將字段句中使用括號將字段1括起來，字段括起來，字段1就是兩個表中建立等值就是兩個表中建立等值連接相互關(guān)聯(lián)的列。連接相互關(guān)聯(lián)的列。8.2 8.2 交叉連接交叉連接交叉連接返回的結(jié)果是一個笛卡爾積。所謂笛卡爾積交叉連接返回的結(jié)果是一個笛卡爾積。所謂笛卡爾積實際就是兩個集合相乘的結(jié)果。假設(shè)集合實際就是兩個集合相乘的結(jié)果。假設(shè)集合A中有中有n個元素，個元素，集合集合B中有中有m個元素，如果最后返回的結(jié)果是個元素，如果最后返回的結(jié)果是n*m，那么這，那么這個結(jié)果就是集合個結(jié)果就是集合A和集合和

10、集合B的笛卡爾積。的笛卡爾積。8.3 8.3 自連接查詢自連接查詢前面講到的連接都是在表與表之間進(jìn)行的。連接查詢前面講到的連接都是在表與表之間進(jìn)行的。連接查詢除了可以在不同的表之間進(jìn)行，也可以對同一張表實現(xiàn)連接除了可以在不同的表之間進(jìn)行，也可以對同一張表實現(xiàn)連接操作，這種連接查詢的方式稱為自連接。所謂自連接，就是操作，這種連接查詢的方式稱為自連接。所謂自連接，就是指一個數(shù)據(jù)表與其自身進(jìn)行連接。其語法規(guī)則如下：指一個數(shù)據(jù)表與其自身進(jìn)行連接。其語法規(guī)則如下：SELECT A.字段字段, A.字段字段 .FROM 表名表名1 A,表名表名1 BWHERE A.字段字段=B.字段字段由于連接的是同一張

11、表，所以在由于連接的是同一張表，所以在FROM語句中需要為語句中需要為表定義不同的別名，這里為表表定義不同的別名，這里為表1分別定義了表的別名為分別定義了表的別名為A和和B。SELECT語句中可以使用語句中可以使用A.字段的形式也可以使用字段的形式也可以使用B.字段字段的形式查詢需要的記錄。這里的的形式查詢需要的記錄。這里的SELECT 語句中使用的是語句中使用的是A.字段的形式。字段的形式。8.3 8.3 自連接查詢自連接查詢例如，在課程信息表中選擇學(xué)分?jǐn)?shù)比操作系統(tǒng)的學(xué)分例如，在課程信息表中選擇學(xué)分?jǐn)?shù)比操作系統(tǒng)的學(xué)分?jǐn)?shù)多的課程信息。數(shù)多的課程信息。SELECT C2.curID,C2.cur

12、Name,C2.creditFROM T_curriculum C1,T_curriculum C2WHERE C1.curName = 操作系統(tǒng)操作系統(tǒng) AND C1.creditC2.credit8.4 8.4 外連接查詢外連接查詢在前面講述的連接操作中，返回的結(jié)果都是滿足連接在前面講述的連接操作中，返回的結(jié)果都是滿足連接條件的記錄。有些時候，開發(fā)人員或者用戶對于不滿足連接條件的記錄。有些時候，開發(fā)人員或者用戶對于不滿足連接條件的部分記錄也感興趣，這個時候就需要使用外連接查詢條件的部分記錄也感興趣，這個時候就需要使用外連接查詢。外連接查詢不僅可以返回滿足連接條件的記錄，對于一個外連接查詢不

13、僅可以返回滿足連接條件的記錄，對于一個數(shù)據(jù)表中在另一個數(shù)據(jù)表中不匹配的記錄也可以返回。外連數(shù)據(jù)表中在另一個數(shù)據(jù)表中不匹配的記錄也可以返回。外連接查詢主要包括三種：左外連接、右外連接和全外連接。這接查詢主要包括三種：左外連接、右外連接和全外連接。這一節(jié)將介紹這三種連接在不同數(shù)據(jù)庫中的使用方法。一節(jié)將介紹這三種連接在不同數(shù)據(jù)庫中的使用方法。8.4.1 8.4.1 左外連接左外連接左外連接中查詢的結(jié)果中不僅將顯示滿足連接條件的左外連接中查詢的結(jié)果中不僅將顯示滿足連接條件的記錄，而且還包括左側(cè)表中不滿足查詢條件的記錄。下面分記錄，而且還包括左側(cè)表中不滿足查詢條件的記錄。下面分別介紹在別介紹在Oracl

14、e數(shù)據(jù)庫、數(shù)據(jù)庫、MySQL數(shù)據(jù)庫和數(shù)據(jù)庫和Microsoft SQL Server數(shù)據(jù)庫中左外連接的使用方法。數(shù)據(jù)庫中左外連接的使用方法。1Oracle數(shù)據(jù)庫數(shù)據(jù)庫2MySQL和和Microsoft SQL Server數(shù)據(jù)庫數(shù)據(jù)庫8.4.1 8.4.1 左外連接左外連接1Oracle數(shù)據(jù)庫數(shù)據(jù)庫在在Oracle數(shù)據(jù)庫中，可以使用加號運(yùn)算符（數(shù)據(jù)庫中，可以使用加號運(yùn)算符（+）來表示）來表示左外連接。當(dāng)該加號運(yùn)算符（左外連接。當(dāng)該加號運(yùn)算符（+）出現(xiàn)在連接條件的左邊時）出現(xiàn)在連接條件的左邊時，就稱之為左外連接。其語法格式如下：，就稱之為左外連接。其語法格式如下：SELECT 表名表名1.字段字

15、段, 表名表名2.字段字段 .FROM 表名表名1,表名表名2WHERE 表名表名1.字段字段1(+)=表名表名2.字段字段2其中，其中，SELECT 語句中表名語句中表名1.字段和表名字段和表名2.字段表示指字段表示指定數(shù)據(jù)表定數(shù)據(jù)表1和數(shù)據(jù)表和數(shù)據(jù)表2中要查詢的列；中要查詢的列；FROM 語句中表名語句中表名1和表名和表名2表示指定連接的數(shù)據(jù)表的名字；表示指定連接的數(shù)據(jù)表的名字；WHERE子句中表子句中表名名1.字段字段1(+)=表名表名2.字段字段2表示左外連接。此時，表名表示左外連接。此時，表名1.字字段段1所在的列的值將會被全部查詢出來。所在的列的值將會被全部查詢出來。8.4.1 8

16、.4.1 左外連接左外連接2. MySQL和和Microsoft SQL Server數(shù)據(jù)庫數(shù)據(jù)庫在在MySQL數(shù)據(jù)庫和數(shù)據(jù)庫和Microsoft SQL Server數(shù)據(jù)庫中可數(shù)據(jù)庫中可以使用以使用LEFTOUTER JOIN關(guān)鍵字實現(xiàn)，其中關(guān)鍵字實現(xiàn)，其中OUTER關(guān)鍵關(guān)鍵字是可選的。使用字是可選的。使用LEFTOUTER JOIN關(guān)鍵字實現(xiàn)左外連關(guān)鍵字實現(xiàn)左外連接的語法規(guī)則如下：接的語法規(guī)則如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 LEFT JOIN表名表名2ON 表名表名1.字段字段1=表名表名2.字段字段2這里使用這里使用LEFT JO

17、IN關(guān)鍵字代替關(guān)鍵字代替SQL語句中語句中FROM語語句里的逗號，使用句里的逗號，使用ON子句代替標(biāo)準(zhǔn)子句代替標(biāo)準(zhǔn)SQL語句中的語句中的WHERE子句，并將子句，并將SQL語句中表示左外連接的加號運(yùn)算符（語句中表示左外連接的加號運(yùn)算符（+）去）去除。除。 8.4.2 8.4.2 右外連接右外連接右外連接中查詢的結(jié)果中不僅將顯示滿足連接條件的右外連接中查詢的結(jié)果中不僅將顯示滿足連接條件的記錄，而且還包括右側(cè)表中不滿足查詢條件的記錄。下面分記錄，而且還包括右側(cè)表中不滿足查詢條件的記錄。下面分別介紹在別介紹在Oracle數(shù)據(jù)庫、數(shù)據(jù)庫、MySQL數(shù)據(jù)庫和數(shù)據(jù)庫和Microsoft SQL Serve

18、r數(shù)據(jù)庫中右外連接的使用方法。數(shù)據(jù)庫中右外連接的使用方法。1Oracle數(shù)據(jù)庫數(shù)據(jù)庫2MySQL和和Microsoft SQL Server數(shù)據(jù)庫數(shù)據(jù)庫8.4.2 8.4.2 右外連接右外連接1. Oracle數(shù)據(jù)庫數(shù)據(jù)庫在在Oracle數(shù)據(jù)庫中，當(dāng)該加號運(yùn)算符（數(shù)據(jù)庫中，當(dāng)該加號運(yùn)算符（+）出現(xiàn)在連接）出現(xiàn)在連接條件的右邊時，就稱之為右外連接。其語法格式如下：條件的右邊時，就稱之為右外連接。其語法格式如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1,表名表名2WHERE 表名表名1.字段字段1=表名表名2.字段字段2(+)其中，其中，SELECT 語句

19、中表名語句中表名1.字段和表名字段和表名2.字段表示指字段表示指定數(shù)據(jù)表定數(shù)據(jù)表1和數(shù)據(jù)表和數(shù)據(jù)表2中要查詢的列；中要查詢的列；FROM 語句中表名語句中表名1和表名和表名2表示指定連接的數(shù)據(jù)表的名字；表示指定連接的數(shù)據(jù)表的名字；WHERE子句中表子句中表名名1.字段字段1=表名表名2.字段字段2(+)表示左外連接。此時，表名表示左外連接。此時，表名2.字字段段2所在的列的值將會被全部查詢出來。所在的列的值將會被全部查詢出來。8.4.2 8.4.2 右外連接右外連接2. MySQL和和Microsoft SQL Server數(shù)據(jù)庫數(shù)據(jù)庫在在MySQL和和Microsoft SQL Server

20、數(shù)據(jù)庫中可以使用數(shù)據(jù)庫中可以使用RIGHT OUTER JOIN關(guān)鍵字實現(xiàn)，其中關(guān)鍵字實現(xiàn)，其中OUTER關(guān)鍵字是關(guān)鍵字是可選的。使用可選的。使用RIGHT OUTER JOIN關(guān)鍵字實現(xiàn)左外連接的關(guān)鍵字實現(xiàn)左外連接的語法規(guī)則如下：語法規(guī)則如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 RIGHT JOIN表名表名2ON 表名表名1.字段字段1=表名表名2.字段字段2這里使用這里使用RIGHT JOIN關(guān)鍵字代替關(guān)鍵字代替SQL語句中語句中FROM語語句里的逗號，使用句里的逗號，使用ON子句代替子句代替SQL語句中的語句中的WHERE子句子句，并將標(biāo)準(zhǔn)

21、，并將標(biāo)準(zhǔn)SQL語句中表示右外連接的加號運(yùn)算符（語句中表示右外連接的加號運(yùn)算符（+）去）去除。除。8.4.2 8.4.2 全外連接全外連接全外連接中查詢的結(jié)果中不僅將顯示左側(cè)表中不滿足全外連接中查詢的結(jié)果中不僅將顯示左側(cè)表中不滿足連接條件的記錄，而且還會顯示右側(cè)表中不滿足查詢條件的連接條件的記錄，而且還會顯示右側(cè)表中不滿足查詢條件的記錄。全外連接可以認(rèn)為是左外連接與右外連接的合集（不記錄。全外連接可以認(rèn)為是左外連接與右外連接的合集（不包括重復(fù)行）。包括重復(fù)行）。全外連接可以使用全外連接可以使用FULL OUTER JOIN關(guān)鍵字實現(xiàn)，關(guān)鍵字實現(xiàn)，其中其中OUTER關(guān)鍵字是可選的。使用關(guān)鍵字是可

22、選的。使用FULL OUTER JOIN關(guān)鍵字實現(xiàn)左外連接的語法規(guī)則如下：關(guān)鍵字實現(xiàn)左外連接的語法規(guī)則如下：SELECT 表名表名1.字段字段, 表名表名2.字段字段 .FROM 表名表名1 FULL JOIN表名表名2ON 表名表名1.字段字段1=表名表名2.字段字段2這里使用這里使用FULL JOIN關(guān)鍵字代替關(guān)鍵字代替SQL語句中語句中FROM語語句里的逗號，使用句里的逗號，使用ON子句代替子句代替SQL語句中的語句中的WHERE子句子句8.5 8.5 集合查詢集合查詢在在SQL的連接查詢語句中，還有一種查詢方式就是結(jié)的連接查詢語句中，還有一種查詢方式就是結(jié)合查詢。集合查詢主要包括三種：

23、并操作、交操作和差操作合查詢。集合查詢主要包括三種：并操作、交操作和差操作。其中交操作和差操作并不是對目前主流的所有的數(shù)據(jù)庫的。其中交操作和差操作并不是對目前主流的所有的數(shù)據(jù)庫的適用。這一節(jié)中將分別介紹這三種集合的操作方法。適用。這一節(jié)中將分別介紹這三種集合的操作方法。8.5.1 8.5.1 并操作（并操作（UNIONUNION）執(zhí)行并操作使用的關(guān)鍵字是執(zhí)行并操作使用的關(guān)鍵字是UNION。并操作返回的結(jié)。并操作返回的結(jié)果集是包括了兩個查詢語句中查詢出來的所有不同的行，不果集是包括了兩個查詢語句中查詢出來的所有不同的行，不包含重復(fù)行。其語法格式如下：包含重復(fù)行。其語法格式如下：SELECT 語句

24、語句1UNIONSELECT 語句語句2其中語句其中語句1和語句和語句2表示的是兩個用于查詢的表示的是兩個用于查詢的SELECT語語句。句。UNION關(guān)鍵字表示對這兩個查詢語句查詢出來的結(jié)果關(guān)鍵字表示對這兩個查詢語句查詢出來的結(jié)果進(jìn)行并操作。這里需要保證進(jìn)行并操作。這里需要保證SELECT 語句語句1和和SELECT 語句語句2中查詢出的列數(shù)必須相同，而且對應(yīng)的列的數(shù)據(jù)類型必須中查詢出的列數(shù)必須相同，而且對應(yīng)的列的數(shù)據(jù)類型必須一致。一致。8.5.2 8.5.2 交操作（交操作（INTERSECTINTERSECT）執(zhí)行交操作使用的關(guān)鍵字是執(zhí)行交操作使用的關(guān)鍵字是INTERSECT。交操作返回。

25、交操作返回的結(jié)果集包括了連接查詢結(jié)果的公共行。交操作中不會出現(xiàn)的結(jié)果集包括了連接查詢結(jié)果的公共行。交操作中不會出現(xiàn)重復(fù)行。其語法格式如下：重復(fù)行。其語法格式如下：SELECT 語句語句1INTERSECTSELECT 語句語句2其中語句其中語句1和語句和語句2表示的是兩個用于查詢的表示的是兩個用于查詢的SELECT語語句。句。INTERSECT關(guān)鍵字表示對這兩個查詢語句查詢出來的關(guān)鍵字表示對這兩個查詢語句查詢出來的結(jié)果進(jìn)行交操作。這里需要保證結(jié)果進(jìn)行交操作。這里需要保證SELECT 語句語句1和和SELECT 語句語句2中查詢出的列數(shù)必須相同，而且對應(yīng)的列的數(shù)據(jù)類型中查詢出的列數(shù)必須相同，而且

26、對應(yīng)的列的數(shù)據(jù)類型必須一致。必須一致。8.5.2 8.5.2 交操作（交操作（INTERSECTINTERSECT）例如，有兩個表例如，有兩個表A和表和表B，表，表A中有三個列，分別是中有三個列，分別是A1,A2和和A3，表，表B中也包含三個列，分別是中也包含三個列，分別是B1,B2和和B3。這。這里假設(shè)字段里假設(shè)字段A1和字段和字段B1有相同的數(shù)據(jù)類型，字段有相同的數(shù)據(jù)類型，字段A2和字段和字段B2有相同的數(shù)據(jù)類型，字段有相同的數(shù)據(jù)類型，字段A3和字段和字段B3有相同的數(shù)據(jù)類型有相同的數(shù)據(jù)類型?，F(xiàn)在對這兩張表執(zhí)行交操作?，F(xiàn)在對這兩張表執(zhí)行交操作。 8.5.2 8.5.2 交操作（交操作（INTERSECTINTERSECT）對這兩張表中的記錄進(jìn)行交操作。這里表對這兩張表中的記錄進(jìn)行交操作。這里表A1查詢的列查詢的列為列為列A1，A2和和A3。這表。這表B1查詢的列為列查詢的列為列B1，B2和和B3。使。使用用INTERSECT關(guān)鍵字對查詢結(jié)果進(jìn)行交操作。根據(jù)交操作關(guān)鍵字對查詢結(jié)果進(jìn)行交操作。根據(jù)交操作的運(yùn)算規(guī)則，執(zhí)行的運(yùn)算規(guī)則，執(zhí)行A交交B的運(yùn)算之后，其執(zhí)行的結(jié)果如下圖的運(yùn)算之后，其執(zhí)行的結(jié)果如下圖所示。所示。8.5.3 8.5.3 差操作（差操作（MINUSMINUS）執(zhí)執(zhí)行交操作使用的關(guān)鍵字是執(zhí)執(zhí)行交操作使用的關(guān)鍵字是MINUS。差操作返回