講義分析講稿les

1、Displaying Data from Multiple Tables Using JoinsObjectivesAfter 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 nonequijoinsJoin a table to itself by using a self-joinView data that generally does not

2、meet a join condition by using OUTER joinsGenerate a Cartesian product of all rows from two or more tablesLesson AgendaTypes of JOINS and its syntaxNatural join:USING clauseON clauseSelf-joinNonequijoinsOUTER join:LEFT OUTER joinRIGHT OUTER joinFULL OUTER joinCartesian productCross joinObtaining Dat

3、a from Multiple TablesEMPLOYEES DEPARTMENTS Types of JoinsJoins that are compliant with the SQL:1999 standard include the following:Natural joins:NATURAL JOIN clauseUSING clauseON clauseOUTER joins:LEFT OUTER JOINRIGHT OUTER JOINFULL OUTER JOINCross joinsJoining Tables Using SQL:1999 SyntaxUse a joi

4、n to query data from more than one table:SELECTtable1.column, table2.columnFROMtable1NATURAL 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 table2;Qualify

5、ing Ambiguous Column NamesUse table prefixes to qualify column names that are in multiple tables.Use table prefixes to improve performance.Instead of full table name prefixes, use table aliases.Table alias gives a table a shorter name:Keeps SQL code smaller, uses less memoryUse column aliases to dis

6、tinguish columns that have identical names, but reside in different tables.Lesson AgendaTypes of JOINS and its syntaxNatural join:USING clauseON clauseSelf-joinNonequijoinsOUTER join:LEFT OUTER joinRIGHT OUTER joinFULL OUTER joinCartesian productCross joinCreating Natural JoinsThe NATURAL JOIN claus

7、e is based on all the columns in the two tables that have the same name.It selects 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.SELECT department_id, department_name, location_id, cityFROM

8、departmentsNATURAL JOIN locations ;Retrieving Records with Natural JoinsCreating Joins with the USING ClauseIf several columns have the same names but the data types do not match, use the USING clause to specify the columns for the equijoin.Use the USING clause to match only one column when more tha

9、n one column matches.The NATURAL JOIN and USING clauses are mutually exclusive.Joining Column NamesEMPLOYEES DEPARTMENTS Foreign keyPrimary keySELECT employee_id, last_name, location_id, department_idFROM employees JOIN departmentsUSING (department_id) ;Retrieving Records with the USING ClauseSELECT

10、 l.city, d.department_name FROM locations l JOIN departments dUSING (location_id)WHERE d.location_id = 1400;Using Table Aliases with the USING ClauseDo 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.注：題7Creating Joins

11、 with the ON ClauseThe join condition for the natural join is basically an equijoin of all columns with the same name.Use the ON clause to specify arbitrary conditions or specify columns to join.The join condition is separated from other search conditions.The ON clause makes code easy to understand.

12、SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id);Retrieving Records with the ON ClauseSELECT employee_id, city, department_nameFROM employees e JOIN departments dON d.department_id = e.departme

13、nt_id JOIN locations lON d.location_id = l.location_id;Creating Three-Way Joins with the ON ClauseSELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)AND e.manager_id = 149 ;Applying Additional Con

14、ditions to a JoinUse the AND clause or the WHERE clause to apply additional conditions:SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_idFROM employees e JOIN departments dON (e.department_id = d.department_id)WHERE e.manager_id = 149 ;OrLesson AgendaTypes of JOINS an

15、d its syntaxNatural join:USING clauseON clauseSelf-joinNonequijoinsOUTER join:LEFT OUTER joinRIGHT OUTER joinFULL OUTER joinCartesian productCross joinJoining a Table to ItselfMANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANAGER table.EMPLOYEES (WORKER)EMPLOYEES (MANAGER)Self-Joins

16、Using the ON ClauseSELECT worker.last_name emp, manager.last_name mgrFROM employees worker JOIN employees managerON (worker.manager_id = manager.employee_id);Lesson AgendaTypes of JOINS and its syntaxNatural join:USING clauseON clauseSelf-joinNonequijoinsOUTER join:LEFT OUTER joinRIGHT OUTER joinFUL

17、L OUTER joinCartesian productCross joinNonequijoinsEMPLOYEESJOB_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.SELECT e.last_name, e.salary, j.grade_levelFROM empl

18、oyees e JOIN job_grades jON e.salary BETWEEN j.lowest_sal AND j.highest_sal;Retrieving Records with NonequijoinsLesson AgendaTypes of JOINS and its syntaxNatural join:USING clauseON clauseSelf-joinNonequijoinsOUTER join:LEFT OUTER joinRIGHT OUTER joinFULL OUTER joinCartesian productCross joinReturni

19、ng Records with No Direct Match Using OUTER JoinsEquijoin with EMPLOYEESDEPARTMENTSThere are no employees in department 190.Employee “Grant” has not been assigned a department ID.INNER Versus OUTER JoinsIn SQL:1999, the join of two tables returning only matched rows is called an INNER join.A join be

20、tween 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 as well as the results of a left and right join is a full OUTER join.SEL

21、ECT e.last_name, e.department_id, d.department_nameFROM employees e LEFT OUTER JOIN departments dON (e.department_id = d.department_id) ;LEFT OUTER JOINSELECT e.last_name, d.department_id, d.department_nameFROM employees e RIGHT OUTER JOIN departments dON (e.department_id = d.department_id) ;RIGHT O

22、UTER JOINSELECT e.last_name, d.department_id, d.department_nameFROM employees e FULL OUTER JOIN departments dON (e.department_id = d.department_id) ;FULL OUTER JOINLesson AgendaTypes of JOINS and its syntaxNatural join:USING clauseON clauseSelf-joinNonequiijoinOUTER join:LEFT OUTER joinRIGHT OUTER j

23、oinFULL OUTER joinCartesian productCross joinCartesian ProductsA Cartesian product is formed when:A join condition is omittedA join condition is invalidAll rows in the first table are joined to all rows in the second tableAlways include a valid join condition if you want to avoid a Cartesian product.Generating a Cartesian ProductCartesian product: 20 x 8 = 160 rowsEMPLOYEES (20 rows)DEPARTMENTS (8 rows)SELECT last_name, department_