Oracle DB uses a connection to display data in multiple tables

Source: Internet
Author: User

• 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.
  • Join type
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:

  • 1
  • 2
  • 3
  • 4
  • Next Page

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.