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.