DB2 LUW: how to influence query planner’s choice of join

A quick check on Wikipedia doesn't mentioned if an "outer join" implies left, right or full when this important bit is omitted.

Practically,

• "outer join" by iself isn't supported. You normally require LEFT, RIGHT or FULL
• "natural" means "join on column with the same names"

This means

• "Natural outer join" won't be recognised
• "Natural full outer join" is "full outer join" with "natural" matching

Indexs/keys don't matter in this case and make no difference.

The result you get is correct for the standard

select *
from 
   R
   full outer join 
   S ON R.B = S.B

or

select *
from 
   R
   full outer join 
   S USING (B)

Note: not all RDBMS support all syntax:

• SQL Server doesn't support NATURAL (a good thing)
• MySQL doesn't support FULL OUTER JOIN (can be worked around)

Natural joins are dangerous anyway (SO links)

• https://stackoverflow.com/questions/4826613/natural-join-in-sql-server/4826659#4826659
• https://stackoverflow.com/questions/3277580/sql-server-lack-of-natural-join-x-join-y-usingfield
• https://stackoverflow.com/questions/3063107/is-natural-join-any-better-than-select-from-where-in-terms-of-performance

You are right that for Nested Loop Join, the choice of which table is the inner and which the outer table matters for perforamnce.

However, there is nothing in the documentation, in the link you provided, that implies that for a query that has a INNER JOIN b, the table a will be used as inner and b as outer table when the Nested Loop Join algorithm is selected.

Any decent optimizer evaluates many different combinations of algorithms, placing of tables and order of execution, so I don't think there is any difference if you write a INNER JOIN b or b INNER JOIN a, the chosen execution plans should be the same in both cases. If there are exceptions to this, I would expect them to be for very complex queries with tens of joined tables and/or multiple groupings.

Testing and checking the actual execution plans is one way to confirm this. Another would be to analyze the source code of the query optimizer.

The general guidance when writing SQL (in whatever DBMS), is not to care at all about the table join orders. SQL code describes what you want as result, it doesn't tell the DBMS how to get it. And many optimizers now are really smarter and faster than most of us in choosing the best execution plan most of the time.

Unless documentation shows that the optimizer is naive or in a very early version and the way the queries are written, really affects the chosen execution plan.

Or testing/running of a specific query shows that it's slow and some obviously good plan was not chosen. Then you can experiment with hints (if the DBMS has such feature), try rewriting the query in different ways, check if statistics are updated, etc.