Search Conditions

The SQL optimizer can only optimize data access for SQL statements that contain a search condition. Search conditions can occur in the following situations:

• in a WHERE clause

• in a join which is defined using ANSI syntax

SELECT name
  FROM customer
    INNER JOIN ORDER ON customer.ID = order.ID and order.ID = 4711
  WHERE customer.name = 'CUSTOMER'

You can specify search conditions for the following types of SQL statements:

• SELECT

• UPDATE

  Note that restrictions apply, see Restrictions for UPDATE Statements.

• DELETE

• INSERT

The form of the search condition has a significant impact on the costs incurred by executing the SQL statement. The SQL optimizer only takes into account the following search conditions:

• Equality Condition

• Range Condition

• IN Condition

• LIKE Condition

If the SQL optimizer cannot transform the search conditions in an SQL statement into one of the forms listed above, it cannot determine a search strategy for them and uses the sequential search instead. If search conditions are linked using several identical boolean operators, the sequence of these conditions does not affect how the SQL optimizer determines the search strategy.

SQL Reference Manual, Table Expression (table_expression)