Joining More than Two SQL Server Tables

on September 9, 2013

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.


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.


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.


Related Posts

Leave a Reply