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

1、第4節(jié)多 表 查 詢講師：宋紅康 新浪微博：尚硅谷-宋紅康目 標(biāo)通過本章學(xué)習(xí)，您將可以:使用等值和不等值連接在SELECT 語句中查詢多個表中的數(shù)據(jù)。使用自連接。使用外連接查詢不滿足連接條件的數(shù)據(jù)。從多個表中獲取數(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;演示笛卡爾集的錯誤情況：笛卡爾集笛卡爾集會在下面條件下產(chǎn)生:省略連接條件連接條件無效所有表中的所有行互相連接為了避免笛卡爾集， 可以在 WHERE 加入有效的連接條件。笛卡爾集笛卡爾集: 20 x8=160行EMPLOYEES (20行)DEPARTMENTS (8行) Oracle 連接使用連接在多個表中查詢數(shù)據(jù)。在 WHERE 子句中寫入連接條件。在表中有相同列時，在列名之前加上表名前綴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;等值連接多個連接條件與 AND 操作符 EMPLOYEES DEPARTMENTS 區(qū)分重復(fù)的列名使用表

4、名前綴在多個表中區(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;表的別名使用別名可以簡化查詢。使用表名前綴可以提高執(zhí)行效率。連接多個表EMPLOYEES LOCATIONS DEPARTMENTS 連接 n個表,至少需要 n-1個連接條件。 例如：連接三個表，至少需要兩個連接

5、條件。練習(xí)：查詢出公司員工的 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號部門沒有員工 內(nèi)連接和外連接(1)內(nèi)連接: 合并具有同一列

6、的兩個以上的表的行, 結(jié)果集中不包含一個表與另一個表不匹配的行外連接: 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 ，這種連接稱為左（或右） 外連接。沒有匹配的行時, 結(jié)果表中相應(yīng)的列為空(NULL). 外連接的 WHERE 子句條件類似于內(nèi)部連接, 但連接條件中沒有匹配行的表的列后面要加外連接運(yùn)算符, 即用圓括號括起來的加號(+). 外連接語法使用外連接可以查詢不滿足連接條件的數(shù)據(jù)。外連接的符號是 (+)。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í)：查詢出 last_name 為 Chen 的員工的 manager 的信息自連接SELECT worker.last_name | works for | manager.last_nameFROM employees worker, employees managerWHERE worker.manager_id = manager.employee_id ;題目：查詢employees表，返回“Xxx works for Xxx”使用SQL: 1999 語法連接使用連接從多個表中查詢數(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 子句，會以兩個表中具有相同名字的列為條件創(chuàng)建等值連接。在表中查詢滿足等值條件的數(shù)據(jù)。如果只是列名相同而數(shù)據(jù)類型不同，則會產(chǎn)生錯誤。返回的是，兩個表中具有相同名字的列的“且、交集”，而非“或，并集”。即：比如employee類和department類都有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)建等值連接時，可以使用 USING 子句指定等值連接中需要用到的列。使用 USING 可以在有多個列滿足條件時進(jìn)行選擇。不要給選中的列中加上表名前綴或別名。JOIN 和 USING 子句經(jīng)常同時使用。selectlast_name,department_namefromemployeesnaturaljoindepartments;selectlast_name,department_namefromemployeesjoindepartmentsusing(de

12、partment_id);會自動匹配表中相同的列，可多列匹配； SELECT e.employee_id, e.last_name, d.location_idFROM employees e JOIN departments dUSING (department_id) ;USING 子句使用ON 子句創(chuàng)建連接（常用）自然連接中是以具有相同名字的列為連接條件的?？梢允褂?ON 子句指定額外的連接條件。這個連接條件是與其它條件分開的。ON 子句使語句具有更高的易讀性。 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ù)兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行，這種連接稱為左（或右） 外連接。兩個表在連接過程中除了返回滿足連接條件的行以外還返回兩個表中不滿足條件的行 ，這種連接稱為滿 外連接。 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