1、Displaying Datafrom Multiple Tables Using Joins多表語句Objectives課程目標After completing this lesson, you should be able to do the following:完成本課，你應該學到Write SELECT statements to access data from more than one table using equijoins and nonequijoins等值與不等值連接Join a table to itself by using a self-join自連接View d

2、ata that generally does not meet a join condition by using OUTER joins外連接Generate a Cartesian product of all rows from two or more tables兩個及以上表的笛卡爾乘積6 - 2Lesson Agenda課程進度Types of JOINS and its syntax連接種類與語法Natural join:USING clauseON clauseSelf-join NonequijoinsOUTER join:LEFT OUTERjoin RIGHT OUTER

3、joinFULL OUTERjoinCartesian productCross join6 - 3Obtaining Data from Multiple Tables從多個表獲取數據EMPLOYEESDEPARTMENTS6 - 4Types of Joins連接類型Joins that are compliant with the SQL:1999 standard include the following:SQL1999標準連接語法Natural joins:自然連接JOINclauseNATURALUSING clauseON clauseOUTER joins:外連接LEFTRI

4、GHT FULLOUTER OUTEROUTERJOIN JOINJOINCross joins交叉連接6 - 5Joining Tables Using SQL:1999 Syntax連接語法Use a join to query data from more than one table:6 - 6SELECTtable1.column, table2.columnFROMtable1NATURAL JOIN table2 |JOIN table2 USING (column_name) | JOIN table2ON (table1.column_name = table2.column

5、_name)| LEFT|RIGHT|FULL OUTER JOIN table2ON (table1.column_name = table2.column_name)| CROSS JOIN table2;Qualifying Ambiguous Column Names限定模糊列名Use table prefixes to qualify column names that are in multiple tables.使用表前綴限制列名Use table prefixes to improve performance.使用前綴可提高性能Instead of full table nam

6、e prefixes, use table aliases.表別名可替代完整的表前綴Table alias gives a table a shorter name:表別名給表一個簡短的名字Keeps SQL code smaller, uses less memory使代碼更短，使用更少內存Use column aliases to distinguish columns that have identical names, but reside in different tables. 列別名區分不同列，但在不同表里6 - 7Lesson Agenda課程進度Types of JOINS

7、and its syntax Natural joinUSING clauseON clauseSelf-join NonequijoinsOUTER join:LEFT OUTERjoin RIGHT OUTERjoinFULL OUTERjoinCartesian productCross join6 - 8Creating Natural Joins創建自然連接The NATURALJOINclause is based on all the columns inthe two tables that have the same name.自然連接基于連個表的所有列有相同的名字It se

8、lects rows from the two tables that have equal values in all matched columns.從兩個表選取所有匹配列的相同值If the columns having the same names have different data types, an error is returned.如果同名列數據類型不匹配就會返回錯誤6 - 9Retrieving Records with Natural Joins Natural Joins例子6 - 10SELECT department_id, department_name, lo

9、cation_id, cityFROMdepartmentsNATURAL JOIN locations;Creating Joins with the USING Clause USING語句If several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.如果個別列有相同名字但數據類型不同可用USING語句指定等值連接的列Use the USING clause to match only o

10、ne column when more than one column matches.有多列時Using只匹配一列The NATURALexclusive.JOINand USING clauses are mutuallyNATURAL JOIN與 USING是互斥的6 - 11Joining Column Names連接列DEPARTMENTSEMPLOYEESPrimary keyForeign key6 - 12Retrieving Records with the USING Clause using例子6 - 13SELECT employee_id, last_name, lo

11、cation_id, department_idFROMemployees JOIN departmentsUSING (department_id);Using Table Aliases with the USING Clause表別名Do not qualify a column that is used in the USING clause.If the same column is used elsewhere in the SQL statement, do not alias it.6 - 14SELECT l.city, d.department_nameFROMlocati

12、ons l JOIN departments d USING (location_id)WHERE d.location_id = 1400;Creating Joins with the ON Clause ON語句The join condition for the natural join is basically an equijoin of all columns with the same name. natural連接的條件基本上是同名列的等值連接Use the ON clause to specify arbitrary conditions or specify column

13、s to join.為了指定任一條件或指定列用于連接可以用ON子句The join condition is separated from other search conditions.連接條件是與其他的條件的The ON clause makes code easy to understand. on子句更加容易理解6 - 15Retrieving Records with the ON Clause ON例子6 - 16SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFRO

