Three-way join

SQL tutorials mention that joins can select data from two or more tables, but they only give examples of joining two tables. What is the syntax for joining say three tables?

There are several flavors of join syntax that you can use. The "old-fashioned" way involves writing the table names in a table list in the FROM clause and the join conditions in the WHERE clause --

select colA1, colA2, colB2, colC2 
  from tableA, tableB, tableC
 where colA1 = colB1
   and         colB1 = colC1

Note that colA1, colB1, and colC1 are the primary/foreign keys used to link the tables in this example. Is it necessary to space the conditions out the way I've done? No, that's just for aesthetics -- you may format and indent your SQL any way you like. My spacing out of join columns is a holdover from painful hours spent debugging queries, particularly where there were multiple primary/foreign columns involved in the multi-table join; it helps to "line up" the columns participating in the join, to make it clearer which table they belong to, especially if the column names do not obvioulsy indicate which table they're coming from. If the join columns all have the same name, then you have no choice but to qualify them with their respective table names --

select tableA.keycol, tableA.colX, tableB.colX, tableC.colX
  from tableA, tableB, tableC
 where tableA.keycol = tableB.keycol
   and                 tableB.keycol = tableC.keycol

It is worth noting that the order of the joins is not significant, because they're all equal conditions. The following gives exactly the same result --

select tableA.keycol, tableA.colX, tableB.colX, tableC.colX
  from tableA, tableB, tableC
 where tableA.keycol              = tableC.keycol
   and              tableB.keycol = tableC.keycol

The more recent "standard" method involves using JOIN syntax with ON conditions to link the tables --

select colA1, colA2, colB2, colC2 
  from tableA
 inner join tableB
    on colA1 = colB1
 inner join tableC
    on         colB1 = colC1

An alternative method for joining relies on the primary and foreign keys having the same name in the joined tables. Thus you can write --

select keycol, colA2, colB2, colC2 
  from tableA
 natural join tableB 
 natural join tableC

This syntax, however, is not supported by all databases.

For More Information

• What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.