第8章多表連接與子查詢

1、多表連接查詢與子查詢多表連接查詢與子查詢 1 多表連接查詢多表連接查詢1.1 等值連接和非等值連接等值連接和非等值連接1.2 外連接外連接1.3 自連接自連接1.4 SQL99表連接語法表連接語法 2 子查詢子查詢單行子查詢、多行子查詢、子查詢中的空值問題、TopN查詢分析什么是多表連接查詢笛卡爾集Oracle表連接 SQL/Oracle使用表連接從多個(gè)表中查詢數(shù)據(jù) 語法格式：select 字段列表from table1, table2where table1.column1 = table2.column2; 說明：在Where子句中指定連接條件當(dāng)被連接的多個(gè)表中存在同名字段時(shí)，必須在該字段

2、前加上表名.作為前綴。連接的類型Oracle8i之前的表連接：等值連接（ Equijoin ）非等值連接（Non-Equijoin）外連接（Outer join）左外連接右外連接自連接（Self join）Oracle9i新引入的的連接形式（支持SQL99規(guī)范）：交叉連接（Cross join）自然連接（Natural join）使用Using子句建立連接使用On子句建立連接外連接（ Outer join ）左外連接右外連接全外連接等值連接(Equijoin) 什么是等值連接？ select empno, ename, sal, emp.deptno, dname from emp, dept

3、 where emp.deptno = dept.deptno; 多表連接中：可使用AND 操作符增加查詢條件；使用表別名可以簡(jiǎn)化查詢使用表名（表別名）前綴可提高查詢效率；為了連接n個(gè)表，至少需要n-1個(gè)連接條件非等值連接(Non-Equijoin)外連接(Outer join) 使用外連接可以看到參與連接的某一方不滿足連接條件的 記錄。 外連接運(yùn)算符為(+) 傳統(tǒng)的外連接分為左外連接和右外連接兩種 語法格式：select 字段列表from table1, table2where table1.column1(+) = table2.column2;select 字段列表from table1

4、, table2where table1.column1 = table2.column2 (+);自連接(Self join)SQL99連接語法 SQL1999規(guī)范中規(guī)定的連接查詢語法select 字段列表from table1cross join table2 |natural join table2 |join table2 using (字段名) |join table2 on (table1.column_name = table2.column_name) |(left | right | full outer) join table2on (table1.column_name

5、= table2.column_name);交叉連接(Cross join) Cross join產(chǎn)生了一個(gè)笛卡爾集，其效果等同于在兩個(gè)表進(jìn)行連接時(shí)未使用WHERE子句限定連接條件; 舉例：select empno, ename, sal, emp.deptno, dname from emp cross join dept;自然連接(Natural join) Natural join基于兩個(gè)表中的全部同名列建立連接從兩個(gè)表中選出同名列的值均對(duì)應(yīng)相等的所有行如果兩個(gè)表中同名列的數(shù)據(jù)類型不同，則出錯(cuò)不允許在參照列上使用表名或者別名作為前綴 舉例： select empno, ename, sa

6、l, deptno, dnamefrom emp natural join dept;Using子句 如果不希望參照被連接表的所有同名列進(jìn)行等值連接，自然連接將無法滿足要求，可以在連接時(shí)使用USING子句來設(shè)置用于等值連接的列（參照列）名。 舉例：select empno, ename, sal, deptno, dnamefrom emp join deptusing (deptno); 不允許在參照列上使用表名或者別名作為前綴On子句 如果要參照非同名的列進(jìn)行等值連接，或想設(shè)置任意的連接條件，可以使用ON子句。 舉例：select empno, ename, sal, emp.deptno

7、, dname from emp join depton (emp.deptno = dept.deptno);多表連接使用SQL99連接語法，兩個(gè)以上的表進(jìn)行連接時(shí)應(yīng)依次/分別指定相臨的兩個(gè)表之間的連接條件。select 字段列表from table1cross join table2 |natural join table2 |join table2 using (字段名) |join table2 on (table1.column_name = table2.column_name) |(left | right | full outer) join table2on (table1.

8、column_name = table2.column_name)cross join table3 |natural join table3 |join table3 using (字段名) |join table3 on (table2.column_name = table3.column_name) |(left | right | full outer) join table3on (table2.column_name = table3.column_name);內(nèi)連接和外連接內(nèi)連接（Inner Join）在SQL99規(guī)范中，內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。外連接（Outer Jo

9、in）左外聯(lián)接（Left Outer Join ）兩個(gè)表在連接過程中除返回滿足連接條件的行以外，還返回左表中不滿足條件的行，這種連接稱為左外聯(lián)接。右外聯(lián)接（Right Outer Join）兩個(gè)表在連接過程中除返回滿足連接條件的行以外，還返回右表中不滿足條件的行，這種連接稱為右外聯(lián)接。滿外聯(lián)接（Full Outer Join）Oracle9i開始新增功能，兩個(gè)表在連接過程中除返回滿足連接條件的行以外，還返回兩個(gè)表中不滿足條件的所有行，這種連接稱為滿外聯(lián)接。子查詢(Sub Query) 問題引入如何查得所有比張三工資高的員工的信息 子查詢子查詢?cè)谥鞑樵兦皥?zhí)行一次主查詢使用子查詢的結(jié)果語法格式：s

10、elect 字段列表 from tablewhere 表達(dá)式operator (select 字段列表from table);子查詢的種類使用子查詢注意事項(xiàng) 在查詢是基于未知值時(shí)應(yīng)考慮使用子查詢 子查詢必須包含在括號(hào)內(nèi) 建議將子查詢放在比較運(yùn)算符的右側(cè)，以增強(qiáng)可讀性。 除非進(jìn)行Top-N 分析，否則不要在子查詢中使用ORDER BY 子句。 對(duì)單行子查詢使用單行運(yùn)算符 對(duì)多行子查詢使用多行運(yùn)算符單行子查詢 單行子查詢只返回一行記錄 對(duì)單行子查詢可使用單行記錄比較運(yùn)算符 舉例：select * from empwhere sal (select sal from emp where empno

11、= 7566);子查詢空值/多值問題 如果子查詢未返回任何行，則主查詢也不會(huì)返回任何結(jié)果select * from empwhere sal (select sal from emp where empno = 8888); 如果子查詢返回單行結(jié)果，則為單行子查詢，可以在主查詢中對(duì)其使用相應(yīng)的單行記錄比較運(yùn)算符select * from empwhere sal (select sal from emp where empno = 7566); 如果子查詢返回多行結(jié)果，則為多行子查詢，此時(shí)不允許對(duì)其使用單行記錄比較運(yùn)算符select * from empwhere sal (select av

12、g(sal) from emp group by deptno); /非法多行子查詢 多行子查詢返回多行記錄 對(duì)多行子查詢只能使用多行記錄比較運(yùn)算符 舉例：select * from empwhere sal any(select avg(sal) from emp group by deptno);select * from empwhere sal all(select avg(sal) from emp group by deptno);select * from empwhere job in (select job from emp where ename = MARTIN or ename