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

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

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

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

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

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