14、Memployees e JOIN departments dON(e.department_id = d.department_id);Creating Three-Way Joins with the ON Clause三表連接join on例子6 - 17SELECT employee_id, city, department_name FROMemployees eJOINdepartments dONd.department_id = e.department_id JOINlocations lONd.location_id = l.location_id;Applying Add

15、itional Conditions to a Join連接附加條件Use the AND clause or the WHERE clause to apply additional conditions:where語句或and語句可附加在連接語法里Or6 - 18SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROMemployees e JOIN departments dON(e.department_id = d.department_id)WHEREe.manage

16、r_id = 149;SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROMemployees e JOIN departments dd.department_id) ANDe.manager_id = 149 ;ON(e.department_id =Lesson Agenda課程進度Types of JOINS and its syntax Natural join:USING clauseON clauseSelf-join自連接NonequijoinsOUTER jo

17、in:LEFT OUTERjoin RIGHT OUTERjoinFULL OUTERjoinCartesian productCross join6 - 19Joining a Table to Itself自連接EMPLOYEES(WORKER)EMPLOYEES(MANAGER)MANAGER_ID in the WORKER table is equal toEMPLOYEE_ID in the MANAGER table.6 - 20Self-Joins Using the ON Clause自連接用ON6 - 21SELECT worker.last_name emp, manag

18、er.last_name mgr FROMemployees worker JOIN employees managerON(worker.manager_id = manager.employee_id);Lesson Agenda課程進度Types of JOINS and its syntax Natural join:USING clauseON clauseSelf-join Nonequijoins不等連接OUTER join:LEFT OUTERjoin RIGHT OUTERjoinFULL OUTERjoinCartesian productCross join6 - 22N

19、onequijoins不等連接EMPLOYEESJOB_GRADESThe JOB_GRADES table defines the LOWEST_SAL and HIGHEST_SAL range of values for each GRADE_LEVEL. Therefore, the GRADE_LEVEL column can be used to assign grades to each employee.6 - 23Retrieving Records with Nonequijoins不等連接例子6 - 24SELECT e.last_name, e.salary, j.gr

20、ade_level FROMemployees e JOIN job_grades jONe.salaryBETWEEN j.lowest_sal AND j.highest_sal;Lesson Agenda課程進度Types of JOINS and its syntax Natural join:USING clauseON clauseSelf-join Nonequijoins OUTER join:外連接OUTERjoinOUTERjoin OUTERjoinLEFTRIGHT FULLCartesian productCross join6 - 25Returning Recor

21、ds with No Direct Match Using OUTER Joins不匹配的用外連接Equijoin with EMPLOYEESDEPARTMENTSThere are no employees in department 190.Employee “Grant” has not been assigned a department ID.6 - 26INNER Versus OUTER Joins內連接與外連接In SQL:1999, the join of two tables returning only matched rows is called an INNER j

22、oin.在SQL:1999只返回兩個表中匹配條件行的連接，是內連接A join between two tables that returns the results of the INNER join as well as the unmatched rows from the left (or right) table is called a left (or right) OUTER join.兩個表的內連接，返回行包含在左右表中不匹配的行，就是左右外連接A join between two tables that returns the results of an INNER join

23、 as well as the results of a left and right join is a full OUTER join.兩個表的內連接，返回行包含左表和右表中不匹配的行，就是完全外連接6 - 27LEFT OUTER JOIN左外連接6 - 28SELECT e.last_name, e.department_id, d.department_name FROMemployees edepartments dON(e.department_id = d.department_id) ;LEFT OUTER JOINRIGHT OUTER JOIN右外連接6 - 29SELE

24、CT e.last_name, d.department_id, d.department_name FROMemployees edepartments dON(e.department_id = d.department_id) ;RIGHT OUTER JOINFULL OUTER JOIN全外連接6 - 30SELECT e.last_name, d.department_id, d.department_name FROMemployees edepartments dON(e.department_id = d.department_id) ;FULL OUTER JOINLess

25、on Agenda課程進度Types of JOINS and its syntax Natural join:USING clauseON clauseSelf-join NonequiijoinOUTER join:LEFT OUTERjoin RIGHT OUTERjoinFULL OUTERjoinCartesian product 笛卡爾乘積Cross join 交叉連接6 - 31Cartesian Products笛卡爾乘積A Cartesian product is formed when:A join condition is omitted連接條件省略A join cond

26、ition is invalid連接條件無效All rows in the first table are joined to all rows in the second table一個表所有行連接到另一個表所有行Always include a valid join condition if you want to avoid a Cartesian product.要避免笛卡爾乘積你需要定義有效的連接條件6 - 32Generating a Cartesian Product迪卡爾乘積EMPLOYEES (20 rows)DEPARTMENTS (8 rows)Cartesian pro

27、duct:20 x 8 = 160 rows6 - 33Creating Cross Joins交叉連接JOINclause produces the cross-product ofThe CROSStwo tables.This is also called a Cartesian product between the two tables.6 - 34SELECT last_name, department_name FROMemployees;CROSS JOIN departmentsQuiz課堂測試The SQL:1999 standard join syntax support

28、s the following types of joins. Which of these join types does Oracle join syntax support?下列的SQL99語法里面，oracle的語法支持的有1.2.3.4.5.6.7.8.Equijoins Nonequijoins Left OUTER join Right OUTER join Full OUTER join Self joins Natural joinsCartesian products6 - 35Summary小結In this lesson, you should have learned

29、 how to use joins to display data from multiple tables by using:本課中你已學到如下的多表語句Equijoins Nonequijoins OUTER joins Self-joins Cross joins Natural joinsFull (or two-sided) OUTER joins6 - 36Practice 6: OverviewThis practice covers the following topics:Joining tables using an equijoin Performing outer an

30、d self-joinsAdding conditions6 - 371. Write a query for the HR department to produce the addresses of all the departments.Use the LOCATIONS and COUNTRIES tables.Show the location ID, street address,city, state or province, and country in the output. Use a NATURAL JOIN to produce the results.2. The H

31、R department needs a report of all employees. Write a query to display the last name, department number, and department name for all the employees.3. The HR department needs a report of employees in Toronto. Display the last name,job, department number, and department name for all employees who work

32、 in Toronto4. Create a report to display employees last names and employee number along with their managers last names and manager number. Label the columns Employee,Emp#, Manager, and Mgr#, respectively. Save your SQL statement as lab_06_04.sql. Run the query6 - 385. Modify lab_06_04.sql to display

33、 all employees including King, who has no manager. Order the results by the employee number. Saveyour SQLas lab_06_05.sql. Run the query in lab_06_05.sql.6. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same departm

34、ent as a given employee. Give each column an appropriate label. Save the script to a file named lab_06_06.sql.7. The HR department needs a report on job grades and salaries. To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that

35、displays the name, job, department name, salary, and grade for allemployees.8. The hr department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies.6 - 399. The HR department needs to

36、 find the names and hire dates for all employees who were hired before their managers, along with their managers names and hire dates. Save the script to a file named lab_06_09.sql.6 - 406 - 416 - 421.Write a query for the HR department to produce the addresses of all the departments.Use the LOCATIO

37、NS and COUNTRIES tables.Show the location ID, street address,city, state or province, and country in the output. Use a NATURAL JOIN to produce the results.SQL>SELECT location_id, street_address, city,state_province,country_name FROM locationsNATURAL JOIN countries;6 - 432. The HR department needs

38、 a report of all employees. Write a query to display the last name, department number, and department name for all the employees.SQL>SELECT last_name, department_id, department_name FROM employeesJOIN departmentsUSING (department_id);6 - 443. The HR department needs a report of employees in Toron

39、to. Display the last name,job, department number, and department name for all employees who work in TorontoSQL>SELECT e.last_name, e.job_id, e.department_id,d.department_name FROM employees e JOIN departments dON (e.department_id = d.department_id) JOIN locations lON (d.location_id = l.location_i

40、d)WHERE LOWER(l.city) = 'toronto'6 - 454. Create a report to display employees last names and employee number along with their managers last names and manager number. Label the columns Employee,Emp#, Manager, and Mgr#, respectively. Save your SQL statement as lab_06_04.sql. Run the querySQL&

41、gt;SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"FROM employees w join employees mON (w.manager_id = m.employee_id);6 - 465. Modify lab_06_04.sql to display all employees including King, who has no manager. Ord

42、er the results by the employee number. Saveyour SQLas lab_06_05.sql. Run the query in lab_06_05.sql.SQL>SELECT w.last_name "Employee", w.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"FROM employees wLEFT OUTER JOIN employees mON (w.manager_

43、id = m.employee_id) ORDER BY 2;6 - 476. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Save the script to a file named lab_06_06.sql.SQL>SELECT e.department_id department, e.last_name employee, c.last