Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
all labs.pdf
Скачиваний:
74
Добавлен:
25.02.2016
Размер:
2 Мб
Скачать
☆

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 data that generally does not meet a join condition by using OUTER joins

•Generate a Cartesian product of all rows from two or more tables

Objectives

 

Academy

 

Copyright © 2009, Oracle. All rights reserved.

 

 

Oracle

 

This lesson explains how to obtain data from more than one tab

. A join is used to view information

from multiple tables. Therefore, you can join tables together to view information from more than one

table.

 

Note: Information on joins is found in the section on SQL Queries and Subqueries: Joins in

 

Only

Oracle Database SQL Language Reference 11g, Release 1 (11.1).

&

 

Internal

 

Use

Oracle

 

Oracle Database 11g: SQL Fundamentals I 6 - 2

Lesson Agenda

•Types of JOINS and its syntax

•Natural join:

–USING clause

–ON clause

•Self-join

•Nonequijoins

•OUTER join:

–LEFT OUTER join

–RIGHT OUTER join

–FULL OUTER join

•Cartesian product

–Cross join

 

Copyright © 2009, Oracle. All rights reserved.

Academy

 

 

 

 

Oracle

&

Only

 

 

Internal

 

 

 

 

Use

 

Oracle

 

 

 

Oracle Database 11g: SQL Fundamentals I 6 - 3

Oracle Database 11g: SQL Fundamentals I 6 - 4
Obtaining Data from Multiple Tables
Copyright © 2009, Oracle. All rights reserved.
Academy Sometimes you need to use data from more thanOracleone table. In the xample in the slide, the report displays data from two separate tables:
• Employee IDs exist in the EMPLOYEES table.
• Department IDs exist in both the EMPLOYEES and DEPARTMENTS tables.
• Department names exist in the DEPARTMENTSOnlytable.
To produce the report, you need to link the&EMPLOYEES a d DEPARTMENTS tables, and access data from both of them.InternalUse Oracle
…
DEPARTMENTS
EMPLOYEES
Obtaining Data from Multiple Tables
Types of Joins
Copyright © 2009, Oracle. All rights reserved.

Types of Joins

Joins that are compliant with the SQL:1999 standard include the following:

•Natural joins:

–NATURAL JOIN clause

–USING clause

–ON clause

•OUTER joins:

–LEFT OUTER JOIN

–RIGHT OUTER JOIN

–FULL OUTER JOIN

•Cross joins

Note

Academy To join tables, you can use a join syntax that is compliantOraclewith the SQL:1999 standard.

• Before the Oracle9i release, the join syntax was diffe ent from the American National Standards Institute (ANSI) standards. The SQL:1999–compliant join s ntax does not offer any performance benefits over the Oracle-&proprietaryOnlyjoin sy tax that existed in the prior releases.

For detailed information about the proprietary join sy tax, see Appendix C: Oracle Join Syntax.

• The followingInternalslide discusses the SQL:1999 join syntax. Use

Oracle

Oracle Database 11g: SQL Fundamentals I 6 - 5

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]