Les04_chinese_多表查询.pptVIP

Creating Cross Joins The example on the slide gives the same results as the following: SELECT last_name, department_name FROM employees, departments; … Creating Natural Joins It was not possible to do a join without explicitly specifying the columns in the corresponding tables in prior releases of Oracle. In Oracle9i it is possible to let the join be completed automatically based on columns in the two tables which have matching data types and names, using the keywords NATURAL JOIN keywords. Note: The join can happen only on columns having the same names and data types in both the tables. If the columns have the same name, but different data types, then the NATURAL JOIN syntax causes an error. Retrieving Records with Natural Joins In the example on the slide, the LOCATIONS table is joined to the DEPARTMENT table by the LOCATION_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all. Equijoins The natural join can also be written as an equijoin: SELECT department_id, department_name, departments.location_id, city FROM departments, locations WHERE departments.location_id = locations.location_id; Natural Joins with a WHERE Clause Additional restrictions on a natural join are implemented by using a WHERE clause. The example below limits the rows of output to those with a department ID equal to 20 or 50. SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations WHERE department_id IN (20, 50); The USING Clause Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement. For example, this statement is valid: SELECT l.city, d.department