9.3? 表的連接類型

9.3.1? 自連接

自連接是指表與其自身進行連接，這就需要用到前面介紹的表別名。下面通過一個具體實例來講解自連接的應用。

實例5? 自連接的使用方法

查詢成績中存在不及格課程的學生的姓名、所在系、所有的課程及成績信息。如果采用前面介紹的SELECT語句查詢方法，該實例的實現要分為兩步。

1．查詢存在不及格課程的學生的學號

之所以查詢學號，是因為學號是學生的惟一標識信息。代碼：

SELECT ??DISTINCT? SNO
FROM ??STUDENT
WHERE??MARK<60

運行結果如圖9.5所示。

?

圖9.5? 不及格課程學生的學號

注意?因為可能有的學生的不及格課程多于1門，因此在SELECT子句中使用了DISTINCT關鍵詞，去除重復的學號。

2．根據學號查詢相關信息

根據查詢的學號結果，查詢這些學號的學生的姓名、所在系、所有所修課程及成績信息。代碼：

SELECT ??SNAME, DNAME, CNO, MARK
FROM ??STUDENT
WHERE??SNO IN (‘9702’,’9703’,’9705’)
ORDER BY??SNAME

運行結果如圖9.6所示。

?

圖9.6? 根據學號查詢的相關信息

而如果采用表的自連接，只要通過一步查詢就可以實現該實例。代碼：

SELECT ??DISTINCT S1.SNAME, S1.DNAME, S1.CNO, S1.MARK
FROM ??STUDENT AS S1, STUDENT AS S2
WHERE??S1.SNO = S2.SNO
AND???S2. MARK<60
ORDER BY??S1.SNAME

運行結果如圖9.7所示。

代碼中，FROM子句中的兩個表實際上都是表STUDENT。為了獨立地使用它們，采用上節介紹的表別名方法，分別為其取別名S1和S2。這樣就可以在WHERE子句中，使用S2設定分數查詢條件，而在SELECT子句中，使用S1查詢滿足條件的結果。

需要說明一點，SELECT子句同樣要有關鍵字DISTINCT，如果沒有使用DISTINCT關鍵字，如下面的代碼：

?

圖9.7? 采用表的自連接執行的查詢結果

SELECT ??S1.SNAME, S1.DNAME, S1.CNO, S1.MARK
FROM ??STUDENT AS S1, STUDENT AS S2
WHERE??S1.SNO = S2.SNO
AND???S2. MARK<60
ORDER BY??S1.SNAME

運行結果如圖9.8所示。

?

圖9.8? 采用表的自連接但沒有去除重復記錄的查詢結果

可以發現，結果表中出現了重復的記錄。下面分析一下代碼的執行過程，從中就不難發現重復記錄產生的原因。

系統首先執行FROM子句，將STUDENT表S1與它自身S2的笛卡爾積，作為中間表。實際上，該中間表的每一條記錄包含兩部分信息，一部分是S1的記錄，一部分是S2的記錄。而后執行WHERE子句，在中間表中，搜索S2中成績低于60分的學生的記錄，同時要求記錄中S1與S2是同一個學生的記錄即學號相同。最后執行SELECT語句，從中間表獲取S1中相應的信息作為結果表。

當執行WHERE子句，從中間表中逐條搜索S2中成績低于60分的學生的記錄時，由于孫慶有兩門課程不及格，所以對于每門不及格的記錄都滿足搜索條件，因此導致了從S1得到的信息中出現了重復記錄。

簡單來說，中間表是沒有重復記錄的，但是S1部分字段是有重復的，而結果集提取的只是S1部分的字段，因此就有可能有重復記錄。