《oracle教學(xué)課件》尚硅谷-宋紅康-04-多表查詢(xún)

1、第4節(jié)多 表 查 詢(xún)講師：宋紅康 新浪微博：尚硅谷-宋紅康目 標(biāo)通過(guò)本章學(xué)習(xí)，您將可以:使用等值和不等值連接在SELECT 語(yǔ)句中查詢(xún)多個(gè)表中的數(shù)據(jù)。使用自連接。使用外連接查詢(xún)不滿足連接條件的數(shù)據(jù)。從多個(gè)表中獲取數(shù)據(jù)EMPLOYEES DEPARTMENTS select last_name, department_namefrom employees, departmentsselect count(employee_id) from employees;假設(shè)輸出107行select count(department_id)from departments;假設(shè)輸出27行select 107

2、*27 from dual;演示笛卡爾集的錯(cuò)誤情況：笛卡爾集笛卡爾集會(huì)在下面條件下產(chǎn)生:省略連接條件連接條件無(wú)效所有表中的所有行互相連接為了避免笛卡爾集， 可以在 WHERE 加入有效的連接條件。笛卡爾集笛卡爾集: 20 x8=160行EMPLOYEES (20行)DEPARTMENTS (8行) Oracle 連接使用連接在多個(gè)表中查詢(xún)數(shù)據(jù)。在 WHERE 子句中寫(xiě)入連接條件。在表中有相同列時(shí)，在列名之前加上表名前綴SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column1 = table2.column2;

3、等值連接EMPLOYEES DEPARTMENTS 外鍵主鍵：唯一、非空 SELECT employees.employee_id, employees.last_name, employees.department_id, departments.department_id, departments.location_idFROM employees, departmentsWHERE employees.department_id = departments.department_id;等值連接多個(gè)連接條件與 AND 操作符 EMPLOYEES DEPARTMENTS 區(qū)分重復(fù)的列名使用表

4、名前綴在多個(gè)表中區(qū)分相同的列。在不同表中具有相同列名的列可以用表的別名加以區(qū)分。SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e , departments dWHERE e.department_id = d.department_id;表的別名使用別名可以簡(jiǎn)化查詢(xún)。使用表名前綴可以提高執(zhí)行效率。連接多個(gè)表EMPLOYEES LOCATIONS DEPARTMENTS 連接 n個(gè)表,至少需要 n-1個(gè)連接條件。 例如：連接三個(gè)表，至少需要兩個(gè)連接

5、條件。練習(xí)：查詢(xún)出公司員工的 last_name, department_name, city非等值連接EMPLOYEESJOB_GRADESEMPLOYEES表中的列工資應(yīng)在JOB_GRADES表中的最高工資與最低工資之間非等值連接SELECT e.last_name, e.salary, j.grade_levelFROM employees e, job_grades jWHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;外連接EMPLOYEESDEPARTMENTS190號(hào)部門(mén)沒(méi)有員工 內(nèi)連接和外連接(1)內(nèi)連接: 合并具有同一列

6、的兩個(gè)以上的表的行, 結(jié)果集中不包含一個(gè)表與另一個(gè)表不匹配的行外連接: 兩個(gè)表在連接過(guò)程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱(chēng)為左（或右） 外連接。沒(méi)有匹配的行時(shí), 結(jié)果表中相應(yīng)的列為空(NULL). 外連接的 WHERE 子句條件類(lèi)似于內(nèi)部連接, 但連接條件中沒(méi)有匹配行的表的列后面要加外連接運(yùn)算符, 即用圓括號(hào)括起來(lái)的加號(hào)(+). 外連接語(yǔ)法使用外連接可以查詢(xún)不滿足連接條件的數(shù)據(jù)。外連接的符號(hào)是 (+)。SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column(+) =

7、 table2.column;SELECTtable1.column, table2.columnFROMtable1, table2WHEREtable1.column = table2.column(+);右外連接左外連接SELECT e.last_name, e.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id(+) = d.department_id ; 外連接自連接EMPLOYEES (WORKER)EMPLOYEES (MANAGER)WORKER 表中的MANA

8、GER_ID 和 MANAGER 表中的EMPLOYEE_ID相等練習(xí)：查詢(xún)出 last_name 為 Chen 的員工的 manager 的信息自連接SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;題目：查詢(xún)employees表，返回“Xxx works for Xxx”使用SQL: 1999 語(yǔ)法連接使用連接從多個(gè)表中查詢(xún)數(shù)據(jù)：SELECTtable1.co

9、lumn, table2.columnFROMtable1CROSS JOIN table2 |NATURAL JOIN table2 |JOIN table2 USING (column_name) |JOIN table2 ON(table1.column_name = table2.column_name) |LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name);叉 集（了解）使用CROSS JOIN 子句使連接的表產(chǎn)生叉集。叉集和笛卡爾集是相同的。 SELECT last_name,

10、 department_nameFROM employeesCROSS JOIN departments ;自然連接NATURAL JOIN 子句，會(huì)以?xún)蓚€(gè)表中具有相同名字的列為條件創(chuàng)建等值連接。在表中查詢(xún)滿足等值條件的數(shù)據(jù)。如果只是列名相同而數(shù)據(jù)類(lèi)型不同，則會(huì)產(chǎn)生錯(cuò)誤。返回的是，兩個(gè)表中具有相同名字的列的“且、交集”，而非“或，并集”。即：比如employee類(lèi)和department類(lèi)都有department_id和manager_id，返回二者都相同的結(jié)果。 SELECT department_id, department_name, location_id, cityFROM depar

11、tmentsNATURAL JOIN locations ;自然連接使用 USING 子句創(chuàng)建連接在NATURAL JOIN 子句創(chuàng)建等值連接時(shí)，可以使用 USING 子句指定等值連接中需要用到的列。使用 USING 可以在有多個(gè)列滿足條件時(shí)進(jìn)行選擇。不要給選中的列中加上表名前綴或別名。JOIN 和 USING 子句經(jīng)常同時(shí)使用。selectlast_name,department_namefromemployeesnaturaljoindepartments;selectlast_name,department_namefromemployeesjoindepartmentsusing(de

12、partment_id);會(huì)自動(dòng)匹配表中相同的列，可多列匹配； SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;USING 子句使用ON 子句創(chuàng)建連接（常用）自然連接中是以具有相同名字的列為連接條件的。可以使用 ON 子句指定額外的連接條件。這個(gè)連接條件是與其它條件分開(kāi)的。ON 子句使語(yǔ)句具有更高的易讀性。 SELECT e.employee_id, e.last_name, e.department_id, d.departmen

13、t_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);ON 子句 使用 ON 子句創(chuàng)建多表連接SELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.department_id JOIN locations lON d.location_id = l.location_id;內(nèi)連接和外連接(2)在SQL: 1999中，內(nèi)連接

14、只返回滿足連接條件的數(shù)據(jù)兩個(gè)表在連接過(guò)程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行，這種連接稱(chēng)為左（或右） 外連接。兩個(gè)表在連接過(guò)程中除了返回滿足連接條件的行以外還返回兩個(gè)表中不滿足條件的行 ，這種連接稱(chēng)為滿 外連接。 SELECT e.last_name, e.department_id, d.department_nameFROM employees eLEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;左外連接 SELECT e.last_name, e.department_id, d.department_nameFROM employees eRIGHT OUTER JOIN department