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 complexaccommodating 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
, the second join applies to table2
. 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.
The previous example used what may be a new term for youinner 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.
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 recordsand only those recordswhere 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 joinretrieving 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 namethus 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:
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:
FROM table1 JOIN table2
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 interchangeablethe 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
You'll learn about the outer joins in the next section. (The examples written in Oracle are for syntax comparison only.)