Joins extract information from two or more tables. In a two-table join, one table is treated as the outer table and the other table is treated as the inner table. Adaptive Server examines the outer table for rows that satisfy the query conditions. For each row in the outer table that qualifies, Adaptive Server then examines the inner table, looking at each row where the join columns match.
Optimizing join queries is extremely important for system performance, since relational databases make heavy use of joins. Queries that perform joins on several tables are especially critical to performance, as explained in the following sections.
In showplan output, the order of “FROM TABLE” messages indicates the order in which Adaptive Server chooses to join tables.
See “FROM TABLE message” on page 75 in the Performance and Tuning: Monitoring and Analyzing for Performance book for an example that joins three tables. Some subqueries are also converted to joins.
