Joining Three or More Tables
It was mentioned earlier that you can join multiple tables. The truth is that the complete statement can be fairly complex—accommodating several join clauses. The rules are the same, but the most important thing to remember is that each join applies to only one relationship, and each relationship represents an association between only two tables. Therefore, joins must build upon one another. When joining more than two tables, use the following syntax:

FROM table1 join table2
  ON table1.primarykey = table2.foreignkey join table3
  ON table2.primarykey = table3.foreignkey

The first join applies to table1 and table2, the second join applies to table2 and table3. You might hear table2 referred to as the intermediate or translation table because it's the connection table for the other two tables. In a really complex statement, you might have several intermediate tables. Continue to add joins in this manner until your statement includes all the relationships and tables necessary to get just the right results. This is another fairly generic behavior that may or may not be strictly enforced by your system; be sure to check your system's documentation.

Join Types
The previous example used what may be a new term for you—inner join. SQL supports a number of join types: inner, left outer, right outer, full outer, and cross. Not all systems support all join types. Check your system's documentation for specifics on which joins it supports.

Inner Join
An inner join returns only those records from both tables that have a matching value in the related field. This type of join is by far the most commonly used. In fact, many systems use this type as the default join. Think of your tables as intersecting circles, like those shown in Figure 2. Each circle represents a table within a relationship and the area where the circles intersect represents the records that have a matching primary and foreign key value. SQL retrieves or acts upon those records—and only those records—where the two circles intersect. (Again, remember that relationships aren't confined to key fields, although most of the time, that is the arrangement you'll be working with.)

	Figure 2: An inner join returns only those records where the primary and foreign key values match.

The earlier Northwind example was an inner join—retrieving data from both tables only when the CategoryID values matched. In this particular example, each product record had a corresponding category value, but that won't always be the case. Sometimes records are eliminated simply because they don't have a matching primary/foreign key value. As you can see, a join works somewhat like a filter.

Oracle SQL enhances your flexibility with the release of Oracle 9i by supporting additional join syntax. Specifically, Oracle SQL (9i only) supports what it refers to as a natural join. This type of join automatically joins all columns in both tables with the same name—thus eliminating the need to include the join in a WHERE statement. The natural join may be used in both inner and outer joins using the following syntax:

SELECT fldlist
FROM table1 NATURAL JOIN table2

Another Oracle SQL (9i only) addition is the USING clause. Rely on this new clause when tables have multiple columns with the same name and you want to limit the join to one particular column. You can modify the previous syntax by replacing the ON clause with a USING clause as follows:

SELECT fldlist
FROM table1 JOIN table2
USING (column)

When using this syntax, don't qualify the column name in the fldlist or USING clause with the table name. Just remember that the ON and USING clauses aren't interchangeable—the results will differ.

Now, take a look at an Oracle counterpart to the earlier Northwind inner join example. The following statement returns all the records where CategoryID in the Products table matches CategoryID in Categories:

SELECT Products.ProductName, Categories.CategoryName
FROM Products JOIN Categories
USING (CategoryID)

You'll learn about the outer joins in the next section. (The examples written in Oracle are for syntax comparison only.)