04多表连接.ppt

Displaying Data from Multiple Tables多表查询 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 equality and nonequality joins(多表查询使用等连或非等连) View data that generally does not meet a join condition by using outer joins(外连) Join a table to itself by using a self join(自连接) Obtaining Data from Multiple Tables Cartesian Products(笛卡儿积) A Cartesian product is formed when: A join condition is omitted(没有连接条件) A join condition is invalid(连接条件无效) All rows in the first table are joined to all rows in the second table(A表所有行连接B表所有行) To avoid a Cartesian product, always include a valid join condition in a WHERE clause.(避免笛卡儿积,用连接条件) Generating a Cartesian Product Types of Joins(连接类型) Cross joins Natural joins Using clause Full or two sided outer joins Arbitrary join conditions for outer joins SQL: 1999 Compliant Joins: Joining Tables Use a join to query data from more than one table. Write the join condition in the WHERE clause.(连接条件在where子句中书写) Prefix the column name with the table name when the same column name appears in more than one table.(前缀) 两表连接的常见情形 两表连接例子 Qualifying AmbiguousColumn Names(处理含有歧意的列名) Use table prefixes to qualify column names that are in multiple tables.(前缀表名) Improve performance by using table prefixes.(前缀提高性能) Distinguish columns that have identical names but reside in different tables by using column aliases.(别名) Using Table Aliases(表别名) Simplify queries by using table aliases. Improve performance by using table prefixes. Joining More than Two Tables(连接多表) To join n tables together, you need a minimum of n-1 join conditions. For example, to join three tables, a minimum of two joins is required. Joining Tables Using SQL: 1999 Syntax(连接的SQL99语法) Use a join to query data from more than one table. Creating Cross Joins(交集连接) The CROSS JOIN clause produces the cross-product of two tables. This is the same as a Cartesian product between the two