By default, Adaptive Server uses nested-loop joins, and also consider merge joins, if this feature is enabled at the server-wide or session level.
When merge joins are enabled, Adaptive Server can use either nested-loop joins or merge joins to process queries involving two or more tables. For each join, the optimizer costs both methods. For queries involving more than two tables, the optimizer examines query costs for merge joins and for nested-loops, and chooses the mix of merge and nested-loop joins that provides the cheapest query cost.
