Adaptive Server cannot optimize join clauses that are linked with or and it may perform Cartesian products to process the query.
Adaptive Server optimizes search arguments that are linked
with or. This description applies only to join
clauses.
For example, when Adaptive Server processes this query, it must look at every row in one of the tables for each row in the other table:
select *
from tab1, tab2
where tab1.a = tab2.b
or tab1.x = tab2.y
If you use union, each side of the union is optimized separately:
select *
from tab1, tab2
where tab1.a = tab2.b
union all
select *
from tab1, tab2
where tab1.x = tab2.y
You can use union instead of union all to eliminate duplicates, but this eliminates all duplicates. You may not get exactly the same set of duplicates from the rewritten query.
Adaptive Server can optimize selects with joins that are linked with union. The result of or is somewhat like the result of union, except for the treatment of duplicate rows and empty tables:
union removes all duplicate rows (in a sort step); union all does not remove any duplicates. The comparable query using or might return some duplicates.
A join with an empty table returns no rows.
