Is it possible to perform a dynamic SQL join?

What is the most efficient way to implement join on multiple tables (>5 tables) using SQL/ANSI SQL?

• how to implement 10+ tables outer join using ANSI SQL (using ON clause) for non oracle database (SQL Server and DB2).

• Answer:

  Generally speaking, your database's optimizer should figure out the best method for accessing and joining data.  While you could provide optimizer hints, there isn't any better way to inner join two tables than writing an inner join statement.  The biggest thing that will impact the performance of your joins is your data model, data size, physical storage properties (IE partitioning), join type (Outer, inner, etc) and indexing strategy.  Also don't forget to gather statistics. For understanding how joins will impact query performance I recommend understanding how/when the following joining methods are used: Hash Join Nested Loop Sort Merge Cartesian Anti-Join Semi-Join Oracle docs have great details on each of these and how the optimizer works to predict lowest cost access: http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#autoId31