Java课件 第讲 SQL语言高级多表关联查询培训讲义.pptVIP

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 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 Equijoin Non-equijoin Outer join Self join Joining Tables Using Oracle Syntax Use a join to query data from more than one table. Write the join condition in the WHERE clause. Prefix the column name with the table name when the same column name appears in more than one table. What is an Equijoin? Retrieving Records with Equijoins Qualifying Ambiguous Column 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. Non-Equijoins Retrieving Records with Non-Equijoins Outer Joins Outer Joins Syntax You use an outer join to also see rows that do not meet the join condition. The outer join operator is the plus sign (+). Using Outer Joins Self Joins Joining a Table to Itself Summary In this lesson, you should have learned how to use joins t