CRITICAL SKILL 2.8 Employ Joins (ANSI vs. Oracle): Inner, Outer, Self

Team Fly

Page 57

CRITICAL SKILL 2.8
Employ Joins (ANSI vs. Oracle): Inner, Outer, Self

Up until now, all of the examples in this chapter have selected data from only one table. In actual fact, much of the data that we need is in two or more tables. The true power of a relational database (and the source of its name) comes from the ability to relate different tables and their data together. Understanding this concept is critical to harvesting the information held within the database. This is more commonly known as joining two or more tables.

With Oracle Database 10g, queries can be written using either Oracle's SQL syntax or ANSI syntax. While Oracle hasn't made ANSI syntax available until recently, it has been used in non-Oracle environments for some time. Many third-party tools accept ANSI SQL and, as you'll see shortly, the joins are quite different.

Inner Joins

An inner join, also known simply as join, occurs when records are selected from two tables and the values in one column from the first table are also found in a similar column in the second table. In effect, two or more tables are joined together based on common fields. These common fields are known as keys. There are two types of keys:

A primary key is what makes a row of data unique within a table. In the CUSTOMERS table, CUST_ID is the primary key.

A foreign key is the primary key of one table that is stored inside another table. The foreign key connects the two tables together. The SALES table also contains CUST_ID, which in the case of the SALES table, is a foreign key back to the CUSTOMERS table.

Oracle Inner Joins

The tables to be joined are listed in the from clause and then related together in the where clause. Whenever two or more tables are found in the from clause, a join happens. Additional conditions can still be specified in the where clause to limit which rows will be returned by the join. For example, when we queried the SALES table on its own, the only customer information available to us was the CUST_ID. However, if we join each record, we retrieve from the SALES table by the CUST_ID to the same column in the CUSTOMERS table, and all the customer information becomes available to us instantly.

Team Fly