• Compile SELECT statements, use equijoin and non-equijoin to access data in multiple tables • use a self-join to connect the table to itself • use an OUTER join to view data that typically does not meet the join conditions • generate two or more tables cartesian product of all rows • JOIN type and its syntax • Natural JOIN: -USING clause-ON Clause • auto join • non-equivalent join • OUTER Join:-left outer Join-right outer Join-full outer join • Cartesian Product-cross join
- Obtain data from multiple tables
Sometimes you need to use data from multiple tables. For example, the data in two independent tables: • The employee ID is in the EMPLOYEES table. • The Department ID is in the EMPLOYEES and DEPARTMENTS tables. • The Department name is in the DEPARTMENTS table. To generate the report, you need to link the EMPLOYEES table and the DEPARTMENTS table, and then access the data in the two tables.
SQL: 1999-compliant joins include: • NATURAL joins:-NATURAL JOIN clause-USING clause-ON Clause • OUTER joins: -left outer join-right outer join-full outer join • To JOIN tables, you can use the JOIN syntax that complies with the SQL: 1999 standard. Note • in an earlier release of Oracle9i, the join syntax is different from the American National Standards Institute (ANSI) standard. Compared with the Oracle dedicated join syntax in the previous release, the join syntax that complies with SQL: 1999 has no performance advantage.
- Use SQL: 1999 syntax to join a table
You can use JOIN to query data in multiple tables: SELECT table1.column, table2.columnFROM table1 [natural 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]; In this syntax: • table1.column indicates the table and column from which data is retrieved • natural join joins two tables based ON the same column name • JOIN table2 USING column_name performs equivalent JOIN Based ON column name • JOIN table2 ON table1.column _ name = table2.column _ name performs equijoin according to the conditions in the ON clause. • LEFT/RIGHT/full outer is used to execute outer join. • cross join is used to return the Cartesian product of two tables.For more details, please continue to read the highlights on the next page:
