Theoretically, there is no upper limit on the number of tables that can be joined using a SELECT statement. (One join condition always combines two tables!) However, the Database Engine has an implementation restriction: the maximum number of tables that can be joined in a SELECT statement is 64.
Example 1 joins three tables of the sample database.
EXAMPLE 1
Get the first and last names of all analysts whose department is located in Seattle:
The result is:
The result in Example 1 can be obtained only if you join at least three tables: works_on, employee, and department. These tables can be joined using two pairs of join columns:
Example 2 uses all four tables from the sample database to obtain the result set.
EXAMPLE 2
Get the names of projects (with redundant duplicates eliminated) being worked on by employees in the Accounting department:
The result is:
Notice that when joining three tables, you use two join conditions (linking two tables each) to achieve a natural join. When you join four tables, you use three such join conditions. In general, if you join n tables, you need n ? 1 join conditions to avoid a Cartesian product. Of course, using more than n ? 1 join conditions, as well as other conditions, is certainly permissible to further reduce the result set.
Leave a Reply