OracleDB uses a connection to display data in multiple tables

In an earlier release of Oracle9i, the join syntax is different from that of the American National Institute of Standards (ANSI. Compared with the Oracle dedicated connection syntax in the previous release, it complies with

In an earlier release of Oracle9i, the join syntax is different from that of the American National Institute of Standards (ANSI. Compared with the Oracle dedicated connection syntax in the previous release, it complies with

• Write SELECT statements to access data in multiple tables using equijoin and non-equijoin

• Use a self-join to connect a table to itself

• Use OUTER Join to view data that normally does not meet the join conditions

• Generate Cartesian product of all rows in two or more tables

• JOIN type and syntax

• Natural Connectivity:

-USING clause

-ON clause

• Self-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, 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.

Join type

SQL: 1999-compliant connections include:

• Natural Connectivity:

-Natural join clause

-USING clause

-ON clause

• OUTER Join:

-LEFT OUTER JOIN

-RIGHT OUTER JOIN

-FULL OUTER JOIN

• Cross join

To join tables, you can use the join syntax that complies with the SQL: 1999 standard.

Notes

• In an earlier release of Oracle9i, the join syntax is different from that of the American National Institute of Standards (ANSI.

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 a join operation to query data in multiple tables:

SELECT table1.column, table2.column

FROM 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 tables and columns from which data is retrieved.

• Natural join two tables based on the same column name

• JOIN table2 USING column_name performs equivalent JOIN Based on column names

• JOIN table2 ON table1.column _ name = table2.column _ name execute equivalent JOIN based ON conditions in the ON Clause

• LEFT/RIGHT/full outer is used to execute the 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: