ASA SQL User's Guide
  Query Optimization and Execution
    Semantic query transformations
      Types of semantic transformations

Join elimination

The join elimination rewrite optimization reduces the join degree of the query by eliminating tables from the query when it is safe to do so. Typically, this optimization is used when the query contains a primary key-foreign key join, and only primary key columns from the primary table are referenced in the query.

Example 

For example, the query

SELECT s.id, s.line_id, p.id
FROM sales_order_items s KEY JOIN product p

would be rewritten as

SELECT s.id, s.line_id, s.prod_id
FROM sales_order_items s
WHERE s.prod_id IS NOT NULL.

The second query is semantically equivalent to the first because any row from the sales_order_items table that has a NULL foreign key to product will not appear in the result.

The join elimination optimization can also apply to tables involved in outer joins, although the conditions for which the optimization is valid are much more complex. Under certain other conditions, tables involved in primary key-primary key joins may also be candidates for elimination.

Users should be aware that when this optimization is used, the result of a DESCRIBE can differ from the expected result due to the substitution of columns. In addition, an UPDATE or DELETE WHERE CURRENT request may fail if the update statement refers to one or more of the eliminated base tables. To circumvent this problem, either ensure that additional columns from the eliminated table are present in the query's SELECT list (to avoid the optimization in the first place), or update the necessary row(s) using a separate statement.