How joins are processed

User's Guide
PART 1. Working with Databases
CHAPTER 6. Joins: Retrieving Data from Several Tables

How joins are processed

Knowing how joins are processed helps to understand them—and to figure out why, when you incorrectly state a join, you sometimes get unexpected results. This section describes the processing of joins in conceptual terms. When executing your statements, Adaptive Server Anywhere uses a sophisticated strategy to obtain the same results by more efficient means.

The first logical step in processing a join is to use the join condition to form the Cartesian product of the tables—all the possible combinations of the rows from each of the tables. The number of rows in a Cartesian product of tables is the product of the number of rows in the individual tables. This Cartesian product contains all the rows that satisfy your join condition, and all of the columns from all of the tables.
The next logical step is to select the rows you want using conditions in the WHERE clause. Whereas you may include NULL values for missing rows using a left- or right-outer join, Adaptive Server Anywhere selects rows only if the condition evaluates to TRUE. It omits rows if the condition evaluates to either FALSE or UNKNOWN.
If you include a GROUP BY clause, the rows are partitioned according to your conditions. Next, rows are selected from these partitions according to any conditions in the HAVING clause.
If the statement includes an ORDER BY clause, then Adaptive Server Anywhere uses it to order the remaining rows. When you do not specify an ordering, make no assumptions regarding the order of the rows.
Finally, Adaptive Server Anywhere returns those columns you specified in your select statement.

Tips
Adaptive Server Anywhere accepts a wide range of syntax. This flexibility means that most queries result in an answer, but sometimes not the one you intended. The following precautions will help you avoid this peril.
1 Always use correlation names.
2 Try eliminating a WHERE clause when testing a new statement.
3 Avoid mixing inner joins with left-outer or right-outer joins.
4 Examine the plan for your query—does it include all the tables?

Performance considerations

Generally, Adaptive Server Anywhere prefers to process joins by selecting information in one table, then performing an indexed look-up to get the rows it needs from another. Anywhere carefully optimizes each of your statements before executing it. As long as your statement correctly identifies the information you want, it usually doesn't matter what syntax you use.

In particular, Adaptive Server Anywhere is free to reconstruct your statement to any form that is semantically equivalent. It will almost always will do so, so as to compute your result efficiently. You can determine the result of a statement using the above methods, but Anywhere usually obtains the result by another means.

One means by which Adaptive Server Anywhere improves performance is to use indexes whenever doing so will improve performance. Columns that are part of a primary or secondary key are indexed automatically. Other columns are not. Creating additional indexes on columns involved in a join, either as part of a join condition or in a where clause, can improve performance dramatically.

For Info For further performance tips, see Monitoring and Improving Performance.