Joining More than Two SQL Server Tables

By: Dusan Petkovic


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:

0222_001

 

The result is:

186

 

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:

0222_002

 

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:

0222_003

0223_001

 

The result is:

187

 

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