第八章-多表連接及子查詢

第8章多表連接及子查詢知識點回顧SELECT語句的基本語法在SELECT子句中執(zhí)行基本的算術運算ORACLE中的偽列使用DISTINCT或UNIQUE關鍵字刪除重復列常用聚合函數及分組子句GROUPBY的使用使用ORDERBY對查詢結果排序2本章目標表連接原理多表連接查詢等值連接非等值連接交叉連接內連接外連接特殊連接子查詢的概念及應用單行子查詢多行子查詢3表連接原理4基本書寫方式1：SQL>select*fromt1,t2[wheret1.id=t2.id];--方括號內為可選基本書寫方式2：SQL>select*fromt1joint2[ont1.id=t2.id];--方括號內為可選SQL>select*fromt1joint2[using(id)];--方括號內為可選表連接原理5表連接原理NESTEDLOOP數據子集較小時使用HASHJOIN兩個巨大的表之間的連接在一個巨大的表和一個小表中的連接SORTMERGEJOIN在行已經被排序的前提下使用為佳6表連接原理7數據查詢語句數據查詢語句是最常用也是最復雜的語句，在介紹查詢語句之前，我們先定義兩個表，以供示例使用：

學生信息表(t_student)：學生編號(s_id)，學生姓名(s_name)、學院編號(c_id)、學生已修課程數(s_source)和學生所在班班長編號(p_id)的表。學院信息表(t_college)：學院編號(c_id)，學院名稱(c_name)和院系平均已修課程數(c_source_avg)。8實驗數據實驗數據t_studentt_college9表連接的分類實際應用場景中，表連接是對一個公共列中存儲了相同類數據的兩個（或多個）表進行關聯的。根據表連接運算符的使用可以分為：等值連接和非等值連接。根據表連接的模式的使用可以分為：內部連接和外部連接。另外還有一些特殊連接：自連接和自然連接。公共列是兩個或更多表中存在相同數據的列，比如，t_student表和t_college表都包含一個c_id的公共列。這里的公共列的列名不必一致，主要是存放的數據要有一定的相關性，甚至是業(yè)務上的同類數據。10等值連接等值連接就是我們進行公共列關聯時，使用的比較運算符使用的是“=”等任意表示相等的運算符。例：查詢學生與院系的關聯信息。11SQL>SELECTt1.s_id,t1.s_name,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id=t2.c_id;非等值連接非等值連接與等值連接最大的區(qū)別就是公共列關聯時使用的比較運算符不是“=”等相等類的運算符而是不等于一類的比較運算符，如：>、<、>=、<=等。這類連接實際應用場景較等值連接來說較為少見，例：查詢一下學生自修課程數大于院系平均自修課程數的學生與院系的相關信息。12SQL>SELECTt1.s_id,t1.s_name,t2.c_name,t1.s_courseFROMt_studentt1,t_colleget2WHEREt1.s_course>t2.c_course_avgANDt1.c_id=t2.c_id;交叉連接交叉連接又稱為“笛卡爾積”連接，表1中的每一個記錄與表2中的每一個記錄配對，這里匹配順序是任意的，用表2去匹配表1與表1匹配表2結果是一致的，只不過索引增加的位置會有不同(詳見下面章節(jié)）如果第一個表中有m條記錄，第二個表中有n條記錄，結果是m*n條記錄13交叉連接基本的交叉連接，下例沒有實際的業(yè)務意義，僅僅展示交叉連接運行效果。SQL>SELECT*FROMt_studentt1,t_colleget2;SQL>SELECT*FROMt_studentt1CROSSJOINt_colleget2;14內部連接內連接主要有三種書寫方式逗號+where子句[inner]join+on子句[inner]join+using子句內連接的兩個表之間是沒有主從關系的，也就是說調整內連接的兩個表的排列順序，對內連接運行結果沒有影響（索引的設置會有影響，詳見下面章節(jié)）。內連接的運行結果的記錄數分別與兩個表沒有直接關系，但是與兩個表之間的數據的組合有一定的關系。只有公共列能完全匹配上的記錄才會輸出到查詢結果中。例：查詢出學生信息與院系信息相關連的基礎信息。15內部連接逗號+where子句連接[inner]join子句+on子句連接[inner]join子句+using子句連接16SQL>SELECTt1.s_id,t1.s_name,t1.c_id,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,t1.c_id,t2.c_nameFROMt_studentt1INNERJOINt_colleget2ONt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1INNERJOINt_colleget2USING(c_id);外部連接外部連接主要有三種書寫方式逗號+where子句[outer]join+on子句[outer]join+using子句外部連接類型：左外連接:LEFT[OUTER]JOIN右外連接：RIGHT[OUTER]JOIN全外連接：FULL[OUTER]JOIN17外部連接-左外連接左外連接(LEFT[OUTER]JOIN)：左外連接的兩個表之間是有主從關系的，左外連接中左表或者說寫在逗號或者join前面的為左表，運行結果中將包含所有左表中的記錄，右表記錄根據匹配關系補充左表信息，如無關聯記錄則置NULL處理（如需加索引，請在右表上與左表關聯的公共列上加索引，詳見下面章節(jié)）。例：查詢出學生信息與院系信息相關連的查詢結果集，要求學生信息是全部的，學院信息作為補充。18外部連接-左外連接逗號+where子句+從表(+)連接left[outer]join子句+on子句連接Left[outer]join子句+using子句連接19SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id=t2.c_id(+);SQL>SELECTi1.u_id,i1.u_age,i1.u_name,i2.u_shortnameFROMinfo1i1LEFTOUTERJOINinfo2i2ONi1.u_id=i2.u_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1LEFTOUTERJOINt_colleget2USING(c_id);外部連接-右外連接右外連接(RIGHT[OUTER]JOIN)：右外連接的兩個表之間是有主從關系的，右外連接中右表或者說寫在逗號或者join后面的為右表，運行結果中將包含所有右表中的記錄，左表記錄根據匹配關系補充右表信息，如無關聯記錄則置NULL處理（如需加索引，請在左表上與右表關聯的公共列上加索引，詳見下面章節(jié)）。例：查詢出學生信息與院系信息相關連的查詢結果集，要求學院信息是全部的，學生信息作為補充。20外部連接-右外連接逗號+where子句+從表(+)連接right[outer]join子句+on子句連接right[outer]join子句+using子句連接21SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1,t_colleget2WHEREt1.c_id(+)=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1RIGHTOUTERJOINt_colleget2ONt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1RIGHTOUTERJOINt_colleget2USING(c_id);外部連接-全外聯接全外連接(RIGHT[OUTER]JOIN)：全外連接的兩個表之間是沒有主從關系的，也就是說調整內連接的兩個表的排列順序，對內連接運行結果沒有影響。內連接的運行結果的記錄數分別與兩個表沒有直接關系，但是與兩個表之間的數據的組合有一定的關系。無論公共列能否完全匹配上的記錄都會輸出到查詢結果中。我們可以簡單的理解為：FULL[OUTER]JOIN相當于LEFT[OUTER]JOIN和RIGHT[OUTER]JOIN查詢的結果集再通過UNION(不是UNIONALL)連接在一起。22外部連接-全外連接full[outer]join子句+on子句連接full[outer]join子句+using子句連接23SQL>SELECTt1.s_id,t1.s_name,t2.c_id,t2.c_nameFROMt_studentt1FULLOUTERJOINt_colleget2ONt1.c_id=t2.c_id;SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1FULLOUTERJOINt_colleget2USING(c_id);特殊連接-自連接自連接是一種特殊的表連接方式。這種連接方式的特殊性在于關聯的兩個表為同一張表自連接可以配合如內連接、外連接、等值連接或非等值連接一起使用。例：查詢出學生和其所在班級的班長的一個組合信息。由于自連接的連接表為相同的表，所有字段都一致，所以在select子句后列舉字段時要清晰的注明字段的來源表。24SQL>SELECTt1.s_id,t1.s_name,t2.s_id,t2.s_nameFROMt_studentt1INNERJOINt_studentt2ONt1.s_pid=t2.s_id;特殊連接-自然連接自然連接是一種特殊的表連接，這種連接方式的特殊性在于關聯的兩個表中有同名字段。自然連接可以配合如內連接、外連接、等值連接或非等值連接一起使用。例：我們再做一遍內連接的例子。由于自然連接是直接使用兩個連接表中同名字段作為關聯關系，所以這個同名字段如果需要在select后列舉時，請一定不要注明字段的來源表。25SQL>SELECTt1.s_id,t1.s_name,c_id,t2.c_nameFROMt_studentt1NATURALINNERJOINt_colleget2;子查詢子查詢是一個嵌套查詢——另一個查詢內部的完整查詢子查詢說明單行子查詢將包含一列的一行結果返回到外部查詢多行子查詢將多行結果返回到外部查詢相關(關聯)子查詢引用外部查詢中的一列，對外部查詢中的每一行執(zhí)行一次子查詢不相關（非關聯）子查詢首先執(zhí)行子查詢，然后將值傳遞給外部查詢26子查詢的使用方式當我們需要一個中間結果集/值時，我們就需要使用子查詢。返回單值的子查詢通常用在WHERE和HAVING子句里。子查詢一般可以出現于限定條件處以及查詢表處的位置：SQL>SELECTfield1,field2,field3FROMtable1WHEREfield1OPERATER(SELECTfield4FROMtable2);SQL>SELECTt.f1,t.f2,t.f3FROM(SELECTt2.*，t3.*FROMt2joint3ont2.f=t3.f)t;。27子查詢及其用法規(guī)則：子查詢必須“自身就是一個完整的查詢”——也就是說至少包括一個SELECT子句和一個FROM子句子查詢不能包括ORDERBY子句。如果顯示輸出需要按照特定順序顯示，那么ORDERBY子句應該作為外部查詢的最后一個子句列出子查詢“必須包括在一組括號中”，以便將它與外部查詢分開。如果將子查詢在外部查詢的WHERE或HAVING子句中，那么該子句推薦放在比較運算符的“右邊”。28單行子查詢單行子查詢就是子查詢中查詢出的結果集只有一條記錄。單行子查詢使用的比較運算符等于：=大于：>大于等于:>=小于:<小于等于:<=不等于:!=例：查詢學院一里的所有學生的全部信息。29SQL>SELECT*FROMt_studentWHEREc_id=(SELECTc_idFROMt_collegeWHEREc_name='學院一');多行子查詢多行子查詢就是子查詢中查詢出的結果集不止一條記錄。多行子查詢使用的運算符inexistsanyAll注意：多行子查詢使用的運算符也可以應用于單行子查詢，但是單行子查詢的運算符不可用于多行子查詢。30多行子查詢-IN運算符in是包含的意思，也就是說將外部查詢條件限定為內部查詢中的所有記錄，限定條件包含在內部查詢結果中。例：查詢一下學院一和學院三中的學生信息SQL>SELECT*FROMt_studentWHEREc_idIN(SELECTc_idFROMt_collegeWHEREc_nameIN('學院一','學院三'));31多行子查詢-EXISTS子句exists為存在的意思，外部查詢中需要的查詢條件在子查詢中存在，或者說子查詢中存在的結果就是外部查詢中需要的條件。例：繼續(xù)使用講解in的例子場景。32SQL>SELECT*FROMt_studentt1WHEREEXISTS(SELECTc_idFROMt_colleget2WHEREc_nameIN('學院一','學院三')ANDt1.c_id=t2.c_id);IN與EXISTS的區(qū)別與聯系IN與EXISTS子查詢的相似點適用于外部查詢中的條件限定部分?？梢栽趇n或exists前配合not關鍵字達到邏輯非的效果。IN與EXISTS子查詢的不同點IN子句外部查詢與子查詢之間的關聯字段信息分別存在于外部查詢與子查詢里。EXISTS子句外部查詢與子查詢之間的關聯字段信息完全存在于子查詢中。IN子句適合外部查詢數據量比較大，子查詢數據量相對較小的模式。而EXISTS子句適合外部查詢數據量較小，子查詢數據量較大的情況。如外部查詢與子查詢數據量接近，那么整體查詢效率也接近。33多行子查詢-ANY和ALL運算符說明>ALL大于子查詢返回的最大值<ALL小于子查詢返回的最小值<ANY小于子查詢返回的最大值>ANY大于子查詢返回的最小值=ANY等于子查詢返回的任何值（于IN相同）34多行子查詢—ANY運算符

any意為任意一個，也就是子查詢查詢結果中的任意一個，這里如果使用“=”進行比較運算，效果等同于IN子句，如果使用“>”進行比較運算，效果等同于大于最小的一個

，大于結果集最小的一個就等于大于結果集任意一個，至少有一個符合。如果用“<”進行運算，效果等同于小于最大的一個，小于最大的一個就等于小于結果集中任意一個，至少有一個符合。例：查詢出任意一個學生所修課程數高于院系平均課程數的學院信息SQL>SELECTc_id,c_nameFROMt_collegeWHEREc_course_avg<ANY(SELECTs_courseFROMt_student);35多行子查詢—ANY運算符

等同于any的另一種寫法。例：繼續(xù)使用any的演示場景外部查詢的“<”和子查詢的MAX函數以及外部查詢的“>”和子查詢的MIN函數的組合都可以模擬與any運算的一個相同的結果。SQL>SELECTc_id,c_nameFROMt_collegeWHEREc_course_avg<(SELECTMAX(s_course)FROMt_student);36多行子查詢